ok... this I "think" might be closer to what you really want. This is 99.999%
stolen from an existing default report. So I honestly don't really understand
it all that much myself. lol I just tweaked it a bit; and it's probably wrong
in some fashion where I tweaked it. But... try it out. It takes 1 parameter,
@ComputerName, and I hard coded in the locale of 1033 (completely cheating,
sorry you guys in other language areas--change it if you want)
declare @compliant int
declare @notapplicable int
declare @notdetected int
declare @enforced int
select @compliant = 1
select @notapplicable= 5
select @notdetected = 6
select @enforced = 7
--declarations
declare @username nvarchar(512) = '(SYSTEM)'
declare @computer nvarchar(512) = @ComputerName
declare @collid nvarchar(512) = ''
declare @CIVersion nvarchar(512) = ''
declare @assignmentid nvarchar(512) = ''
declare @CIName nvarchar(512) = ''
declare @BLname nvarchar(512) = ''
declare @allassignments table
(
AssignmentID int,
CollectionID nvarchar(512),
CollecionName nvarchar(512),
CI_ID int,
DisplayName nvarchar(512),
CIType_ID int,
CI_UniqueID nvarchar(512),
CIVersion int
)
declare @AllCIs table
(
BL_CI_ID int,
CI_ID int,
CIVersion int,
ModelName nvarchar(512),
CI_UniqueID nvarchar(512),
CIType_ID int,
RelationType int,
DisplayName nvarchar(512)
)
--declare @lcid as int set @lcid = dbo.fn_LShortNameToLCID(@locale)
declare @UserIDs table (UserID int)
insert into @UserIDs(UserID) select UserID from v_Users where
FullName = @UserName
declare @MachineID as int
set @MachineID = (select ResourceID from v_R_System_Valid where
Netbios_Name0 = @computer)
insert into @allassignments
(AssignmentID,CollectionID,CollecionName,CI_ID,
DisplayName,CIType_ID,CI_UniqueID,CIVersion)
select assign.AssignmentID,coll.CollectionID as
CollectionID,coll.name as CollecionName,bls.CI_ID,
dbo.fn_GetLocalizedCIName(1033,bls.CI_ID),bls.CIType_ID,bls.CI_UniqueID,bls.CIVersion
from v_CIAssignment assign
inner join v_Collection coll on coll.CollectionID=assign.CollectionID
inner join v_CIAssignmentToCI targ on
targ.AssignmentID=assign.AssignmentID
inner join v_CIRelation_All rel on rel.CI_ID=targ.CI_ID
inner join v_SMSConfigurationItems bls on
bls.CI_ID=rel.ReferencedCI_ID
where CIType_ID in (2,50) and bls.IsTombstoned=0
and (assign.AssignmentID = @assignmentID or @assignmentID='' or
@assignmentID is NULL)
and assign.AssignmentType in (0,8)
and (coll.CollectionID=@collid or @collid='')
and (dbo.fn_GetLocalizedCIName(1033,bls.CI_ID) = @BLname or @BLname =
'')
Insert into @AllCIs
select distinct rel.CI_ID,
rel.ReferencedCI_ID,ciref.CIVersion,ciref.ModelName,ciref.CI_UniqueID,ciref.CIType_ID,rel.RelationType,dbo.fn_GetLocalizedCIName(1033,ciref.CI_ID)
from
@allassignments bl
inner join v_CIRelation_All rel on rel.CI_ID=bl.CI_ID and
rel.RelationType<>5 and rel.RelationType<>50 and rel.RelationType<>8 and
rel.RelationType<>80
inner join v_ConfigurationItems ciref on
ciref.CI_ID=rel.ReferencedCI_ID
where
((rel.RelationType in (0, 1, 2, 3, 4)) OR (rel.RelationType = 7 and
ciref.CIType_ID=50))
and ciref.CIType_ID<>2 and ciref.CIType_ID <> 11--no baselines or
global cis
and ciref.IsTombstoned=0
and (dbo.fn_GetLocalizedCIName(1033,ciref.CI_ID) = @CIname or
@CIname='' or @CIName is null)
and (ciref.CIVersion = @CIVersion or @CIVersion='' or @CIVersion is
null)
declare @RulesAndSetting table
(
Rule_ID int,
Rule_CI_ID int,
Setting_ID int,
Setting_CI_ID int,
RuleLogicalName nvarchar(512),
SettingLogicalName nvarchar(512),
RuleName nvarchar(max),
RuleDescription nvarchar(max),
RuleType int,
SettingName nvarchar(max),
SettingDescription nvarchar(max),
SettingSourceType int,
RuleCriteria nvarchar(max),
Severity int
)
declare @countRule as int
set @countRule = 0
declare @valueRule as int
set @valueRule = 1
insert into
@RulesAndSetting(Rule_ID,Rule_CI_ID,Setting_ID,Setting_CI_ID,RuleLogicalName,SettingLogicalName,RuleName,RuleDescription,RuleType,SettingName,SettingDescription,SettingSourceType,RuleCriteria,Severity)
select distinct
rules.Rule_ID,
rules.CI_ID,
settings.Setting_ID,
settings.CI_ID,
rules.Rule_UniqueID,
settings.Setting_UniqueID,
dbo.fn_GetLocalizedProperty(1033,rules.RuleNameRID,rules.CI_ID) as
RuleName,
dbo.fn_GetLocalizedProperty(1033,rules.RuleDescriptionRID,rules.CI_ID) as
RuleDescription,
rules.RuleType,
dbo.fn_GetLocalizedProperty(1033,settings.SettingNameRID,settings.CI_ID) as
SettingName,
dbo.fn_GetLocalizedProperty(1033,settings.SettingDescriptionRID,settings.CI_ID)
as SettingDescription,
Settings.SourceType,
rules.Criteria,
rules.Severity
from @AllCIs cis
inner join v_CIRulesAll allrules on allrules.CI_ID=cis.CI_ID
inner join v_CIRules rules on rules.Rule_ID=allrules.Rule_ID
inner join v_CISettings settings on
settings.Setting_ID=allrules.Setting_ID
DECLARE @Assignments Assignment_Type INSERT INTO @Assignments select
AssignmentID from @allassignments
select
TargetCompliance.Netbios_Name0 as MachineName,
TargetCompliance.FullName,
TargetCompliance.DisplayName as ConfigurationItemName,
TargetCompliance.CIVersion,
TargetCompliance.ComplianceState,
Case when targetcompliance.ComplianceState = 1 then 'Compliant'
when targetcompliance.ComplianceState = 2 then 'Non-Compliant'
else 'Unknown' end as 'TextComplianceState',
rules.RuleName as ConstraintName,
rules.RuleDescription as ConstraintDescription,
rules.SettingName,
rules.SettingDescription,
dbo.fn_GetLastAssignmentEvaluationTime(@Assignments,TargetCompliance.ResourceID,TargetCompliance.UserID)
as LastComplianceMessageTime,
TargetCompliance.CI_UniqueID,
rules.SettingLogicalName as SettingLogicalName,
rules.RuleLogicalName as ConstraintLogicalName,
case when rules.ruleType=0 then @valueRule when rules.RuleType=1 then
@countRule else NULL end as RuleType,
case when TargetCompliance.SubRuleState=0 then @compliant when
TargetCompliance.SubRuleState=1 then @notapplicable when
TargetCompliance.SubRuleState=2 then @notapplicable when
TargetCompliance.SubRuleState=3 then @enforced end as SubRuleState,
rules.SettingSourceType,
rules.Severity
from
( --Machine assignment
select
detail.ResourceID,detail.UserID,AllCIs.DisplayName,AllCIs.CIType_ID,AllCIs.CI_UniqueID,AllCIs.CIVersion,sys.Netbios_Name0,users.FullName,detail.Setting_ID,detail.Rule_ID,detail.ComplianceState,detail.SubRuleState
from (select distinct CI_ID from @allassignments) allbl
inner join @AllCIs AllCIs on allbl.CI_ID=AllCIs.BL_CI_ID
cross join (select distinct cm.ResourceID from
v_ClientCollectionMembers cm join v_Collection coll on coll.CollectionID =
cm.CollectionID join @allassignments allbl on
allbl.CollectionID=cm.CollectionID and cm.ResourceID=@MachineID) cm
inner join v_CIComplianceStatusComplianceDetail detail on
detail.CI_ID=AllCIs.CI_ID and detail.ResourceID=cm.ResourceID and detail.UserID
in (select UserID from @UserIDs) and detail.ResourceID=@MachineID
inner join v_R_System_Valid sys on sys.ResourceID=detail.ResourceID
and sys.ResourceID=@MachineID
inner join v_Users users on users.UserID=detail.UserID
) TargetCompliance
inner join @RulesAndSetting rules on
rules.Rule_ID=TargetCompliance.Rule_ID and
rules.Setting_ID=TargetCompliance.Setting_ID
UNION
select
TargetCompliance.Netbios_Name0 as MachineName,
TargetCompliance.FullName,
TargetCompliance.DisplayName as ConfigurationItemName,
TargetCompliance.CIVersion,
TargetCompliance.ComplianceState,
Case when targetcompliance.ComplianceState = 1 then 'Compliant'
when targetcompliance.ComplianceState = 2 then 'Non-Compliant'
else 'Unknown' end as 'TextComplianceState',
rules.RuleName as ConstraintName,
rules.RuleDescription as ConstraintDescription,
rules.SettingName,
rules.SettingDescription,
dbo.fn_GetLastAssignmentEvaluationTime(@assignments,TargetCompliance.ResourceID,TargetCompliance.UserID)
as LastComplianceMessageTime,
TargetCompliance.CI_UniqueID,
rules.SettingLogicalName as SettingLogicalName,
rules.RuleLogicalName as ConstraintLogicalName,
case when rules.ruleType=0 then @valueRule when rules.RuleType=1 then
@countRule else NULL end as RuleType,
case when TargetCompliance.SubRuleState=0 then @compliant
when TargetCompliance.SubRuleState=1 then @notapplicable
when TargetCompliance.SubRuleState=2 then @notapplicable
when TargetCompliance.SubRuleState=3 then @enforced
end as SubRuleState,
rules.SettingSourceType,
rules.Severity
from
(
select
detail.ResourceID,detail.UserID,AllCIs.DisplayName,AllCIs.CIType_ID,AllCIs.CI_UniqueID,AllCIs.CIVersion,sys.Netbios_Name0,users.FullName,detail.Setting_ID,detail.Rule_ID,detail.ComplianceState,detail.SubRuleState
from (select distinct CI_ID from @allassignments) allbl
inner join @AllCIs AllCIs on allbl.CI_ID=AllCIs.BL_CI_ID
cross join (select distinct resources.UserName from
v_dcmdeploymentresourcesuser resources join v_Collection coll on
coll.CollectionID = resources.CollectionID join @allassignments allbl on
allbl.AssignmentID=resources.AssignmentID and resources.UserName=@UserName)
resources
inner join v_Users users on users.FullName=resources.UserName and
users.UserID in (select UserID from @UserIDs)
inner join v_CIComplianceStatusComplianceDetail detail on
detail.CI_ID=AllCIs.CI_ID and detail.UserID=users.UserID and detail.UserID in
(select UserID from @UserIDs) and detail.ResourceID=@MachineID
inner join v_R_System_Valid sys on sys.ResourceID=detail.ResourceID
and sys.ResourceID=@MachineID
) TargetCompliance
inner join @RulesAndSetting rules on
rules.Rule_ID=TargetCompliance.Rule_ID and
rules.Setting_ID=TargetCompliance.Setting_ID
On Wednesday, December 17, 2014 8:18 AM, Daniel Ratliff <dratl...@humana.com>
wrote:
Nice report Sherry, just ran it and the results look good!
Daniel Ratliff
From:listsad...@lists.myitforum.com [mailto:listsad...@lists.myitforum.com] On
Behalf Of Sherry Kissinger
Sent: Wednesday, December 17, 2014 8:44 AM
To: mssms@lists.myitforum.com
Subject: Re: [mssms] Detailed Compliance Baseline Reporting
Here's a beginning--I don't want to say it's perfect, because I have this in a
'testing' folder in SRS; so it's not available to everyone @work. So when I
wrote it apparently there was something about it I didn't like, or it didn't
fit whatever the person asking wanted it for.
I think (to me) one of the flaws--or maybe one of the draws (depends on your
point of view) is that it will only show CIs that are both applicable and
detected. i.e., if you have a baseline with OS applicability of "all server
08" (but not Server08R2); if you happen to upgrade a server from server08 to
08r2, from this report alone you won't realize that you neglected to update
your CI to make it applicable to 08r2--maybe that's exactly what you want--or
maybe it's not. Ditto for 'isdetected'; for an application-type CI, if your
app detection logic in that CI is flawed, you might be targeting a server which
has widgets, but your app detection logic was looking for wigdets, you wouldn't
notice from this report alone that the app isn't detected.
Also not sure if this is really what you want; this is by CI, not by each
element/rule you might have inside a CI.
select fcm.name, ba.assignmentName, lcb.IsAssigned
,cs.ComplianceState
,CASE WHEN cs.ComplianceState = 1 then 'Compliant'
when cs.ComplianceState = 2 then 'Non-Compliant'
when cs.ComplianceState = 4 then 'Failure' end as 'TextComplianceState'
,cs.IsEnforced, cs.ComplianceValidationRuleFailures, cs.errorCount
,cs.ConflictCount, cs.LastComplianceMessageTime, cs.LastEnforcementMessageTime
,ciinfo.DisplayName, ciinfo.description
from vSMS_BaselineAssignment ba
join v_fullcollectionmembership fcm on fcm.collectionid=ba.collectionid
join fn_ListconfigurationBaselineInfo(1033) lcb on
lcb.CI_UniqueID=ba.AssignedCI_UniqueID
join vSMS_CombinedconfigurationItemRelations cir on cir.FromCI_ID=lcb.CI_ID
join v_ConfigurationItems ci on ci.ci_id=cir.TOCI_ID
join dbo.v_CICurrentComplianceStatus cs with(NOLOCK) on cs.CI_ID=ci.ci_ID
and cs.Resourceid=fcm.resourceid
join fn_ListCIs(1033) ciinfo on ciinfo.ci_id=cs.ci_id
where fcm.name = @ComputerNameHere
and cs.isapplicable=1 and cs.isdetected=1
On Tuesday, December 16, 2014 3:04 PM, Paul Mallett <pmall...@gmail.com> wrote:
I know we have “Details of compliant rules of configuration items in a
configuration baseline for an asset”, “Details of non-compliant rules of
configuration items in a configuration baseline for an asset”, etc. But I’m
trying to create a report that just gives all compliance details for a specific
asset, compliant, non-compliant, errors, etc. Although I’d settle for just the
first two. Ideally in one big report, all configuration items rather than
broken down into categories to link on. It’s all for auditing purposes and it
would be easier for the auditors to search for specific settings that way.
Has anyone done anything like this? I looked at the canned reports but frankly
they’re beyond me.
The information transmitted is intended only for the person or entity to which
it is addressed
and may contain CONFIDENTIAL material. If you receive this
material/information in error,
please contact the sender and delete or destroy the material/information.
