Convert AD Logon Time to Readable Dates
If you have added the lastLogonTimestamp field into the AD System Discovery, you can use this SQL Statement:
Read article →Archive
Category: Technology
Determine machines less than a Pentium IV
Query to determine machines less than a Pentium
Read article →Get Update Compliance Status
So that one is great and all, but here’s one that presents a little more detail
Read article →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_systemwhere (1=1)--and ad_site_name0 is null--and client0 is null--and ad_site_name0 is not nulland operating_system_name_and0 not like '%mac%'and operating_system_name_and0…
Read article →Determine which Distribution Points are Offline
select distinct ServerName,vss.percentfree, vss.status, dp.sitecode, dp.bitsenabled, vss.DownSince,vss.TimeReported from vSummarizer_SiteSystem as vss left join v_DistributionPointInfo as dp on vss.SiteSystem = dp.NALPath where vss.role = 'SMS Distribution Point' and vss.status…
Read article →Get Count of All Machines By Platform or OS
This handly little SQL Script will get you a count of all Machines in SCCM by Operating System: select count(operating_system_name_and0 ) as Total , operating_system_name_and0 from v_r_system group…
Read article →Patches Missing or Needed By CollectionID
declare @collection varchar set @collection = ‘%’ selectCS.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…
Read article →Get the Status of All Update Deployment Packages
Select Deploymentname, Available, Deadline, cast(cast(((cast([Compliant] as float) / (ISNULL([Compliant], 0) + ISNULL([Enforcement state unknown], 0) + ISNULL([Successfully installed update(s)], 0) + ISNULL([Failed to install update(s)], 0) + ISNULL([Installing…
Read article →Showing all Computers (With Boot Time)
Showing All computers in SCCM that have software installed, with the last boot time listed DECLARE @lookup as varchar (50) set @lookup = ‘%’ SELECT TOP (100) PERCENT…
Read article →Getting All Machines Containing a Specific Software Package
So, Let’s say you need to see all machines that contain a specific software package, you can use sccm’s gui, or you can do it in SQL, where…
Read article →