SCCM applications displayname report with install source location and username

Query:


SELECT        dbo.v_GS_SYSTEM.Name0 AS [Machine Name], dbo.v_R_System.User_Name0 AS UserName, dbo.v_GS_SYSTEM.SystemType0 AS [Proceesor Type], dbo.v_R_System.AD_Site_Name0 AS [AD Site Name],
                         dbo.v_R_System.Operating_System_Name_and0 AS [Operating System], dbo.v_GS_INSTALLED_SOFTWARE.Publisher0 AS Manufacture,
                         dbo.v_GS_INSTALLED_SOFTWARE.ARPDisplayName0 AS [ARP Application Name], dbo.v_GS_INSTALLED_SOFTWARE.ProductName0 AS [Application Name],
                         dbo.v_GS_INSTALLED_SOFTWARE.ProductVersion0 AS [Software Version], dbo.v_GS_INSTALLED_SOFTWARE.InstallDate0 AS InstallDate, dbo.v_GS_INSTALLED_SOFTWARE.InstallSource0 AS [Install Source],
                         dbo.v_GS_INSTALLED_SOFTWARE.InstalledLocation0 AS [Installed Location]
FROM            dbo.v_GS_INSTALLED_SOFTWARE INNER JOIN
                         dbo.v_R_System ON dbo.v_GS_INSTALLED_SOFTWARE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN
                         dbo.v_GS_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_SYSTEM.ResourceID
WHERE        (dbo.v_GS_INSTALLED_SOFTWARE.ARPDisplayName0 LIKE '%autocad%')

order by dbo.v_GS_INSTALLED_SOFTWARE.InstallDate0 desc

SCCM application installed manual report

Query:

SELECT    dbo.v_GS_SYSTEM.Domain0, dbo.v_GS_SYSTEM.Name0, dbo.v_GS_SYSTEM.SystemType0, dbo.v_GS_SYSTEM.SystemRole0,
                      dbo.v_GS_INSTALLED_SOFTWARE.ResourceID, dbo.v_GS_INSTALLED_SOFTWARE.InstallDate0,
                      dbo.v_GS_INSTALLED_SOFTWARE.ARPDisplayName0, dbo.v_GS_INSTALLED_SOFTWARE.InstalledLocation0,
                      dbo.v_GS_INSTALLED_SOFTWARE.InstallSource0, dbo.v_GS_INSTALLED_SOFTWARE.ProductVersion0,
                      dbo.v_GS_INSTALLED_SOFTWARE.Publisher0, dbo.v_GS_INSTALLED_SOFTWARE.ProductName0
FROM         dbo.v_GS_INSTALLED_SOFTWARE INNER JOIN
                      dbo.v_GS_SYSTEM ON dbo.v_GS_INSTALLED_SOFTWARE.ResourceID = dbo.v_GS_SYSTEM.ResourceID
Where dbo.v_GS_INSTALLED_SOFTWARE.InstallSource0 not like '%SMS%$%'
order by dbo.v_GS_INSTALLED_SOFTWARE.InstallDate0 desc

SCCM Application displayname present in the machines report

Query:

SELECT     TOP (100) PERCENT dbo.v_R_System.Netbios_Name0, dbo.v_R_System.User_Name0, dbo.v_R_System.Operating_System_Name_and0,
                      dbo.v_Add_Remove_Programs.DisplayName0, dbo.v_Add_Remove_Programs.InstallDate0, dbo.v_Add_Remove_Programs.Publisher0
FROM         dbo.v_Add_Remove_Programs INNER JOIN
                      dbo.v_R_System ON dbo.v_Add_Remove_Programs.ResourceID = dbo.v_R_System.ResourceID
WHERE     (dbo.v_Add_Remove_Programs.DisplayName0 LIKE '%autocad%')
ORDER BY dbo.v_R_System.Netbios_Name0 

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