Delete Aged DCM Scan data

[pvr02]

I recently started using DCM to create some compliance reports. Using a user based DCM, I noticed that I see on occasion the same computer in the compliant and non-compliant status.

 

Looking at the records, I noticed that the computer name and username are identical. The only difference appears to be the Last Scan Time and the Status (compliant vs non-compliant).

 

Is there some sort of Site Maintenance task that I should run more often to keep this data more accurate by removing the old scans and results?

[GarthMJ]

What does you query look like? If I remember right, the data will get clear out based Aged Inventory History task.

[pvr02]

I'm sure there is a much nicer way to accomplish this, but i'm new to the SQL game.

 

This query did help to remove all of the duplicate compliant and duplicate non-compliant computers, but in this scenario I end up with a PC listed once in each section instead of 5 times.

SELECT
  data.Computer_Name, MAX(data.Compliance_Scan) AS 'Last_Scan', data.User_Name, data.Full_Name, data.Compliance_State, data.DCM_Baseline_Name
FROM
(
SELECT
DISTINCT v_R_System.Name0 AS 'Computer_Name',
v_CICurrentComplianceStatus.LastComplianceMessageTime AS 'Compliance_Scan',
v_R_System.User_Name0 AS 'User_Name',
v_R_User.Full_User_Name0 AS 'Full_Name',
CASE v_CICurrentComplianceStatus.ComplianceState
  WHEN 1 THEN 'Compliant'
  WHEN 2 THEN 'Non-Compliant'
  Else 'Error'
END  AS 'Compliance_State',
v_LocalizedCIProperties_SiteLoc.DisplayName AS 'DCM_Baseline_Name'

FROM
v_BaselineTargetedComputers
INNER JOIN v_R_System ON v_R_System.ResourceID = v_BaselineTargetedComputers.ResourceID
INNER JOIN v_ConfigurationItems ON v_ConfigurationItems.CI_ID = v_BaselineTargetedComputers.CI_ID
INNER JOIN v_CICurrentComplianceStatus ON v_CICurrentComplianceStatus.CI_ID = v_ConfigurationItems.CI_ID AND v_CICurrentComplianceStatus.ResourceID = v_BaselineTargetedComputers.ResourceID
INNER JOIN v_LocalizedCIProperties_SiteLoc ON v_LocalizedCIProperties_SiteLoc.CI_ID = v_ConfigurationItems.CI_ID
LEFT OUTER JOIN v_R_User ON v_R_User.User_Name0 = v_R_System.User_Name0

WHERE v_LocalizedCIProperties_SiteLoc.DisplayName = 'U - IE Proxy (None) Baseline'
)
AS data
GROUP BY data.Computer_Name, data.User_Name, data.Full_Name, data.Compliance_State, data.DCM_Baseline_Name

For Example:

Computer_Name Last_Scan Compliance_State

Computer1 7/3/2014 Compliant

Computer1 6/26/2014 Non-Compliant

[NickPilon]

Your query is working on my side but I would have done it like this.

 

SELECT

SYS.Name0 AS Computer_Name,

MAX(CCS.LastComplianceMessageTime) AS 'Last_Scan',

USR.User_Name0 AS User_Name,

USR.Full_User_Name0 AS Full_Name,

CASE CCS.ComplianceState WHEN 1 THEN 'Compliant' WHEN 2 THEN 'Non-Compliant' ELSE 'Error' END AS 'Compliance_State',

CIP.DisplayName AS DCM_Baseline_Name

FROM

v_BaselineTargetedComputers BTC

INNER JOIN v_R_System SYS ON SYS.ResourceID = BTC.ResourceID

INNER JOIN v_ConfigurationItems CI ON CI.CI_ID = BTC.CI_ID

INNER JOIN v_CICurrentComplianceStatus CCS ON CCS.CI_ID = CI.CI_ID AND CCS.ResourceID = BTC.ResourceID

INNER JOIN v_LocalizedCIProperties_SiteLoc CIP ON CIP.CI_ID = CI.CI_ID

LEFT OUTER JOIN v_R_User USR ON USR.User_Name0 = SYS.User_Name0

WHERE CIP.DisplayName = 'U - IE Proxy (None) Baseline'

