Month: October 2011

So that one is great and all, but here’s one that presents a little more detail

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 not like ‘%server%’–GROUP BY NAME0 and (datediff(day, creation_date0, {fn Now()}) < 8)ORDER BY NAME0, age DESC

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 > 0 order by servername, downsince

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 by operating_system_name_and0orderby Total desc

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 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–, fromv_UpdateComplianceStatus UCSleft outer join dbo.v_GS_COMPUTER_SYSTEM CS on […]

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 update(s)], 0) + ISNULL([Waiting for another installation to complete], 0) + ISNULL([Pending system restart], 0) + ISNULL([Downloading update(s)], 0)))*100) as Numeric(10,2)) as varchar(256)) + ‘%’ AS ‘% […]

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 dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, DATEDIFF(Day,dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) AS [Days since last boot], dbo.v_GS_OPERATING_SYSTEM.Caption0 FROM         dbo.v_GS_OPERATING_SYSTEM INNER JOIN dbo.v_R_System ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID WHERE     dbo.v_R_System.Name0 in (SELECT     vrs.Name0 FROM v_r_system AS […]

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 you can act on the results:   declare @LookupString as nvarchar set @LookupString = ‘%’   SELECT     vrs.Name0 FROM v_r_system AS vrs JOIN v_GS_INSTALLED_SOFTWARE AS VIS ON […]