Saturday, September 21, 2013

SCCM Custom reports

This post provides various SQL queries to generate custom SCCM reports (07/12) for reporting purposes.
All of the queries from this post has tested in my lab. If you see any errors in a report please post a comment so I can fix them.
This page will be updated periodically.
Please note; When you are copying a SQL query from a web browser you need to make sure that the browser is not adding any extra characters.
SCCM Report for packages where on demand distribution is enabled:
This query will provide package name and on demand distribution status.
If on demand distribution status is set to 1, then on demand is enabled for that package.
********************************************************************
SELECT  Name, 
   (PkgFlags&0x40000000)/0x40000000 AS PKG_DISTRIBUTE_ON_DEMAND 
FROM  dbo.v_Package pkg

********************************************************************
SCCM Report for Windows 10 versions:
The following report provide you the machine name, logged on user name, Operating System build number and Operating system version for a specific collection.
This report is useful when performing Windows 10 servicing.
*****************************************************************************
SELECT v_R_System.Name0 AS [Machine name], v_R_System.User_Name0 AS [User name], v_GS_OPERATING_SYSTEM.BuildNumber0 AS [OS Build Number], v_GS_OPERATING_SYSTEM.Caption0 AS [OS version]
FROM v_GS_OPERATING_SYSTEM INNER JOIN v_R_System ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID INNER JOIN
v_FullCollectionMembership ON v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
WHERE (v_FullCollectionMembership.CollectionID = N'SCB00018')
*****************************************************************************
SCCM Report for status of SCCM predefined maintenance tasks:
When troubleshooting SCCM maintenance tasks, it is important to verify that predefined maintenance tasks are running as per the schedule.
The below simple query will give us the Task name, Last start time, Last completion time and status of the completed task.
Run this query in SQL Management studio.
********************************************************************************
SELECT TaskName, LastStartTime, LastCompletionTime, CompletionStatus
FROM   SQLTaskStatus
********************************************************************************
SCCM Report for custom hardware inventory:
The following SCCM report will provide Computer Name, Manufacturer, Model, Serial number, OS Version, Service Pack, Last logged on User, OS deployed date, Architecture, IP Address, Last Boot Time and Last H/W Scan for a nominated collection.
Change the collection ID as required.
********************************************************************************
SELECT DISTINCT CS.Name0 AS 'Computer Name', CS.Manufacturer0 AS 'Manufacturer', CS.Model0 AS 'Model', BIOS.SerialNumber0 AS 'Bios serial', OS.Caption0 AS 'OS Version', 
                      OS.CSDVersion0 AS 'Service Pack', CS.UserName0 AS 'Logged on User', OS.InstallDate0 AS [OS deployed on], SYS.SystemType0 AS Architecture, 
                      v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address], OS.LastBootUpTime0 AS [Last Boot Time], 
                      v_GS_WORKSTATION_STATUS.LastHWScan AS [Last H/W Scan]
FROM v_GS_COMPUTER_SYSTEM AS CS RIGHT OUTER JOIN
                      v_GS_PC_BIOS AS BIOS ON BIOS.ResourceID = CS.ResourceID RIGHT OUTER JOIN
                      v_GS_SYSTEM AS SYS ON SYS.ResourceID = CS.ResourceID RIGHT OUTER JOIN
                      v_GS_OPERATING_SYSTEM AS OS ON OS.ResourceID = CS.ResourceID INNER JOIN
                      v_GS_SYSTEM_ENCLOSURE AS ES ON SYS.ResourceID = ES.ResourceID INNER JOIN
                      v_GS_WORKSTATION_STATUS ON BIOS.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID LEFT OUTER JOIN
                      v_FullCollectionMembership ON CS.ResourceID = v_FullCollectionMembership.ResourceID LEFT OUTER JOIN
                      v_RA_System_IPAddresses ON BIOS.ResourceID = v_RA_System_IPAddresses.ResourceID
WHERE     (v_FullCollectionMembership.CollectionID = 'LAB00260')
GROUP BY CS.Manufacturer0, CS.Model0, ES.ChassisTypes0, BIOS.SerialNumber0, CS.Name0, OS.Caption0, OS.CSDVersion0, CS.UserName0, OS.InstallDate0, SYS.SystemType0, v_RA_System_IPAddresses.IP_Addresses0, OS.LastBootUpTime0, v_GS_WORKSTATION_STATUS.LastHWScan

********************************************************************************
 SCCM Report for all the applications created in SCCM:
This report is for all the applications available in SCCM. Please note this is NOT for an inventory from client devices.
The following report provides;
- Application Name
- Application Vendor and
- Application Version
*********************************************************************
SELECT CI_UniqueID, DisplayName AS [Application Name], Manufacturer AS [Application Vendor], SoftwareVersion AS [Software Version]
FROM dbo.fn_ListLatestApplicationCIs(1033)

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

