Read Time:26 Minute, 4 Second
Posting ini menyediakan berbagai kueri SQL untuk menghasilkan laporan SCCM khusus (07/12) untuk tujuan pelaporan.
Halaman ini akan diperbarui secara berkala.
Tolong dicatat; Saat Anda menyalin kueri SQL dari browser web, Anda perlu memastikan bahwa browser tidak menambahkan karakter tambahan apa pun.
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 SCCM site roles and install directory on each server
The following query will provide details of SCCM site roles and install directory on each server;
******************************************************************
SELECT v_Site.ServerName, v_SiteSystemSummarizer.Role, v_Site.InstallDir, v_Site.SiteCode
FROM v_Site
INNER JOIN v_SiteSystemSummarizer ON v_Site.SiteCode = v_SiteSystemSummarizer.SiteCode
******************************************************************
SCCM Report to findout packages selected Copy the content in this package to a package share on distribution Points:
The following query will provide details of SCCM package Copy the content in this package to a package share on distribution Points is selected on package share settings;
******************************************************************
SELECT ContentID, ServerName, ServerPath, SiteCode, Version, URL, UpdateTime
FROM ContentDPMap
WHERE(URL LIKE '\\%')
******************************************************************
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 [Google earth 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%.
SCCM report query to get installed application:
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
*********************************************************************************
SCCM Report for Package distribution status on Distribution Points:
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>%'
SCCM Report for Machine type(laptop or desktop), Serial no, Manufacturer, Model, OS, SP level, last loggedon user, total memory and total HDD:
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
**************************************************
SCCM Report for computer details for a specific collection:
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
***************************************************************
SCCM report for Internet explorer browser version count:
***************************************************************
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
****************************************************************
SCCM report to get advertisement details for a program,package and collection:
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.
SCCM report for Machine name, user full name:
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
**********************************************************************
SCCM report for SCCM Client versions:
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
******************************************************************
SCCM report for Machine name and OS deployed date :
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%')
****************************************************************************
Happy
0 %
Sad
0 %
Excited
0 %
Sleepy
0 %
Angry
0 %
Surprise
0 %
0
0
Average Rating