SCCM + 170+ Common SQL Queries

1. A Specific System Is Part of what are all Collections

SELECT
v_R_System.Name0,v_R_System.Client0,v_Collection.Name as 'Collection Name',v_Collection.CollectionID FROM v_FullCollectionMembership
INNER JOIN v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID INNER JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID WHERE (v_R_System.Name0 = 'Client01') and v_Collection.CollectionID not like 'SMS%'

2. All Users Names Part of Domain Admin Group

SELECT
dbo.v_RA_User_UserGroupName.User_Group_Name0, dbo.v_R_User.User_Name0 FROM dbo.v_RA_User_UserGroupName INNER JOIN
dbo.v_R_User ON dbo.v_RA_User_UserGroupName.ResourceID = dbo.v_R_User.ResourceID WHERE dbo.v_RA_User_UserGroupName.User_Group_Name0 LIKE '%\Domain Admins' ORDER BY dbo.v_RA_User_UserGroupName.User_Group_Name0

3. Advertisement status for specific Collection ID with Computer Details

Select sys.Netbios_Name0, sys.User_Domain0, sys.AD_Site_Name0, sys.User_Name0, LastAcceptanceStateName, LastStateName,LastStatusMessageIDName, LastExecutionResult, MAX(LastStatusTime) as 'LastStatusTime', AdvertisementID,coll.CollectionID from v_ClientAdvertisementStatus stat
join v_R_System sys on stat.ResourceID=sys.ResourceID
left join v_RA_System_SMSInstalledSites site on stat.ResourceID=site.ResourceID join v_FullCollectionMembership coll on sys.ResourceID = coll.ResourceID where stat.AdvertisementID = 'AdvertID' and coll.CollectionID = 'Collection ID'
GROUP BY sys.Netbios_Name0,sys.User_Domain0,sys.AD_Site_Name0, sys.User_Name0, LastAcceptanceStateName,LastStateName, LastStatusMessageIDName,LastExecutionResult, LastStatusTime, AdvertisementID,coll.CollectionID

4. All SQL Server Installed Version and Computer Details

Select distinct VRS.name0,ADRM.DisplayName0,ADRM.Version0,ADRM.Publisher0 from v_GS_ADD_REMOVE_PROGRAMS ADRM join v_r_system VRS on VRS.ResourceID = ADRM.ResourceID
where ADRM.Publisher0 like '%Microsoft%' and (ADRM.DisplayName0 like '%Microsoft SQL Server%'
or ADRM.DisplayName0 like '%SQL Server%') order by VRS.Name0

5. All Computers with Last Heartbeat Discovery Time Stamp

SELECT v_R_System.Netbios_Name0 AS Name, v_R_System.Client0,Min(A.AgentTime) as 'Time Stamp', v_R_System.Operating_System_Name_and0 AS [AD Operating System] FROM v_R_System INNER JOIN v_FullCollectionMembership ON v_FullCollectionMembership.ResourceID =v_R_System.ResourceID inner join v_AgentDiscoveries A ON A.ResourceId=dbo.v_R_System.ResourceID WHERE A.AgentName like 'Heartbeat Discovery' group by Netbios_Name0,Client0,AgentTime, Operating_System_Name_and0 order by Netbios_Name0 desc

6. All IE Version using Software Inventory

SELECT

b.Netbios_Name0, b.User_Name0, b.AD_Site_Name0,
CASE
WHEN a.FileVersion LIKE '5.%' THEN 'Internet Explorer 5'
WHEN a.FileVersion LIKE '6.%' THEN 'Internet Explorer 6'
WHEN a.FileVersion LIKE '7.%' THEN 'Internet Explorer 7'
WHEN a.FileVersion LIKE '8.%' THEN 'Internet Explorer 8'
WHEN a.FileVersion LIKE '9.%' THEN 'Internet Explorer 9'
WHEN a.FileVersion LIKE '10.%' THEN 'Internet Explorer 10'
WHEN a.FileVersion LIKE '11.%' THEN 'Internet Explorer 11'
ELSE 'Other Version' END AS 'IE Version', a.FileName, a.FileVersion, a.FilePath 
FROM v_GS_SoftwareFile a 
JOIN v_R_System b ON a.ResourceID = b.ResourceID 
JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID WHERE FileName = 'iexplore.exe'
and FilePath like '_:\Program%Internet Explorer%' 
GROUP BY b.Netbios_Name0, b.User_Name0, b.AD_Site_Name0,  a.FileName, a.FileVersion, a.FilePath 
ORDER BY b.Netbios_Name0