SCCM Report for all the packages created in SCCM :
This report provides list of packages created in SCCM. This report is not an inventory for installed applications on the client devices.
The report contains;
- Package Name
- Vendor and
- Version
*************************************************************************
SELECT Program.PackageID, Package.Name AS 'Package Name', Package.Manufacturer AS Vendor, Package.Version
FROM v_Program AS Program LEFT OUTER JOIN
     v_Package AS Package ON Package.PackageID = Program.PackageID
ORDER BY Program.PackageID
*************************************************************************

 SCCM Report for Installed versions of Google Earth:
 The following report will provide all installed versions of Google Earth along with the version number, user name and machien name.
*****************************************************************************
SELECT DISTINCT sys.Netbios_Name0 AS [Machine Name], sys.User_Name0 AS [User Name],
    arp.DisplayName0 AS [MS Project version]
FROM       v_R_System AS sys
INNER JOIN v_Add_Remove_Programs AS arp ON sys.ResourceID = arp.ResourceID
WHERE      (arp.DisplayName0 LIKE '%Google Earth%')

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

SCCM Report for WSUS location:
Below SQL query will provide WSUS location from the devices.Please read the full article here.
***********************************************
SELECT     v_GS_COMPUTER_SYSTEM.Name0 AS [Machine Name], WSUSLocation_DATA.WUServer00 AS [WSUS Server Name]
FROM         WSUSLocation_DATA CROSS JOIN
                      v_GS_COMPUTER_SYSTEM
GROUP BY v_GS_COMPUTER_SYSTEM.Name0, WSUSLocation_DATA.WUServer00, WSUSLocation_DATA.WUStatusServer00

************************************************
SCCM Report for all installed versions of Project Professional:
This report will provide all the installed versions of Microsoft Project Professional including Machine names and user names.
The user name is addition to the SCCM out of box report
***********************************************************************************
SELECT DISTINCT sys.Netbios_Name0 AS [Machine Name], sys.User_Name0 AS [User Name],
    arp.DisplayName0 AS [MS Project version]
FROM       v_R_System AS sys
INNER JOIN v_Add_Remove_Programs AS arp ON sys.ResourceID = arp.ResourceID
WHERE      (arp.DisplayName0 LIKE '%Microsoft Project Professional%')

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


SCCM Report for all software installed for all computers:
 The following report will give you all the installed software’s (applications from the environment) and removing the installed hotfixes, security updates and any other updates. If you want to remove any other product from the report, use DisplayName0 NOT LIKE 'Product name %'.
All the inventoried software will be grouped using displayname, version and publisher to remove all the duplicate records.
**************************************************************************
SELECT    DisplayName0 AS [Product Name], Version0 AS [Product Version], Publisher0 AS [Publisher Name]
FROM      v_GS_ADD_REMOVE_PROGRAMS
WHERE     (DisplayName0 NOT LIKE 'Hotfix for %') AND (DisplayName0 NOT LIKE 'Security Update for %') AND (DisplayName0 NOT               LIKE 'Update %')
GROUP BY  DisplayName0, Version0, Publisher0

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

 SCCM Report for local printers:
 Below SCCM report provides all the attached local printer information from a machine.

Collecting local printer inventory involves 2 tasks.

1. Enable Printer Device (Win32_Printer) class in Hardware Inventory classes from \Administration\Overview\Client Settings
2. Run below report to collect the inventoried printer information in a report format
Below report provides all the attached printer information excluding;
- Microsoft XPS Document Writer
- Adobe PDF Converter
- Microsoft Shared Fax Driver
- TP Output Gateway
- pdfFactory

 ********************************************************************************************************
SELECT  SYS.Netbios_Name0 AS [Computer Name], printer.DriverName0 AS [Printer Name], printer.PortName0 AS [Port Type],
    printer.Location0 AS [Device Location]
FROM    v_GS_PRINTER_DEVICE AS printer INNER JOIN
        v_R_System AS SYS ON SYS.ResourceID = printer.ResourceID
WHERE   (NOT (printer.DriverName0 LIKE 'Microsoft XPS Document Writer')) AND
    (NOT (printer.DriverName0 LIKE 'Adobe PDF Converter')) AND
        (NOT (printer.DriverName0 LIKE 'Microsoft Shared Fax Driver')) AND
    (NOT (printer.DriverName0 LIKE '%TP Output Gateway%')) AND
        (NOT (printer.DriverName0 LIKE '%pdfFactory%'))
ORDER BY [Computer Name]

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

SCCM Report workstation architecture:
 Previously I have created this report to list all the devices and architecture.
However, the following report will list only workstations (excludes servers) with hostname and device OS architecture type.
********************************************************************************************
SELECT     Name AS [Machine Name], SystemType AS [System Architecture]
FROM         vWorkstationStatus
WHERE     (SystemRole = 'Workstation')

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


SCCM Report CRM versions:
Below report will provide information of;
      - Host Name
      - Operating System
      - CRM Product
      - Publisher of the product
      - Product Version
      - Product Installed date
