SCCM

The Ultimate SCCM SQL Script for Comprehensive Information Retrieval

Everything you need from a System Center SQL Script

System Center Configuration Manager (SCCM) is a powerful tool used by IT professionals to manage and deploy software, monitor system health, and gather information about users, computers, and more. One of the key components of SCCM is its SQL database, which stores a wealth of data that can be queried and analyzed to gain valuable insights. If you're an SCCM Admin, then you probably spend a lot of time in SQL Management Studio. I know I do.

If you're looking for a single SQL script that can capture all the information you need about users, computers, software, and more, look no further. This comprehensive SCCM SQL script will provide you with a holistic view of your environment and empower you to make informed decisions.

With this script, you can easily retrieve user information such as usernames, email addresses, department, and more. I am constantly asked for different information relating to users and computers. here are some examples:

  • I have a list of email addresses, can you tell me what computers they are using?

  • Can you let me know all the computers that have this software?

  • Can I get a list of all the versions of this software and who is using it?

  • We need a list of all computers at so-n-so site.

  • can I get a list of users from this physical office?

  • What version of Windows are we running and can they be separated by department?

  • What models of hardware are we using?

  • Can you create a list of computers for all the users in this group?

  • I need a list of software installed in this folder

  • can you list all the systems that haven't logged in within the last 30 days?

  • and so on....

Anywho, here is the script.



Declare @MyOUDomain as VarChar(50)
Set @MyOUDomain = ',DC=Domain,DC=Com'  -- Change to your domain name used in separation out OU data

select distinct
VRS.ResourceID


--APP stuff
--apps.DisplayName [SCCM_AppName]
--count(SF.FileName) [Count],
--,SF.FileName,sf.filepath,sf.FileSize,sf.Fileversion
--mail.Largest_OST_File
--ie.ExecutableName0
--,ie.FileVersion0--
,vrs.Netbios_Name0
--,s.ARPDisplayName0, s.productversion0, s.InstalledLocation0,s.packagecode0,s.softwarecode0,InstallSource0
--,REPLACE(SF.FileName,',',' ') [FileName],REPLACE(sf.filepath,',',' ') [PAth],sf.FileSize,REPLACE(sf.Fileversion,',',' ') [Version]
--qfe.HotFixID0
--, qfe.InstallDate0
--User stuff
,REPLACE(REPLACE(vru.displayname0,' ,','|'),',','|') [User_Name]
, vru.mail0
, vru.employeeID
--, vru.EmployeeType0
, REPLACE(SUBSTRING(vru.manager,4,CHARINDEX(',OU',vru.manager,3)-4),'\,','|') [Manager]
--, vru.telephoneNumber0
--, vru.ipphone 
, Replace(vru.physicalDeliveryOfficeNam,',','.') as User_Physical_Office
, REPLACE(replace(vru.Full_User_Name0,'	,','|'),',','|') [Full Name]
,vru.User_name0
, CASE
   WHEN U.TopConsoleUser0 = '-1' OR U.TopConsoleUser0  IS NULL THEN 'N/A'
   ELSE U.TopConsoleUser0
END as TopUser
--, vru.businessCategory0 [Category]
, REPLACE(replace(vru.department0,'	,','|'),',','|') [Department]
, vru.title0

,parsename(Replace(Replace(Replace(Replace(Replace(substring(VRS.Distinguished_Name0,charindex(',OU',VRS.Distinguished_Name0)+1,len(VRS.Distinguished_Name0)-charindex(',OU',VRS.Distinguished_Name0)),',OU=Locations',''),@MyOUDomain,''),',OU','.OU'),'OU=Machines.',''),'OU=',''),4) as [ou 4]
,parsename(Replace(Replace(Replace(Replace(Replace(substring(VRS.Distinguished_Name0,charindex(',OU',VRS.Distinguished_Name0)+1,len(VRS.Distinguished_Name0)-charindex(',OU',VRS.Distinguished_Name0)),',OU=Locations',''),@MyOUDomain,''),',OU','.OU'),'OU=Machines.',''),'OU=',''),3) as [ou 3]
,parsename(Replace(Replace(Replace(Replace(Replace(substring(VRS.Distinguished_Name0,charindex(',OU',VRS.Distinguished_Name0)+1,len(VRS.Distinguished_Name0)-charindex(',OU',VRS.Distinguished_Name0)),',OU=Locations',''),@MyOUDomain,''),',OU','.OU'),'OU=Machines.',''),'OU=',''),2) as [ou 2]
,parsename(Replace(Replace(Replace(Replace(Replace(substring(VRS.Distinguished_Name0,charindex(',OU',VRS.Distinguished_Name0)+1,len(VRS.Distinguished_Name0)-charindex(',OU',VRS.Distinguished_Name0)),',OU=Locations',''),@MyOUDomain,''),',OU','.OU'),'OU=Machines.',''),'OU=',''),1) as [ou 1]

