The following information can be used to create the report for Advanced Exercise 1: Create a New Report for Desired Configuration Management.
Report SQL Statement
The following SQL statement can be used to retrieve the information required by this exercise:
SELECT
v_CICurrentComplianceStatus.ComplianceStateName,
v_LocalizedCIProperties.DisplayName,
v_LocalizedCIProperties.Description
FROM v_CICurrentComplianceStatus INNER JOIN
v_R_System ON
v_CICurrentComplianceStatus.ResourceID =
v_R_System.ResourceID
INNER JOIN v_ConfigurationItems ON
v_CICurrentComplianceStatus.CI_ID =
v_ConfigurationItems.CI_ID
INNER JOIN v_LocalizedCIProperties ON
v_CICurrentComplianceStatus.CI_ID =
v_LocalizedCIProperties.CI_ID
WHERE (v_ConfigurationItems.CIType_ID = 2)
AND
(v_R_System.Netbios_Name0 LIKE
@client)
Report Prompt
The following report prompt settings and SQL statement can be used for this exercise:
Name: client
Prompt Text: Computer Name
Report Prompt SQL Statement:
begin
if (@__filterwildcard = '')
SELECT DISTINCT
SYS.Netbios_Name0
from v_R_System SYS WHERE
SYS.Client0=1
ORDER By SYS.Netbios_Name0
else
SELECT DISTINCT
SYS.Netbios_Name0
from v_R_System SYS WHERE
SYS.Client0=1
and SYS.Netbios_Name0
like @__filterwildcard
ORDER By SYS.Netbios_Name0
end