Report Count Local Printers

select Distinct
    PD.Name0,
    count(*)
from
    dbo.v_GS_PRINTER_DEVICE PD
Group by
    PD.Name0
Order by
    PD.Name0

Read more →

Machines Count By Desktops/Notebooks Type

SELECT  
CASE ChassisTypes0
    WHEN '10' THEN 'Notebooks'
    ELSE 'Desktops'
END AS "Workstation Type", count(distinct sys.name0) as ClientCount from
v_GS_SYSTEM_ENCLOSURE ENC   
        INNER JOIN 
            v_R_System SYS ON ENC.ResourceID = SYS.ResourceID   
WHERE
    sys.client0=1 AND sys.obsolete0=0 AND active0=1
GROUP BY
    CASE ChassisTypes0
        WHEN '10' THEN 'Notebooks'
        ELSE 'Desktops'
    END
ORDER BY 2 desc

Read more →

SCCM Report Last HW Scan

select
        [sys].[AD_Site_Name0]
        , [sys].[Name0]
        , [ws].[LastHWScan]

    from v_GS_WORKSTATION_STATUS [ws]

    join v_R_System [sys] on [ws].[ResourceID] = [sys].[ResourceID]
    left join v_GS_OPERATING_SYSTEM [os] on [ws].[ResourceID] = [os].[ResourceID]

    order by LastHWScan desc

********************************

Report view example:

Read more →

SCCM Query Last Harware Scan

select 
SMS_G_System_LastSoftwareScan.LastScanDate, 
SMS_R_System.Name, 
SMS_G_System_WORKSTATION_STATUS.LastHardwareScan 
from  SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId order by SMS_G_System_WORKSTATION_STATUS.LastHardwareScan

Read more →

Machines Count By Server/Workstation OS Type

SELECT
ClientOSType =
CASE
    WHEN Operating_System_Name_and0 LIKE '%workstation%' THEN 'Total WorkStation OS'
    WHEN Operating_System_Name_and0 LIKE '%server%' THEN 'Total Server OS'
END,
COUNT(*)AS ClientCount
FROM v_r_system (NOLOCK)
WHERE
    client0 =1 AND obsolete0 =0 AND Active0=1
    AND Operating_System_Name_and0 IS NOT NULL
GROUP BY
CASE
    WHEN Operating_System_Name_and0 LIKE '%workstation%' THEN 'Total WorkStation OS'
    WHEN Operating_System_Name_and0 LIKE '%server%' THEN 'Total Server OS'
END

Read more →

Last Successful Site Server Backup Date

SELECT
    stat.MachineName AS ServerName,stat.SiteCode AS SiteCode,MAX(stat.Time) AS LastBackUpTime
FROM
    (SELECT a.* FROM v_StatusMessage a join v_StatusMessage b
    on a.sitecode = b.sitecode
    AND a.modulename = b.modulename
    AND a.recordid > b.recordid
WHERE
    a.component = 'SMS_SITE_BACKUP'
    AND b.Component = 'SMS_SITE_BACKUP'
    AND a.MessageID in (501)
    AND b.messageID = 500
    AND not exists
(SELECT * FROM v_StatusMessage where component = 'SMS_SITE_BACKUP' and sitecode = a.sitecode and messageid = 4610 and recordid > b.recordid and recordid < a.recordid)
and not exists
(SELECT * FROM v_StatusMessage where component = 'SMS_SITE_BACKUP' and sitecode = a.sitecode and messageid = 500 and recordid > b.recordid and recordid < a.recordid)
and not exists
(SELECT * FROM v_StatusMessage where component = 'SMS_SITE_BACKUP' and sitecode = a.sitecode and messageid in (501) and recordid > b.recordid and recordid < a.recordid)
) stat INNER JOIN v_Site sites
                on sites.ServerName = stat.MachineName
WHERE
    sites.Type = 2
GROUP BY
    stat.SiteCode, stat.MachineName
ORDER BY 3

Read more →

Count Office Suite Versions

select
DisplayName0,
Count (Distinct arp.ResourceID) as 'Client Count'
From
dbo.v_Add_Remove_Programs ARP
Where
DisplayName0 in ( 'Microsoft Office Professional Edition 2003','Microsoft Office Standard Edition 2003','Microsoft Office Enterprise 2007','Microsoft Office Standard 2007','Microsoft Office Professional Plus 2007','Microsoft Office 2000 SR-1 Professional','Microsoft Office 2000 SR-1 Standard','Microsoft Office Professional Plus 2010','Microsoft Office Standard 2010','Microsoft Office Enterprise 2007')
Group by DisplayName0
Order by Count(*) desc

Read more →

Visio/Project Version Count

select
DisplayName0,
Count (Distinct arp.ResourceID) as 'Client Count'
From
dbo.v_Add_Remove_Programs ARP
Where
DisplayName0 in ('Microsoft Visio Standard 2010','Microsoft Visio 2010 (Technical Preview)','Microsoft Office Visio Standard 2007','Microsoft Office Visio Standard 2003','Microsoft Office Visio Professional 2007 Trial','Microsoft Office Visio Professional 2007','Microsoft Office Visio Professional 2003','Microsoft Office Visio 2010','Microsoft Office Project Standard 2007 Trial','Microsoft Office Project Standard 2007','Microsoft Office Visio Professional 2007','Microsoft Office Project Professional 2010','Microsoft Office Project Professional 2007 Trial','Microsoft Office Project Professional 2007','Microsoft Office Project Professional 2003','Microsoft Office Visio Professional 2010','Microsoft Office Visio Professional 2010 Trial','Microsoft Office Project Standard 2010')
Group by DisplayName0
Order by Count(*) desc

Read more →

Patch Compliance Progression Report

select
      CS.Name0,
      CS.UserName0,
case
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ('Needs '+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ' Patches'))
else 'Good Client'
end as 'Status',
      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 = 'SMS00001'
Group by
      CS.Name0,
      CS.UserName0,
      ws.lasthwscan,
      FCM.collectionID
Order by
      CS.Name0,
      CS.UserName0,
      ws.lasthwscan,
      FCM.collectionID

Read more →