SCCM Inventory report with user mail ID and Physical delivery office

Add "mail" and "physicalDeliveryOfficeName" active directory attributes in SCCM Active directory user discovery properties and run the active directory user discovery cycle.


Query:


SELECT
A.Name0,
vrs1.full_user_name0,
vrs1.Mail0,
vrs1.physicalDeliveryOfficeNam0,
MAX (B.SerialNumber0) AS 'Serialnumber',
A.Manufacturer0,
A.Model0, C.Name0 AS 'Processor',
D.TotalPhysicalMemory0 AS 'Memory (KBytes)',
MAX ( E.Size0 ) AS 'Size (MBytes)',
MAX (F.MACAddress0) AS 'MAC Adress',
MAX (F.IPAddress0) AS 'IP Adress',
G.AD_Site_Name0 AS 'AD Site',
MAX (A.UserName0) AS 'Last user logged in',
H.Caption0 AS 'Operating System',
H.CSDVersion0 AS 'Service Pack',
G.Creation_Date0 AS 'Creationdate in SMS',
I.LastHWScan
FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGURATION F,
v_R_System G,
v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I,
v_R_system vrs,
v_R_user vrs1
WHERE
vrs.ResourceID = A.ResourceID and
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID and
vrs.user_name0=vrs1.full_user_name0

GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan,vrs1.Full_User_Name0,vrs1.Mail0,vrs1.physicalDeliveryOfficeNam0

SCCM Inventory report

Query:


SELECT  distinct
 CS.name0 as 'Computer Name',
 CS.domain0 as 'Domain',
 CS.UserName0 as 'User',
 BIOS.SerialNumber0 as 'Bios serial',
 SE.SerialNumber0 as 'System Enclosure serial',
 CS.Manufacturer0 as 'Manufacturer',
 CS.Model0 as 'model',
 OS.Caption0 as 'OS',
 RAA.SMS_Assigned_Sites0 as 'Site',
 RAM.TotalPhysicalMemory0 as 'Total Memory',
 sum(isnull(LDisk.Size0,'0')) as 'Hardrive Size',
 sum(isnull(LDisk.FreeSpace0,'0')) AS 'Free Space',
 CPU.CurrentClockSpeed0 as 'CPU Speed'
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_RA_System_SMSAssignedSites RAA on RAA.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
 right join v_GS_Processor CPU on CPU.ResourceID = CS.ResourceID  
 right join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
where
 LDisk.DriveType0 =3
group by
 CS.Name0,
 CS.domain0,
 CS.Username0,
 BIOS.SerialNumber0,
 SE.SerialNumber0,
 CS.Manufacturer0,
 CS.Model0,
 OS.Caption0,
 RAA.SMS_Assigned_Sites0,
 RAM.TotalPhysicalMemory0,
 CPU.CurrentClockSpeed0

Output:

Computer Name
Domain Name
User Name
Bios Serial
Serial Enclosure
Manufacture
Model
Operating system
Sitecode
total memory

harddrive size

size

SCCM Count of installed applications report

Query:

Add collection ID in 'x'

select S.ARPDisplayName0, S.ProductVersion0, Count(*) as 'Count'
FROM v_GS_INSTALLED_SOFTWARE S, v_R_System B, v_FullCollectionMembership C
WHERE S.ResourceID = B.ResourceID
AND S.ResourceID = C.ResourceID
AND S.ARPDisplayName0 NOT LIKE 'Hotfix for %'
AND S.ARPDisplayName0 NOT LIKE 'Security Update for %'
AND S.ARPDisplayName0 NOT LIKE 'Update for Microsoft %'
AND S.ARPDisplayName0 NOT LIKE 'Update for Office %'
AND S.ARPDisplayName0 NOT LIKE 'Update for Outlook %'
AND S.ARPDisplayName0 NOT LIKE 'Update for Windows %'
AND S.ARPDisplayName0 NOT LIKE 'Windows 2000 Hotfix%'
AND S.ARPDisplayName0 NOT LIKE 'Windows Server 2003 Hotfix%'
AND S.ARPDisplayName0 NOT LIKE 'Windows XP Hotfix%'
AND S.ARPDisplayName0 NOT LIKE ''
AND CollectionID = 'X'
GROUP BY S.ARPDisplayName0, S.ProductVersion0
ORDER BY Count DESC

Output:

Product Name                 Product version          Count of machines

SCCM Count of IE versions report

Query:

Select 'IE5' as 'IE Version', COUNT(Distinct Sys.ResourceID ) as 'Count'
From v_GS_SoftwareFile SF
JOIN v_R_System sys on SF.ResourceID=sys.ResourceID
where (SF.FileName like 'iexplore.exe') and SF.Fileversion like '5.%' and (SF.FilePath like 'C:\Program Files\Internet Explorer\%') and sys.Operating_System_Name_and0 like '%workstation%'
UNION
Select 'IE6' as 'IE Version', COUNT(Distinct Sys.ResourceID ) as 'Count'
From v_GS_SoftwareFile SF
JOIN v_R_System sys on SF.ResourceID=sys.ResourceID
where (SF.FileName like 'iexplore.exe') and SF.Fileversion like '6.%' and (SF.FilePath like 'C:\Program Files\Internet Explorer\%') and sys.Operating_System_Name_and0 like '%workstation%'
UNION
Select 'IE7' as 'IE Version', COUNT(Distinct Sys.ResourceID ) as 'Count'
From v_GS_SoftwareFile SF
JOIN v_R_System sys on SF.ResourceID=sys.ResourceID
where (SF.FileName like 'iexplore.exe') and SF.Fileversion like '7.%' and (SF.FilePath like 'C:\Program Files\Internet Explorer\%') and sys.Operating_System_Name_and0 like '%workstation%'
UNION
Select 'IE8' as 'IE Version', COUNT(Distinct Sys.ResourceID ) as 'Count'
From v_GS_SoftwareFile SF
JOIN v_R_System sys on SF.ResourceID=sys.ResourceID
where (SF.FileName like 'iexplore.exe') and SF.Fileversion like '8.%' and (SF.FilePath like 'C:\Program Files\Internet Explorer\%') and sys.Operating_System_Name_and0 like '%workstation%'
UNION
Select 'IE9' as 'IE Version', COUNT(Distinct Sys.ResourceID ) as 'Count'
From v_GS_SoftwareFile SF
JOIN v_R_System sys on SF.ResourceID=sys.ResourceID
where (SF.FileName like 'iexplore.exe') and SF.Fileversion like '9.%' and (SF.FilePath like 'C:\Program Files\Internet Explorer\%') and sys.Operating_System_Name_and0 like '%workstation%'
UNION
Select 'IE10' as 'IE Version', COUNT(Distinct Sys.ResourceID ) as 'Count'
From v_GS_SoftwareFile SF
JOIN v_R_System sys on SF.ResourceID=sys.ResourceID
where (SF.FileName like 'iexplore.exe') and SF.Fileversion like '10.%' and (SF.FilePath like 'C:\Program Files\Internet Explorer\%') and sys.Operating_System_Name_and0 like '%workstation%'
UNION
Select 'IE11' as 'IE Version', COUNT(Distinct Sys.ResourceID ) as 'Count'
From v_GS_SoftwareFile SF
JOIN v_R_System sys on SF.ResourceID=sys.ResourceID
where (SF.FileName like 'iexplore.exe') and SF.Fileversion like '11.%' and (SF.FilePath like 'C:\Program Files\Internet Explorer\%') and sys.Operating_System_Name_and0 like '%workstation%'
ORDER BY 'IE Version'


Output:

IE Version               Count

SCCM last logon username report

Query:

Add collection ID in 'X'

Select A.Name0, A.UserName0  from V_GS_COMPUTER_SYSTEM A join v_FullCollectionMembership B on A.ResourceID = B.ResourceID Where B.CollectionID ='X'

Output:

Machine name      username

SCCM Count of computer models report

Query:

Add collection ID in 'X'


SELECT CSYS.Model0, Count(*) as 'Count'

FROM v_GS_COMPUTER_SYSTEM CSYS

JOIN v_FullCollectionMembership fcm on CSYS.ResourceID=fcm.ResourceID

WHERE fcm.CollectionID='X'

GROUP BY CSYS.Model0

ORDER BY Count DESC

Output:

Model of computer       Count of machines

Cleanup CCMCache content older than 20 days

Create vbs file with the below codes and create a package in SCCM and run the deployment in a schedule cycle.

Modify X value in Const intDaysOld = "x" for days. eg: 30 equal to 30 days

Dim objFSO
Dim objFolder
Dim objSubFolder
Dim winsh
Dim winenv

Const intDaysOld = 20
set winsh = CreateObject("WScript.Shell")
set winenv = winsh.Environment("Process")
windir = winenv("WINDIR")
Set objFSO = CreateObject("Scripting.FileSystemObject")
if objFSO.FolderExists (windir & "\ccmcache") Then
Set objFolder = objFSO.GetFolder(windir & "\ccmcache")
For Each objSubFolder In objFolder.SubFolders
                If objSubFolder.DateLastModified < DateValue(Now() - intDaysOld) Then
           objSubFolder.Delete True
    End If
Next
End if