****************************************************************************************************
SELECT DISTINCT
                      v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName AS [Product Name],
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher IS NULL OR
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = '-1')
                      THEN 'Unknown' ELSE v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher END AS Publisher,
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion IS NULL OR
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion = '-1')
                      THEN 'Unknown' ELSE v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion END AS Version,
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 IS NULL)
                      THEN 'Unknown' ELSE CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 AS varchar) END AS [Install Date]
FROM         v_GS_INSTALLED_SOFTWARE_CATEGORIZED INNER JOIN
                      v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID INNER JOIN
                      v_GS_OPERATING_SYSTEM ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE     (v_GS_OPERATING_SYSTEM.Caption0 LIKE '%server%') AND (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE '%CRM%')
ORDER BY [Computer Name], [Product Name], Publisher, Version


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

SCCM Report for Exchange Server Versions:.
 Below report will provide information of;
      - Host Name
      - Operating System
      - Exchange Server Product
      - Publisher of the product
      - Product Version
      - Product Installed date
****************************************************************************************************
SELECT DISTINCT
                      v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName AS [Product Name],
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher IS NULL OR
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = '-1')
                      THEN 'Unknown' ELSE v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher END AS Publisher,
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion IS NULL OR
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion = '-1')
                      THEN 'Unknown' ELSE v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion END AS Version,
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 IS NULL)
                      THEN 'Unknown' ELSE CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 AS varchar) END AS [Install Date]
FROM         v_GS_INSTALLED_SOFTWARE_CATEGORIZED INNER JOIN
                      v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID INNER JOIN
                      v_GS_OPERATING_SYSTEM ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE     (v_GS_OPERATING_SYSTEM.Caption0 LIKE '%server%') AND (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%exchange%') AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%commvault%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%Signatures%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%updates%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%Plug-in%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%Monitor%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'%Connector%')) AND
                      (NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE N'% Language%'))
ORDER BY [Computer Name], [Product Name], Publisher, Version

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

SCCM Report for SQL Versions:
Below report will provide information of;
     - Host Name
     - Operating System
     - SQL Product
     - Publisher of the product
     - Product Version
     - Product Installed date
***********************************************************************************
SELECT DISTINCT
                      v_R_System_Valid.Netbios_Name0 AS [Computer Name], v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName AS [Product Name],
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher IS NULL OR
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher = '-1')
                      THEN 'Unknown' ELSE v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher END AS Publisher,
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion IS NULL OR
                      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion = '-1')
                      THEN 'Unknown' ELSE v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedVersion END AS Version,
                      CASE WHEN (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 IS NULL)
                      THEN 'Unknown' ELSE CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.InstallDate0 AS varchar) END AS [Install Date]
FROM         v_GS_INSTALLED_SOFTWARE_CATEGORIZED INNER JOIN
                      v_R_System_Valid ON v_R_System_Valid.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID INNER JOIN
                      v_GS_OPERATING_SYSTEM ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE     (v_GS_OPERATING_SYSTEM.Caption0 LIKE '%server%') AND (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName LIKE '%SQL%') AND
                      (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName NOT LIKE '%arcserve%') AND
                      (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName NOT LIKE '%hotfix%') AND
                      (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName NOT LIKE '%books%') AND
                      (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName NOT LIKE '%Tools%') AND
                      (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName NOT LIKE '%Compatibility%') AND
                      (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName NOT LIKE '%setup support%')
ORDER BY [Computer Name], [Product Name], Publisher, Version


***********************************************************************************
SCCM Report for list all the tables from SCCM database:
The following query gives all the available tables (including custom hardware inventory classes).
****************************************************
Select distinct table_name,COLUMN_NAME From Information_Schema.columns
where table_name LIKE ('v_%')
Order by table_name

****************************************************
SCCM Report for Machine name, MAC address and IPAddress:
*****************************************************************
SELECT     v_RA_System_ResourceNames.Resource_Names0 AS [Resource name], v_RA_System_MACAddresses.MAC_Addresses0 AS [MAC Address],
v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address]
FROM         v_RA_System_MACAddresses INNER JOIN
v_RA_System_ResourceNames ON v_RA_System_MACAddresses.ResourceID = v_RA_System_ResourceNames.ResourceID INNER JOIN
v_RA_System_IPAddresses ON v_RA_System_MACAddresses.ResourceID = v_RA_System_IPAddresses.ResourceID
***************************************************************
SCCM Report for Installed versions of IE:
****************************************************************
Select Distinct
     sys.Netbios_Name0,
     sys.User_Domain0,
     sys.User_Name0,

     arp.Displayname0

FROM v_R_System sys

JOIN  v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID

WHERE ARP.DisplayName0 like '%Internet Explorer%' and (DisplayName0 not like '%Security Update%' and DisplayName0 not like '%Update For Windows%' and DisplayName0 not like 'Hotfix%')
*******************************************************************
*****An updated article published here to get all the versions of Internet explorer.*****
SCCM report for Operating System:
Following custom SCCM report provides Machine NetBIOS name, user domain, user name, Operating system (Windows 7)
*********************************************************************************
SELECT Distinct
 sys.Netbios_Name0,
 sys.User_Domain0,
 sys.User_Name0,
 os.caption0