--computer Stuff
,bios.SerialNumber0
, year(CONVERT(DATE,BIOS.ReleaseDate0)) AS S_BIOSDate
, vrs.Netbios_Name0
--, nlp.LastLogon0
--, nlp.TimeStamp
--, nlp.Name0 as ['last user logged on']
--,vrs.User_Name0
, os.Caption0
, os.BuildNumber0
,'OS Build Number' = CASE
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '10240') THEN '1507'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '10586') THEN '1511'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '14393') THEN '1607'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '15063') THEN '1703'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '16299') THEN '1709'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '17134') THEN '1803'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '17763') THEN '1809'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '18362') THEN '1903'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '18363') THEN '1909'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '18912') THEN '1909'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '19042') THEN '20H2'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Education') and (OS.BuildNumber0 = '19042') THEN '20H2'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '19043') THEN '21H1'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '19044') THEN '21H2'
		WHEN (OS.Caption0 = 'Microsoft Windows 10 Enterprise' or OS.Caption0 = 'Microsoft Windows 10 Pro') and (OS.BuildNumber0 = '19045') THEN '22H2'
		WHEN (OS.Caption0 = '?????????? Windows 10 ?????????????' ) and (OS.BuildNumber0 = '18362') THEN '1903'
		WHEN (OS.Caption0 = 'Microsoft Windows 7 Enterprise' or OS.Caption0 = 'Microsoft Windows 7 Professional') and (OS.BuildNumber0 = '7601') THEN 'With SP1'
		WHEN (OS.Caption0 = 'Microsoft Windows 7 Enterprise' or OS.Caption0 = 'Microsoft Windows 7 Professional') and (OS.BuildNumber0 = '7600') THEN 'Without SP1'
		ELSE OS.BuildNumber0
		END
		
		,CASE 
WHEN OS.Locale0 = '041e' THEN 'Thai/Thailand/th-TH'
WHEN OS.Locale0 = '2c0a' THEN 'Spanish/Argentina/es-AR'
WHEN OS.Locale0 = '0409' THEN 'English/United States/en-US'
WHEN OS.Locale0 = '1009' THEN 'English/Canada/en-CA'
WHEN OS.Locale0 = '4409' THEN 'English/Malaysia/en-MY'
WHEN OS.Locale0 = '0c09' THEN 'English/Australia/en-AU'
WHEN OS.Locale0 = '3809' THEN 'English/Indonesia/en-ID'
WHEN OS.Locale0 = '0416' THEN 'Portuguese/Brazil/pt-BR'
WHEN OS.Locale0 = '0419' THEN 'Russian/Russia/ru-RU'
WHEN OS.Locale0 = '0816' THEN 'Portuguese/Portugal/pt-PT'
WHEN OS.Locale0 = '280a' THEN 'Spanish/Peru/es-PE'
WHEN OS.Locale0 = '0804' THEN 'Chinese (Simplified)/Peoples Republic of China/zh-CN'
WHEN OS.Locale0 = '240a' THEN 'Spanish/Colombia/es-CO'
WHEN OS.Locale0 = '0c0a' THEN 'Spanish/Spain/es-ES'
WHEN OS.Locale0 = '080A' THEN 'Spanish/Mexico/es-MX'
WHEN OS.Locale0 = '400a' THEN 'Spanish/Bolivia/es-BO'
ELSE OS.Locale0
END AS [locale]

