Useful SCCM Reporting

Query to get all report in console :

SQL Query : 


SELECT [ReportID]
      ,[SecurityKey]
      ,[Name]
      ,[Comment]
      ,[Category]
      ,[SQLQuery]
      ,[GraphType]
      ,[GraphCaption]
      ,[GraphXCol]
      ,[XColLabel]
      ,[GraphYCol]
      ,[YColLabel]
      ,[DrillThroughReportID]
      ,[MachineDetail]
      ,[MachineSource]
      ,[RefreshInterval]
      ,[DrillThroughURL]
      ,[StatusMessageDetailSource]
  FROM [SCCM2007R3].[dbo].[v_Report]
------------------------------------------------------------------------------------------
Workstation UP Time :


SQL Query :


SELECT     TOP (100) PERCENT os.Caption0 AS 'Operating System', cs.Name0 AS Name, DATEDIFF(hour, os.LastBootUpTime0, ws.LastHWScan) AS 'Uptime (in Hours)',
                      CONVERT(varchar(20), os.LastBootUpTime0, 100) AS 'Last Reboot Date/Time', CONVERT(varchar(20), ws.LastHWScan, 100) AS 'Last Hardware Inventory'
FROM         dbo.v_GS_WORKSTATION_STATUS AS ws LEFT OUTER JOIN
                      dbo.v_GS_OPERATING_SYSTEM AS os ON ws.ResourceID = os.ResourceID INNER JOIN
                      dbo.v_GS_COMPUTER_SYSTEM AS cs ON cs.ResourceID = os.ResourceID
WHERE     (ws.LastHWScan <> 0) AND (cs.Name0 IS NOT NULL) AND (os.Caption0 LIKE '%xp%') OR
                      (os.Caption0 LIKE '%7%') OR
                      (os.Caption0 LIKE '%vista%')
ORDER BY Name


---------------------------------------------------------------------------------------------

Report to find Non-Compliance software's in Organisation :


SQL Query,

This report will show you application list other than what you specified in this query,i added compliance software's MS Office,Adobe Air, etc.,


SELECT DISTINCT
  v_R_System.Netbios_Name0,
  ARP.DisplayName0,
  ARP.Version0,
  ARP.InstallDate0,
  ARP.ProdID0,
  HWSCAN.LastHWScan as "Last SCCM Hardware Scan"
FROM
  v_R_System
  JOIN v_GS_ADD_REMOVE_PROGRAMS ARP on v_R_System.ResourceID = ARP.ResourceID
  JOIN v_FullCollectionMembership on v_R_System.ResourceID =

v_FullCollectionMembership.ResourceID
  LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on v_R_System.ResourceID =

HWSCAN.ResourceID
WHERE
  v_FullCollectionMembership.CollectionID = @CollID and
  (ARP.DisplayName0 Not like '%Microsoft Office%')and
  (ARP.DisplayName0 Not like '%Adobe AIR%')  and
  (ARP.DisplayName0 Not like '%Configuration Manager Client%')and
  (ARP.DisplayName0 Not like '% Mozilla Firefox%')and
  (ARP.DisplayName0 Not like '%LiveUpdate %')and
  (ARP.DisplayName0 Not like '%Skype%')
ORDER BY v_R_System.Netbios_Name0

Note : Set promt for select collection,

-------------------------------------------------------------------------------------------
SCCM Report to find Torrent installed machines list :


SQL Query : 


SELECT DISTINCT
RSYS.Name0 AS 'Computer',
RSYS.User_Name0 As 'Last User ID',
SF.FileName As 'File Name',
SF.FileDescription As 'File Description',
SF.FilePath As 'File Path',
SF.FileSize As 'File Size',
SF.FileVersion As 'File Version'
FROM
V_R_SYSTEM RSYS
INNER JOIN V_GS_SoftwareFile SF
ON RSYS.ResourceID = SF.ResourceID
AND (
SF.FileName Like '%Azureus%'
Or SF.FileName Like '%BitComet%'
Or SF.FileName Like '%BitLord%'
Or SF.FileName Like '%BitPump%'
Or SF.FileName Like '%BitTornado%'
Or SF.FileName Like '%BitTorrent%'
Or SF.FileName Like '%Shareaza%'
Or SF.FileName Like '%Utorrent%'
)
ORDER BY
RSYS.Name0

Output :

--------------------------------------------------------------------------------------------------