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 :

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

SCCM Report to get Primary partition less than 1GB on C: drive

SQL Query :


Select
SD.Name0 'Machine Name',
SD.User_Name0 'User Name',
LD.FreeSpace0 'Free Space'
From v_R_System SD
Join v_Gs_Logical_Disk LD on SD.ResourceId = LD.ResourceId
Where LD.DeviceId0 = 'C:'
And LD.FreeSpace0 < 1024
And SD.Client0 = 1

Tag : Web Report

Report to get Hostname and Username using SCCM

Query :

SELECT distinct sys.Netbios_Name0 ComputerName, sys.user_name0,ipsub.IP_Subnets0, ip.defaultIPGateway0, sys.AD_site_name0 ADSite
from v_R_system sys inner join v_GS_Network_Adapter_Configur IP on sys.ResourceID=IP.ResourceID inner join v_RA_System_IPSubnets ipsub on sys.ResourceID=ipsub.ResourceID
where sys.resource_domain_or_workgr0 = 'OM' and sys.obsolete0=0 and ip.defaultIPGateway0 is not NULL and ipsub.IP_Subnets0 is not NULL;

Output :

How to create boundaries in Protected mode - SCCM

Site Database -> Site Management -> Site code -> Site Settings -> Site Systems -> server name -> Configmgr Site system -> properties

Select boundary range and OK.

Command line for some software distribution in SCCM

1) RealVNC : vnc-E4_2_9-x86_win32.exe /SP- /VERYSILENT /NORESTART

2) Putty : putty-0.60-installer.exe /sp- /silent

3)SQLYog : SQLyog714_1.exe /S

4) WinSCP : winscp416setup.exe /SILENT /NORESTART

5) Tortoise CVS : TortoiseCVS-1.6.14.exe /verysilent /norestart /sp-

6) Edit Plus : epp312.exe -s -sp-s

7) Apache Tomcat : apache-tomcat-6.0.14.exe /S /D=C:\MyTomcat
Note : /D is directory where tomcat needs to be installed, you can change drive letter as you wish.

8) Mozilla Firefox : "Firefox Setup 9.0.1.exe" -ms

9) WireShark : wireshark-win32-1.6.2.exe /NCRC /S /desktopicon=yes /quicklaunchicon=no /D=C:\Program Files\Foo
Note : /D is directory where tomcat needs to be installed, you can change drive letter as you wish.

These are the command lines you need to specify when creating program in SCCM. keeping in mind that you guys know creation of software distribution in SCCM.

Creating software distribution, refer this link : http://www.windows-noob.com/forums/index.php?/topic/499-how-can-i-deploy-an-application-in-sccm-2007/