FROM
     v_R_System sys
INNER JOIN
     v_GS_COMPUTER_SYSTEM cs on sys.resourceID = cs.resourceID
INNER JOIN
     V_GS_Operating_system os on sys.resourceID = os.resourceID
WHERE
     os.caption0 like '%Windows 7%'
**********************************************************************************
The os.caption0 can be changed any operating system (%Windows XP% or %Windows 7% ) or all the windows operating systems by %Windows%.

The below SCCM Custom report is useful to identify installed application based on display name in Add-remove programs and generate a report
**********************************************************************************
Select
     sys.Netbios_Name0,
     sys.User_Domain0,
     sys.User_Name0,
     sys.Operating_System_Name_and0,
     arp.DisplayName0,
     ARP.Version0
FROM
     v_R_System sys
     JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE
     Displayname0 like '%Application Name%'

*********************************************************************************
Example:
Creating a report for Adobe Reader.
*********************************************************************************
Select
     sys.Netbios_Name0,
     sys.User_Domain0,
     sys.User_Name0,
     sys.Operating_System_Name_and0,
     arp.DisplayName0,
     ARP.Version0
FROM
     v_R_System sys
     JOIN v_Add_Remove_Programs arp ON sys.ResourceID = arp.ResourceID
WHERE
     Displayname0 like '%Adobe Reader%'

********************************************************************************
SCCM report for Computer Manufacturer, Model and total number of machines:
  The following query gives details of computer Manufacturer, model and total number of machines in the environment.
*********************************************************************************
SELECT Manufacturer0, Model0, Count(Model0) AS 'Count'
  FROM dbo.v_GS_COMPUTER_SYSTEM
    GROUP BY Manufacturer0,Model0
ORDER BY Model0

*********************************************************************************
This query gives all the packages status on all the Distribution Points.
*********************************************************************************
SELECT DPS.PackageID, PCK.Name, PCK.SourceSite,
       DPS.ServerNALPath, DPS.InstallStatus
FROM v_PackageStatusDistPointsSumm DPS INNER JOIN v_Package PCK
ON DPS.PackageID = PCK.PackageID
ORDER BY DPS.PackageID
********************************************************************
SCCM Report for all installed applications for a collection:
This report is a simplified version of in build Software 01A - Summary of installed software in a specific collection.
In this report we can get software title, Software version and number of instances installed in that collection.
********************************************************************
SELECT  A.DisplayName0, A.Version0, Count(*) AS 'Count'

FROM v_GS_ADD_REMOVE_PROGRAMS A, v_GS_COMPUTER_SYSTEM B, v_FullCollectionMembership C

WHERE A.ResourceID = B.ResourceID
    AND A.ResourceID = C.ResourceID
    AND A.DisplayName0 NOT LIKE 'Hotfix for %'
    AND A.DisplayName0 NOT LIKE 'Security Update for %'
    AND A.DisplayName0 NOT LIKE 'Update %'
    AND CollectionID = @collection

GROUP BY A.DisplayName0, A.Version0
ORDER BY A.DisplayName0
********************************************************************
SCCM report for MachineName Make Model Architecture Username RAM:
Below Report provides;   Machine name,  Manufacturer, Model Architecture, User name and Memory (RAM)size
***********************************************************
SELECT    v_GS_COMPUTER_SYSTEM.Name0 AS [Machine Name],
v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, v_GS_COMPUTER_SYSTEM.Model0 AS Model,
v_GS_COMPUTER_SYSTEM.SystemType0 AS Architecture, v_GS_COMPUTER_SYSTEM.UserName0 AS Username,
          v_GS_PHYSICAL_MEMORY.Capacity0 AS [RAM Size]
FROM      v_GS_COMPUTER_SYSTEM
INNER JOIN v_GS_PHYSICAL_MEMORY ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PHYSICAL_MEMORY.ResourceID
************************************************************
SCCM report for File sharing /Torrent software:
This reporting query is useful to get all installed versions of P2P / file sharing software's in the environment.
*******************************************************************
Select
SD.Name0,
sys1.User_Name0,
SF.FileName
From v_r_system sys1
join v_gs_softwarefile sf on sf.resourceid=sys1.resourceid
left join v_R_User usr on usr.User_Name0=sys1.User_Name0
left Join v_R_System SD on SD.ResourceId = SF.ResourceId
Where SF.FileName Like '%Azureus%'
Or SF.FileName Like '%BitComet%'
Or SF.FileName Like '%BitLord%'
Or SF.FileName Like '%BitTornado%'
Or SF.FileName Like '%BitTorrent%'
Or SF.FileName Like '%Shareaza%'
Or SF.FileName Like '%Utorrent%'
Or SF.FileName Like '%eDonkey%'
Or SF.FileName Like '%Emule%'
Or SF.FileName Like '%Kazaa%'
Or SF.FileName Like '%LimeWire%'
Order By SD.Name0, SF.FileName, sys1.User_Name0


