Saturday, May 25, 2019

Get list of Devices not checked into SCCM for X days

SCCM 2012 Written by  September 12 2018 font size decrease font size increase font size
Rate this item
(1 Vote)

i have recently been putting in more automation scripts to alert myself and others around me of things that are and are not working in the school,

Since most of my time is spread accross multiple sites i have been putting together tasks delegated to my team members and other staff that are able to follow things up such as in this case 

DEVICES THAT HAVE NOT CHECKED IN FOR X Days (30 days for this script below.)

 

for now i have just generated the sql statement and will soon add it into my SCCM Subscriptions to email me and others of our team if actions need to be followed up. 

in my sites database i use the following QUERY.

 

SELECT

A.Name0,

MAX (B.SerialNumber0) AS 'Serialnumber',

A.Manufacturer0,

A.Model0, C.Name0 AS 'Processor',

D.TotalPhysicalMemory0 AS 'Memory (KBytes)',

MAX ( E.Size0 ) AS 'Size (MBytes)',

MAX (F.MACAddress0) AS 'MAC Adress',

MAX (F.IPAddress0) AS 'IP Adress',

G.AD_Site_Name0 AS 'AD Site',

MAX (A.UserName0) AS 'Last user logged in',

H.Caption0 AS 'Operating System',

H.CSDVersion0 AS 'Service Pack',

G.Creation_Date0 AS 'Creationdate in SMS',

I.LastHWScan,

O.LastPolicyRequest

 

FROM

v_GS_COMPUTER_SYSTEM A,

v_GS_PC_BIOS B,

v_GS_PROCESSOR C,

v_GS_X86_PC_MEMORY D,

v_GS_DISK E,

v_GS_NETWORK_ADAPTER_CONFIGUR F,

v_R_System G,

v_GS_OPERATING_SYSTEM H,

v_GS_WORKSTATION_STATUS I,

vWorkstationStatus O

 

WHERE

A.ResourceID = B.ResourceID AND

A.ResourceID = C.ResourceID AND

A.ResourceID = D.ResourceID AND

A.ResourceID = E.ResourceID AND

A.ResourceID = F.ResourceID AND

A.ResourceID = G.ResourceID AND

A.ResourceID = H.ResourceID AND

A.ResourceID = I.ResourceID AND

A.ResourceID = O.ResourceID AND

O.LastPolicyRequest <= GETDATE() -30

 

GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan, O.LastPolicyRequest

 

 

Read 4131 times Last modified on September 12 2018
Login to post comments