7. All Outlook Version using Software Inventory

SELECT
b.Netbios_Name0, b.User_Name0, 
CASE 
WHEN a.FileVersion LIKE '12.%' THEN 'Office 2007' 
ELSE 'Other Version' END AS 'Office Version', a.FileName, a.FileVersion, a.FilePath 
FROM v_GS_SoftwareFile a
JOIN v_R_System b ON a.ResourceID = b.ResourceID
JOIN v_RA_System_SystemOUName c ON a.ResourceID = c.ResourceID WHERE FileName = 'outlook.exe'
and FilePath = '_\Pr%' 
GROUP BY b.Netbios_Name0, b.User_Name0, a.FileName, a.FileVersion, a.FilePath 
ORDER BY  b.Netbios_Name0

8. All Packages Total Targeted DP Counts

SELECT PackageID, Name, Version, Manufacturer, Language, Description,PkgSourcePath , LastRefreshTime,
(SELECT COUNT(PkgID) FROM vPkgStatusSummaryDistPts WHERE PkgID = v_Package.PackageID) AS [Total Number of DP’s] FROM v_Package order by [Total Number of DP’s]  desc

9. All Packages Compare targeted Packages on Two DPS

select s.SiteCode,s.PackageID,p.Name,(p.sourcesize)/1024 as 'Size(MB)', s.sourceversion as 'DPVersion',p.storedpkgversion as 'LastVersion',s.Installstatus as 'Package Status', Case v_Package.PackageType
When 0 Then 'Package' When 3 Then 'Driver' When 4 Then 'Task Sequence' When 5 Then 'software Update' When 7 Then 'Virtual'  When 8 Then 'Application' When 257 Then 'Image' When 258 Then 'Boot Image'  When 259 Then 'OS' 
Else ' ' END AS 'Type' from v_PackageStatusDistPointsSumm s 
inner join smspackages p on s.packageid = p.pkgid 
inner join v_Package on v_Package.PackageID=p.[PkgID]
where s.PackageID not in 
(select PackageID from v_DistributionPoint where ServerNALPath like '%DPServerName1%') and ServerNALPath like '%DPServerName1%' order by 8

10. Compare Packages with two DP’S

select 
Pkg.PackageID, Pkg.Name, Case Pkg.PackageType 
When 0 Then 'Package' 
When 3 Then 'Driver'
When 4 Then 'TaskSequence'
When 5 Then 'softwareUpdate'
When 7 Then 'Virtual'
When 8 Then 'Application'
When 257 Then 'Image'
When 258 Then 'BootImage'
When 259 Then 'OS'
Else ' ' 
END AS 'Type from v_Package Pkg
where Pkg.PackageID in

( select PackageID from v_DistributionPoint where ServerNALPath like '%Master DP Name%' and PackageID not in 

(select PackageID from v_DistributionPoint where ServerNALPath like '%Compare DP Name%')) order by 3

11. All Software Distribution Packages without Advertisements

select  v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate from dbo.v_package Where packageID not in (select PackageID from dbo.v_Advertisement) and PackageID not in (SELECT ReferencePackageID FROM v_TaskSequenceReferencesInfo) and v_Package.name not like '%osd%' and V_package.PackageType ='0'  group by v_Package.PackageID, v_Package.Name,v_Package.SourceVersion,v_Package.SourceDate order by v_Package.PackageID

12. All Client Settings Status

SELECT *  FROM v_CH_Settings where SettingsID = 1

Hold on tight, we're working on it......