*******************************************************
You can add more to it by adding Or SF.FileName Like '%<Application name>%'
This report will include Name,  whether the device laptop or desktop,  Serial no, Manufacturer, Model, OS, SP level, last logged on user, total memory and total HDD
****************************************************************
SELECT  distinct
  CS.Manufacturer0 as 'Manufacturer',
  CS.Model0 as 'model',
    CASE
        WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
        WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
        Else 'Unknown'
END as 'Description',
 BIOS.SerialNumber0 as 'Bios serial',
 CS.name0 as 'Computer Name',
 RAM.TotalPhysicalMemory0 as 'Total Memory',
 sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
 OS.Caption0 as 'OS',
 CSDVersion0 as 'Service Pack',
 CS.UserName0 as 'User',
 CS.domain0 as 'Domain'
from
  v_GS_COMPUTER_SYSTEM CS right join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
right join v_GS_SYSTEM SYS on SYS.ResourceID = CS.ResourceID
right join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
right join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
right join v_GS_Logical_Disk LDisk on LDisk.ResourceID = CS.ResourceID
INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ES on SYS.ResourceID = ES.ResourceID
where
 LDisk.DriveType0 =3
group by
  CS.Manufacturer0,
  CS.Model0,
  ChassisTypes0,
  BIOS.SerialNumber0,
  CS.Name0,
  RAM.TotalPhysicalMemory0,
  OS.Caption0,
  CSDVersion0,
  CS.Username0,
  CS.domain0

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

SCCM report for Computer name and MAC address:
This query is handy when we want to get MAC address for a machine in the environment, especially when we see No task sequence is available error in PXE boot.
***********************************************************************
Select
      v_R_System.Netbios_Name0, SYS.User_Name0,
      v_RA_System_MACAddresses.MAC_Addresses0
from
    v_R_System, v_R_System SYS, v_RA_System_MACaddresses,
    v_fullcollectionmembership
where
    v_ra_system_macaddresses.resourceid=v_r_system.resourceid
    and v_fullcollectionmembership.resourceid=v_ra_system_macaddresses.resourceid
    and SYS.resourceid=v_r_system.resourceid

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

SCCM report for computer Make Model count:
The following report will display the computer manufacturer, Model and Number of machines in the environment.

*************************************************************************
SELECT
Manufacturer0 AS Manufacturer,
Model0 AS Model,
COUNT (Model0) AS Count
FROM v_GS_COMPUTER_SYSTEM
GROUP BY Model0, Manufacturer0
ORDER BY Manufacturer0, Model0
*************************************************************************
SCCM Report for Server Name and Properties:
Following report provides Server name, Operating System version, manufacturer, Model Memory, Processor type, OS Install data
************************************************************************
SELECT distinct
CS.name0 as 'Server Name',
OS.Caption0 as 'OS',
CU.Manufacturer0 as 'Manufacturer',
CU.Model0 as 'Model',
RAM.TotalPhysicalMemory0/1024 as [RAM (MB)],
processor.Name0 as 'Processor',
BIOS.ReleaseDate0 as 'BIOS Manufacture Date',
OS.InstallDate0 as 'OS Install Date'
from
v_R_System CS
FULL join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
FULL join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
FULL join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
FULL JOIN v_GS_PROCESSOR Processor on Processor.ResourceID=CS.ResourceID
FULL join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
FULL join v_GS_COMPUTER_SYSTEM CU on CU.ResourceID = CS.ResourceID
WHERE CS.Operating_System_Name_and0 LIKE '%nt%server%' and CS.Client0 = 1
group by
CS.Name0,
OS.Caption0,
CU.Manufacturer0,
CU.Model0,
RAM.TotalPhysicalMemory0,
BIOS.ReleaseDate0,
OS.InstallDate0,
Processor.Name0,
BIOS.ReleaseDate0
Order by CS.Name0
**************************************************************************
SCCM report for Computers in a Specific sub net:
Following SCCM report provide Machine name, IP Subnet, IP Address Default gateway
**********************************************
SELECT Distinct SYS.Name0, IPSUB.IP_Subnets0, NETW.IPSubnet0,
NETW.IPAddress0, NETW.DefaultIPGateway0
FROM  v_RA_System_IPSubnets IPSUB, v_R_System SYS, 
v_Network_DATA_Serialized NETW
WHERE SYS.ResourceID = IPSUB.ResourceID AND
SYS.ResourceID = NETW.ResourceID AND
NETW.IPAddress0 IS NOT NULL AND
IP_Subnets0 LIKE @variable
Order by SYS.Name0

On prompt;
Name: variable
SQL:
begin
 if (@__filterwildcard = '')
   Select Distinct IPSUB.IP_Subnets0 from v_RA_System_IPSubnets IPSUB  order by IPSUB.IP_Subnets0
 else
   Select Distinct IPSUB.IP_Subnets0 from v_RA_System_IPSubnets IPSUB
   WHERE IPSUB.IP_Subnets0 like @__filterwildcard
   order by IPSUB.IP_Subnets0
