Jump to content


pvr02

Delete Aged DCM Scan data

Recommended Posts

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?

Share this post


Link to post
Share on other sites


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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'

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...