Blog

Convert AD Logon Time to Readable Dates

select

*,

DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) as LastLogonDay,

DATEDIFF(dd,DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) , GETDATE()) DaysSinceLastLogon



from v_R_System vrs

—————-

If you have added the lastLogonTimestamp field into the AD System Discovery, you can use this SQL Statement:


select

*,

DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) as LastLogonDay,

DATEDIFF(dd,DATEADD(hh, cast((LastLogon0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) , GETDATE()) DaysSinceLastLogon,

DATEADD(hh, cast((LastLogonTimestamp0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) as LastLogonTimeStamp,

DATEDIFF(dd,DATEADD(hh, cast((LastLogonTimestamp0 / (600000000))/ 60 as bigint), cast(‘1601-1-1’ as datetime2) ) , GETDATE()) DaysSinceLastLogonTimeStamp

from v_R_System vrs

Determine machines less than a Pentium IV

Query to determine machines less than a Pentium 4

SELECT distinct vrs.Name0, vps.Name0, vps.MaxClockSpeed0

  FROMv_R_System vrs

inner join[v_GS_PROCESSOR] vps on vrs.ResourceID = vps.ResourceID

 

  Where

 vps.Name0 like ‘%Pentium%4%’ or

  vps.Name0 like ‘%Pentium%III%’or

  vps.Name0 like ‘%Pentium%II%’  or

vps.Name0 like ‘%Pentium%M%’

Get Update Compliance Status

select su.bulletinid, ui.description, ui.title,count(status) NumberOfMachinesInState, sn.statename, ui.daterevised

from v_UpdateComplianceStatus UCS

join vSMS_SoftwareUpdate SU on SU.CI_ID = ucs.CI_ID

join v_StateNames sn on ucs.status = sn.stateid

join v_updateinfo ui on ui.ci_id = ucs.ci_id


where (1=1)

and
su.isSuperseded = 0

and sn.topictype = 300

group by ucs.ci_id, status, su.issuperseded, sn.statename,su.bulletinid, ui.description, ui.title, ui.daterevised

order by daterevised, title, description,bulletinid

[updated: 10/27/11 – 15:22)
So this is great and all, but there’s a better table that has all the summary information that you need, here’s the statement:

select 

su
.bulletinid,

ui
.description,

ui
.title,

ui
.daterevised,

UCS.LastSummaryTime,

UCS.NumTotal,

UCS.NumUnknown,

UCS.NumPresent,

UCS.NumInstalled,

UCS.NumFailed,

UCS.NumNotApplicable,

UCS.NumMissing

from v_Update_ComplianceSummary UCS

join vSMS_SoftwareUpdate SU on SU.CI_ID = ucs.CI_ID

join v_updateinfo ui on ui.ci_id = ucs.ci_id


where (1=1)

and
su.isSuperseded = 0

order by daterevised, title, description,bulletinid

Find Machines added to SCCM in the last 7 Days

select distinct (name0) ,creation_date0,datediff(day, creation_date0, {fn Now()}) as Age, CLIENT0,ad_site_name0,active0

from v_r_system


where (1=1)

--and ad_site_name0 is null
--and client0 is null
--and ad_site_name0 is not null
and operating_system_name_and0 not like '%mac%'

and operating_system_name_and0 not like '%server%'

--GROUP BY NAME0
and (datediff(day, creation_date0, {fn Now()}) < 8)

ORDER BY NAME0, age DESC

Patches Missing or Needed By CollectionID

declare @collection varchar

set @collection = ‘%’

select

CS.Name0,

CS.Domain0,

CS.UserName0,

CS.Manufacturer0,

CS.Model0,

CS.NumberOfProcessors0,

CS.Status0,

CS.SystemType0,

case

when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ((cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))))

else ‘Good Client’

end as ‘Missing Patches’,

ws.lasthwscan as ‘Last HW scan’,

FCM.collectionID–,

from

v_UpdateComplianceStatus UCS

left outer join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = UCS.ResourceID

join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID

join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=‘UpdateClassification’

left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid

left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid

Where

UCS.Status = ‘2’

and FCM.collectionid like @collection

Group by

CS.Name0,

CS.UserName0,

ws.lasthwscan,

FCM.collectionID,

CS.Domain0,

CS.Manufacturer0,

CS.Model0,

CS.NumberOfProcessors0,

CS.Status0,

CS.SystemType0

Order by

CS.Name0,

CS.UserName0,

ws.lasthwscan,

FCM.collectionID