end
**************************************************
Following report provides machine name, Operating System, Service pack level, Serial number, Model, memory IP address for a specific collection
************************************************************************
select distinct v_R_System_Valid.Netbios_Name0 AS [Computer Name],

v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],

v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level],

v_GS_SYSTEM_ENCLOSURE_UNIQUE.SerialNumber0 AS [Serial Number],

v_GS_COMPUTER_SYSTEM.Model0 AS [Model],

v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)],

v_RA_System_IPAddresses.IP_Addresses0

from v_R_System_Valid

inner join v_GS_OPERATING_SYSTEM on (v_GS_OPERATING_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)

left join v_GS_SYSTEM_ENCLOSURE_UNIQUE on (v_GS_SYSTEM_ENCLOSURE_UNIQUE.ResourceID = v_R_System_Valid.ResourceID)

inner join v_GS_COMPUTER_SYSTEM on (v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System_Valid.ResourceID)

inner join v_GS_X86_PC_MEMORY on (v_GS_X86_PC_MEMORY.ResourceID = v_R_System_Valid.ResourceID)

inner join v_GS_PROCESSOR on (v_GS_PROCESSOR.ResourceID = v_R_System_Valid.ResourceID)

inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = v_R_System_Valid.ResourceID)

left join v_Site on (v_FullCollectionMembership.SiteCode = v_Site.SiteCode)

inner join v_GS_LOGICAL_DISK on (v_GS_LOGICAL_DISK.ResourceID = v_R_System_Valid.ResourceID) and v_GS_LOGICAL_DISK.DeviceID0=SUBSTRING(v_GS_OPERATING_SYSTEM.WindowsDirectory0,1,2)

left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = v_R_System_Valid.ResourceID)

left join v_RA_System_IPAddresses on (v_FullCollectionMembership.ResourceID = v_RA_System_IPAddresses.ResourceID)

Where v_FullCollectionMembership.CollectionID = @COLLID

Order by [Computer Name]

On prompt:
Name: COLLID
SQL statement:
Select CollectionID,Name from v_Collection
********************************************
SCCM report for Server name and Operating System version:
This simple and easy report provides all the server names in the organization and provides the Operating System details.
******************************************************
Select sys.name0,
cs.manufacturer0,
os.caption0
from v_R_System sys
INNER JOIN v_GS_COMPUTER_SYSTEM cs on sys.resourceID = cs.resourceID
INNER JOIN V_GS_Operating_system os on sys.resourceID = os.resourceID
Where os.caption0 like '%server%'
******************************************************
SCCM report for Collection names and count of Sub Collections:
Following SCCM custom report provides names of all the collections where they have sub collections and count of the sub collections for each collection.
************************************************************
select top 10000
coll.CollectionID, coll.Name, Count (csc.parentCollectionID) as [Count of subcollections]
from v_collection coll
inner join v_CollectToSubCollect csc on coll.CollectionID = csc.parentCollectionID
group by coll.CollectionID, coll.Name
*************************************************************
SCCM report for Computer make, model and count:
This report provides computer manufacture name, model and number of machines in the environment.
**************************************************************
SELECT     Manufacturer0, Model0, Count(Model0) AS 'Count'
FROM         dbo.v_GS_COMPUTER_SYSTEM
GROUP BY Manufacturer0,Model0
ORDER BY Model0
***************************************************************
***************************************************************
SELECT     TOP (100) PERCENT DisplayName0, COUNT(DisplayName0) AS Total
FROM         dbo.v_GS_ADD_REMOVE_PROGRAMS
GROUP BY DisplayName0
HAVING      (DisplayName0 LIKE 'Windows Internet Explorer%')
***************************************************************
SCCM report for Distribution point last refresh time and last status time:
The following SCCM report provides SCCM package ID, Source site Last refresh time, Active status and last status time;
****************************************************************
SELECT  v_DistributionPoint.PackageID AS [Package ID],
        v_DistributionPoint.SourceSite AS [Source Site],    
        v_DistributionPoint.LastRefreshTime AS [Last Refresh Time],
        v_DistributionPoint.IsActive AS Active,    
        v_DistributionStatus.LastStatusTime AS [Last Status time]
FROM    v_DistributionPoint
CROSS JOIN v_DistributionStatus


****************************************************************
Below is a simple SQL query which provides Program Name, Package Name, Collection Name, Advertisement Name and Advertisement expiration Time. **********************************************************************
SELECT AdvertisementName AS [Advertisement Name], ProgramName AS [Program Name], PackageName AS [PKG Name], CollectionName AS [Collection Name], ExpirationTime AS [Adv Exp Time] 
FROM v_AdvertisementInfo **********************************************************************
 SCCMReport for Machine name and SMS GUID:

SCCM Report for Machine name and SMS GUID
 *********************************************************************
SELECT     Name0 AS [Machine Name], SMSID0 AS [SMS GUID]
FROM   v_GS_SYSTEM
*********************************************************************  
SCCM Report for PC count  based on manufacturer:

The following SCCM report will provide PC count by grouping the machine manufacturer name then adds a friendly name and target to a particular collection.