,OS.Locale0 [LCID HEX]
,OS.OSLanguage0 [Language]
, vrs.AD_Site_Name0
,'Chassis'=CASE 
 WHEN enc.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
 WHEN enc.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
 END

,CASE
    When cs.Manufacturer0 = 'LENOVO' Then ltrim(rtrim(CSP.Version0)) Else ltrim(rtrim(cS.Model0))
End As [CorrectedModel]
, vrs.Last_Logon_Timestamp0
,cs.Manufacturer0
,enc.SMBIOSAssetTag0
--, vid.CurrentHorizontalResolution0
--, vid.CurrentVerticalResolution0
--, cast(vid.CurrentHorizontalResolution0 as varchar) + 'x' + cast(vid.CurrentVerticalResolution0 as varchar) as ResolutionFinal
--,ipaddress =  STUFF((SELECT ', ' + b.IPAddress0 FROM v_GS_NETWORK_ADAPTER_CONFIGUR b WHERE b.ResourceID = vrs.ResourceID FOR XML PATH('')), 1, 2, '')
from v_r_system vrs
--from dbo.v_R_System_Valid RV
left Join v_GS_INSTALLED_SOFTWARE S						on S.ResourceID = VRS.ResourceID
left join v_r_user vru									on vrs.User_name0 = vru.User_Name0
LEFT outer join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP U	ON vrs.ResourceID = U.ResourceID
left join v_Gs_SoftwareFile Sf							on vrs.resourceid = sf.resourceid
--left join V_GS_INSTALLED_EXECUTABLE IE					on vrs.resourceid = IE.resourceid
left join v_GS_COMPUTER_SYSTEM cs						on vrs.resourceid = cs.resourceid
left join v_GS_OPERATING_SYSTEM os						on vrs.resourceid = os.resourceid 
--Left Join (Select ResourceID, User_Group_Name0 From v_RA_User_UserGroupName Where User_Group_Name0 = 'Domain\GSG.USA.BT.AllUsers') GN on GN.ResourceID = vrU.ResourceID
--inner Join v_GS_QUICK_FIX_ENGINEERING QFE 				on QFE.ResourceID = VRS.ResourceID 
left join V_GS_NETWORK_LOGIN_PROFILE nlp				on nlp.ResourceID = vrs.ResourceID
Left Join v_GS_COMPUTER_SYSTEM_PRODUCT CSP				on CSP.ResourceID = vrs.ResourceID
left join v_GS_SYSTEM_ENCLOSURE enc						on vrs.ResourceID = enc.ResourceID
left join v_Add_Remove_Programs arp						on vrs.ResourceID = arp.ResourceID
--join v_GS_VIDEO_CONTROLLER as vid						on vrs.resourceid = vid.ResourceID
left join v_GS_PC_BIOS BIOS on vrs.ResourceID=bios.ResourceID
--Full join fn_ListDeploymentTypeCIs(1033) DT on ISNULL(dt.SDMPackageDigest.value('(/AppMgmtDigest/DeploymentType/Installer/CustomData/SourceUpdateProductCode)[1]','nvarchar(MAX)'), 'None') = s.SoftwareCode0
--Full join fn_ListApplicationCIs(1033) Apps on DT.AppModelName = Apps.ModelName
--left join v_RA_SYSTEM_SystemOUName ou on (ou.ResourceID = vRS.ResourceID)  
--left outer join dbo.v_RA_System_SystemContainerName CN on vrs.Resourceid = cn.resourceID