GROUP BY SYS.Name0, USR.User_Name0, USR.Full_User_Name0, CCS.ComplianceState, CIP.DisplayName

MAX beside CCS.LastComplianceMessageTime is a good thing which will show you only the last scan.

Nick Pilon

http://www.systemcenterdudes.com

[pvr02]

The code you provided is alot nicer than mine. However I still ran into the same issue:

 

[NickPilon]

Your query don't give me double like you on my side.

 

By the way I took more time to check your query and I handle it by removing v_BaselineTargetedComputers and v_ConfigurationItems.

 

Can you try to run the query below?

 

SELECT

SYS.Name0 AS Computer_Name,

MAX(CCS.LastComplianceMessageTime) AS 'Last_Scan',

USR.User_Name0 AS User_Name,

USR.Full_User_Name0 AS Full_Name,

CASE CCS.ComplianceState WHEN 1 THEN 'Compliant' WHEN 2 THEN 'Non-Compliant' ELSE 'Error' END AS 'Compliance_State',

CIP.DisplayName AS DCM_Baseline_Name

FROM

v_CICurrentComplianceStatus CCS

INNER JOIN v_LocalizedCIProperties_SiteLoc CIP ON CIP.CI_ID = CCS.CI_ID

INNER JOIN v_R_System SYS ON SYS.ResourceID = CCS.ResourceID

LEFT OUTER JOIN v_R_User USR ON USR.User_Name0 = SYS.User_Name0

WHERE CIP.DisplayName = 'U - IE Proxy (None) Baseline'

GROUP BY SYS.Name0, USR.User_Name0, USR.Full_User_Name0, CCS.ComplianceState, CIP.DisplayName

Nick Pilon

http://www.systemcenterdudes.com

[pvr02]

I appreciate the help, but unfortunatly, this didn't resolve my issue either. My results are the same as your original query above.

 

Just to clarify, I don't have duplicates for every computer. It only duplicates about 5 out of every 100 entries if I had to guess.

 

The only difference between the 2 line items is that one is Compliant and one is Non-Compliant.

[NickPilon]

Do you still have duplicate like this?

 

SELECT

SYS.Name0 AS Computer_Name,

CCS.ComplianceState AS 'Compliance_State',

MAX(CCS.LastComplianceMessageTime) AS 'Last_Scan'

FROM

v_CICurrentComplianceStatus CCS

INNER JOIN v_LocalizedCIProperties_SiteLoc CIP ON CIP.CI_ID = CCS.CI_ID

INNER JOIN v_R_System SYS ON SYS.ResourceID = CCS.ResourceID

WHERE CIP.DisplayName = 'U - IE Proxy (None) Baseline' AND SYS.Name0 = 'Computer2'

GROUP BY SYS.Name0, CCS.ComplianceState

Nick Pilon

http://www.systemcenterdudes.com

[pvr02]

I do get a duplicate when I try that.

 

I found a function called Row_Number this morning that I am currently testing. So far it appears to have removed the duplicates.

SELECT * FROM (

SELECT
SYS.Name0 AS Computer_Name,
MAX(CCS.LastComplianceMessageTime) AS 'Last_Scan',
USR.User_Name0 AS User_Name,
USR.Full_User_Name0 AS Full_Name,
CASE CCS.ComplianceState  WHEN 1 THEN 'Compliant' WHEN 2 THEN 'Non-Compliant' ELSE 'Error' END  AS 'Compliance_State',
CIP.DisplayName AS DCM_Baseline_Name,
ROW_NUMBER () OVER (PARTITION BY SYS.Name0 ORDER BY MAX(CCS.LastComplianceMessageTime)desc) AS 'SNo'
 
FROM
v_CICurrentComplianceStatus CCS
INNER JOIN v_LocalizedCIProperties_SiteLoc CIP ON CIP.CI_ID = CCS.CI_ID
INNER JOIN v_R_System SYS ON SYS.ResourceID = CCS.ResourceID
LEFT OUTER JOIN v_R_User USR ON USR.User_Name0 = SYS.User_Name0

WHERE CIP.DisplayName = 'U - IE Proxy (None) Baseline'
GROUP BY SYS.Name0, USR.User_Name0, USR.Full_User_Name0, CCS.ComplianceState, CIP.DisplayName
) compliance
WHERE SNo = '1'