Example:
Group all the Virtual machines as VM’s, all the Toshiba machines as Laptops and Hewitt Packard machines as Laptops from All systems collection.

********************************************************************************
SELECT     CASE WHEN Manufacturer0 LIKE 'VMware, Inc.' THEN 'VMs' WHEN Manufacturer0 LIKE 'Toshiba' THEN 'Laptops' WHEN Manufacturer0 LIKE 'Hewlett-Packard' THEN
                       'Desktops' ELSE 'Null' END AS [Device Type], v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Manufacturer, COUNT(v_GS_COMPUTER_SYSTEM.Manufacturer0)
                      AS Count
FROM         v_FullCollectionMembership AS FCM INNER JOIN
                      v_GS_COMPUTER_SYSTEM ON FCM.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
WHERE     (FCM.CollectionID = 'SMS00001')
GROUP BY v_GS_COMPUTER_SYSTEM.Manufacturer0

**********************************************************************************
You need to change the manufacturer details and collection ID where you want run against.
If you want to run for all systems then you don’t need to modify the collection ID.
 This reports provides Machine name, Machine manufacturer, Model, Serial number, AD Site, Last hardware scan, Discovery time, user name and user full name.
********************************************************************
SELECT     sys.Netbios_Name0 AS AssetID, v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Make, v_GS_COMPUTER_SYSTEM.Model0 AS Model,
v_GS_PC_BIOS.SerialNumber0 AS [Serial Number], sys.AD_Site_Name0 AS [AD Site Location], vWorkstationStatus.LastHardwareScan AS [Last Hardware Scan], MAX(v_AgentDiscoveries.AgentTime) AS Discovery_time, sys.User_Name0 AS [User Name], usr.Full_User_Name0 AS [Full User Name]
FROM v_AgentDiscoveries
INNER JOIN v_R_User AS usr ON v_AgentDiscoveries.ResourceId = usr.ResourceID RIGHT OUTER JOIN v_GS_PC_BIOS INNER JOIN       v_R_System_Valid AS sys ON v_GS_PC_BIOS.ResourceID = sys.ResourceID INNER JOIN v_GS_COMPUTER_SYSTEM ON sys.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN                 vWorkstationStatus ON v_GS_COMPUTER_SYSTEM.ResourceID = vWorkstationStatus.ResourceID ON usr.User_Name0 = sys.User_Name0
GROUP BY sys.Netbios_Name0, sys.User_Name0, sys.AD_Site_Name0, v_GS_PC_BIOS.SerialNumber0, v_GS_COMPUTER_SYSTEM.Manufacturer0,
v_GS_COMPUTER_SYSTEM.Model0, vWorkstationStatus.LastHardwareScan, usr.Full_User_Name0
**********************************************************************

This report will provide machine name and the installed SCCM client version

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


SELECT     Netbios_Name0 AS [Machine Name], Client_Version0 AS [Client Version]


FROM         v_R_System

  ******************************************************************
This report provides the Machine Name and when OS deployed to that machine.
************************************************************************
SELECT v_R_System.Name0 AS [Machine Name], v_GS_OPERATING_SYSTEM.InstallDate0 AS [OS Deployed On]
FROM   v_GS_OPERATING_SYSTEM
INNER JOIN
v_R_System ON v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceID
************************************************************************
SCCM report for a specific application machine name make model:
Below SQL query will provide a installed specific application, Machine Name, Machine Manufacturer and Machine model.

Change the Application name to a desired app name.
**************************************************************************
SELECT     arp.DisplayName0 AS [Application Name], arp.Version0 AS [App Version], v_GS_COMPUTER_SYSTEM.Name0 AS [Machine Name],
                      v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [Manufacturer Name], v_GS_COMPUTER_SYSTEM.Model0 AS [Machine Model]
FROM         v_Add_Remove_Programs AS arp INNER JOIN
                      v_GS_COMPUTER_SYSTEM ON arp.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
WHERE     (arp.DisplayName0 LIKE '%Firefox%')
**************************************************************************
SCCM report for App-v client:


The following SCCM report will provide Machine Name, User ID, User domain, App –V client name and Client version excluding the language pack.
*************************************************************************
SELECT     sys.Netbios_Name0 AS [Machine Name], sys.User_Name0 AS [User ID], sys.User_Domain0 AS [User Domain], arp.DisplayName0 AS [App Name], arp.Version0 AS [App Version]
FROM v_R_System AS sys INNER JOIN v_Add_Remove_Programs AS arp ON sys.ResourceID = arp.ResourceID
WHERE    
(arp.DisplayName0 = 'Microsoft Application Virtualization Desktop Client') OR
(arp.DisplayName0 = 'Microsoft App-V 5.0 Client UI') OR
(arp.DisplayName0 = 'Microsoft Application Virtualization (App-V) Client 5.0 Service Pack 2') AND
(arp.DisplayName0 NOT LIKE '%Microsoft Application Virtualization Client en-US Language Pack x64%')
****************************************************************************