Where 1=1   
--and csp.Version0 like '%7010%' or cS.Model0 like '%7010%'
--and cS.Model0 like '%5440%'
--and vid.CurrentVerticalResolution0 is not null
--and nlp.Name0 like 'X%'
--and vru.mail0  like '%bah%'
--and os.Caption0 not like '%server%' 
--and vrs.name0 like '%dp0%'
--and vrs.name0 in ('computername1','computername2')
--and VRS.Distinguished_Name0 like '%bang%'
--and vru.user_name0 in ('userid1','userid2')
--and User_Group_Name0 like '%UDL.IT.allstaff%'
--and s.ARPDisplayName0 like '%autocad%' or s.ARPDisplayName0 like '%aspen%'
--and s.InstalledLocation0 like '%protreat%'
--and arp.ProdID0 = '{6A4B782B-A676-481F-92B9-DF10D4B517BB}'
--and vru.displayname0 in ('Lname, Fname','Lname, Fname')
--and vru.mail0 in ('fname.lname@domain.com','fname.lname2@domain.com')
--and sf.FileName like '%.exe'
--and SF.FileName like '%.pst%'
/*
and arp.DisplayName0 like '%microsoft office%'
and arp.DisplayName0 not like '%update%'
and arp.DisplayName0 not like '%kb%'
and arp.DisplayName0 not like '%tool%'
and arp.DisplayName0 not like '%mui%'
and arp.DisplayName0 not like '%server%'
and arp.DisplayName0 not like '%web%'
and arp.DisplayName0 not like '%proof%'
and arp.DisplayName0 not like '%comp%'
and arp.DisplayName0 not like '%servi%'
*/


--order by vrs.Netbios_Name0

Customizing the Script

This SCCM SQL script is highly customizable, allowing you to tailor it to your specific needs. You can modify the queries to include additional fields or filter the results based on specific criteria. By leveraging the power of SQL, you can extract the exact information you require and present it in a meaningful way.

With this ultimate SCCM SQL script, you can unlock the full potential of SCCM and harness the power of data to optimize your IT operations. By retrieving comprehensive information about users, computers, software, and system health, you can make informed decisions, streamline processes, and ensure the efficiency and security of your organization.

Customizing the script is either as simple as commenting out (--) or uncommenting () certain sections. Or changing the query values to match your need.

You will need to have the proper discovery methods enabled within your environment and the attribute you want to query.

Obtaining Computer Details

Need to know the hardware specifications of your computers or their last logged-on user? This SCCM SQL script has got you covered. It enables you to retrieve information about computer models, operating systems, disk space, and much more.

Tracking Software Inventory

Keeping track of software installations and licenses can be a daunting task, especially in large organizations. This SCCM SQL script simplifies the process by providing you with a comprehensive software inventory report. You can easily identify installed software and who is using it.

Modifying the script to get application data.

There are two lines for retrieving specific application data form the computers.

--,s.ARPDisplayName0, s.productversion0, s.InstalledLocation0,s.packagecode0,s.softwarecode0,InstallSource0
--,REPLACE(SF.FileName,',',' ') [FileName],REPLACE(sf.filepath,',',' ') [PAth],sf.FileSize,REPLACE(sf.Fileversion,',',' ') [Version]

  • One is for the installation data details. Think about Add/Remove Programs or Programs and Features.

    1. v_GS_INSTALLED_SOFTWARE = Via Hardware inventory = Lists information about the installed software applications on Configuration Manager clients found through Asset Intelligence.

    2. you must have defined the items that you want collected for this to pull data. here is a diagram below on where to add hardware classes that you want inventoried.

  • The second is for the specific files. These are winword.exe, acrord32.exe, etc.

    1. v_GS_SoftwareFile = Via software inventory =Lists the files and associated product IDs on each Configuration Manager client.

    2. you must have defined the items that you want collected for this to pull data. here is a diagram below on where to add files that you want inventoried.

      1. Typically you just see *.exe, but I worked in a company that had me add audio and video file types, pdf's, pst's, etc. if you want to know who has what, then this is how you do it.

If you do not need a full inventory of all software, then you would also uncomment a line under the where clause. Typically it would be one of these lines to determine who has a certain software title installed, or who has the software installed in a specific folder, or even who has this file somewhere on their machine?

--and s.ARPDisplayName0 like '%autocad%' or s.ARPDisplayName0 like '%aspen%'
--and s.InstalledLocation0 like '%protreat%'
--and sf.FileName like 'vlc.exe'

Alternatively you could uncomment one of the following to limit your searched to a user, computer, computer model, or specific operating system.

--and csp.Version0 like '%7010%' or cS.Model0 like '%7010%'
--and vru.mail0  like '%bah%'
--and os.Caption0 not like '%server%' 
--and vrs.name0 like '%dp0%'

Download the Script

Scripts SCCM
✉

Get new articles in your inbox

SCCM guides, AI tools, automation tips — sent when published. No spam.

Keep reading

Comments