28 comments:

  1. Excellent write up. Very useful for day-to-day management reporting purpose.
    Thank you for putting all together.
    Cheers

    ReplyDelete
  2. Exactly what I needed. I have tried 2 of these reports and they work without a glitch.
    Keep up the good work. Keep adding more

    ReplyDelete
  3. Very useful reports. Thanks for sharing with us

    ReplyDelete
  4. Thanks very much. Very helpful!

    ReplyDelete
  5. Hi Looking to Get a customized report for the below scenario's.
    Please help us.
    Need to get any hardware\Software changes for overall systems which is in a single domain.
    Example: Ram Size increased or Harddisk size increased etc..

    Please mail to sathya4long@gmail.com

    ReplyDelete
  6. Hey Venu,
    Is it possible to possible to write a query that includes device name, model, serial number, and IP address all in the same report? It's the IP address integration with the rest of those items that I'm having a hard time with.

    ReplyDelete
    Replies
    1. It is possible to create all the fields you have mentioned above. However adding IP address to a report not a good idea. You will get lot of duplicate records (I,e, LAN, Wifi, Bluetooth) and the report wont be accurate.
      If you need help with the report, let me know.

      Delete
  7. Hi, Any tips for generating a report to show a specific application installed on all PC's as well as the make/model of the machine?

    ReplyDelete
    Replies
    1. Check this report;
      http://venusingireddy.blogspot.com.au/2014/08/sccm-report-for-installed-application.html

      Delete
  8. Need help in Creating a report for a specific software installed on machines with Serial No, user name and machine no.. please help

    ReplyDelete
  9. Any help in creating a report to show all machines that DO NOT have Microsoft .NET 4.0 or higher? Need it to just list the machine names.

    ReplyDelete
  10. Is it possible to write a query that includes device collection names, site names, computer name , and os type????? plshelp me

    ReplyDelete
  11. It's excellent thought to help some one.. your post is so much helping for day to day tasks..
    if possible can you please help me where should i configure the license details to get the complete license based report .. also can you help me to get the report like below

    Vendor

    Product

    Version

    Licensed Y/N

    Count

    ReplyDelete
  12. anyway to get the list of the include and exclude collection and folder location of collection ?

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Venu, Could you please help us to customize the report as below

      ProductName
      Vendor
      MachineName
      Licensed Y

      Delete
  14. Hi Venu,

    Nice post!!!

    Can you help me with this report. Select an application from drop down and it should be looking at active deployments and users in those deployments and primary machines assigned to those users. Result should be the list of machines with primary user.

    Thanks much!!!
    Lucky

    ReplyDelete
  15. I've been searching to see if this is possible as the variations I've been trying have not produced what I'm looking for. Do you know if it is possible to create a report that has all services (or programs in add/remove programs) for all servers in a collection. Not an aggregate but all the services for each server in a collections tied to that server (and later add/remove programs). Management want to know all the services we have for all our servers but to know what service is on which server. While I could do each separately, it gets ugly with 200+ servers. It would be a very long report but I can't seem to generate it as I either end up with an aggregate of all service or one server's services. Do you know if this is possible? If you think it is could you point me down a more productive rabbit hole then I've managed by myself so far? Or if it is not possible, let me know...though I really feel it should be and I'm just missing something basic. Thank you. --Cori

    ReplyDelete
  16. Is it possible to report on all application deployment requirements? For example, our sccm environment contains for Windows 7 clients and citrix 2012 servers, and we need to report on all applications that do not have a OS requirement which only allows them to install to Windows 7 devices

    ReplyDelete
  17. Hi.. Very useful reports.. Is it possible to get a report showing the compliance (installation of Application). The format I am looking for Computer Name, Application Name, Installation Status (Installed or Failed), Installation Date, Error codes.

    ReplyDelete
  18. Hello Venu,

    Can you help me with the SQL query to find the Status of SUG targeted to particular collection and the status of the deployment : Complaint , failed , Enforcement state unknown if that is failed with the error code

    ReplyDelete
  19. Hello Venu,

    I need a Report on Computers with Software A and Software B.
    Thank you.
    Best regards,
    Orhan

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
  21. Hi , Need query for specific package on ALL distribution points?
    Perhaps the specific package appear as prompt.

    ReplyDelete
  22. Hi you dont happen to have a Visio report do you ? Similar to the Project?

    Thank you and your work is a great help!

    ReplyDelete
    Replies
    1. Hi there,
      to get Visio report, replace the Project to Visio.
      That will give you the installed Visio versions.

      Cheers,
      Venu

      Delete
  23. Hello venu,
    can you please help me pulling the following report,
    Computer Manufacturer
    Computer Model #
    Computer Name
    Processor -
    CPU Manufacture
    CPU Model
    CPU Norm Speed
    Cores
    RAM -
    How much memory
    Memory Banks
    Memory Speed
    Video Graphics -
    Manufacture
    Model
    Amount of RAM

    ReplyDelete
  24. Hi I need help get query for find the computer name, OS version, user name,what windows patch installed and Compliance or not.

    ReplyDelete