SCCM application displayname report with AD site and user name

Query:

SELECT     TOP (100) PERCENT dbo.v_R_System.Netbios_Name0, dbo.v_R_System.AD_Site_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, dbo.v_Add_Remove_Programs.Version0

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 '%topspro%')

ORDER BY dbo.v_R_System.Netbios_Name0 

SCCM Internet Explorer detection method

Registry method:

HKLM\SOFTWARE\Wow6432Node\Microsoft\Internet Explorer

svcVersion

begins with 11 or 10 or 9 or 8 (for diff versions of IE)

OR

HKLM\SOFTWARE\Microsoft\Internet Explorer

svcVersion

begins with 11 or 10 or 9 or 8 (for diff versions of IE)

Detection method will work post reboot

SCCM Adobe Flashplayer detection method

install_flash_player_24_active_x.msi:

HKLM\SOFTWARE\Macromedia\FlashPlayerActiveX
Version
24.0.0.194

install_flash_player_24_plugin.msi

HKLM\SOFTWARE\Macromedia\FlashPlayerPlugin
Verison
24.0.0.194

install_flash_player_24_ppapi.msi

HKLM\SOFTWARE\Macromedia\FlashPlayerPepper
Version
24.0.0.194

SCCM Identify package ID installed with user rights or system rights report

Package ID value with 1 --> Run mode - Run with user rights--> User context installation
Package ID value with 0--> Run mode - Run with administrative rights - system context installation


Query:

DECLARE @ProgramFlags TABLE (

   BitFlag INT PRIMARY KEY,

   Meaning VARCHAR(128),

   Description VARCHAR(512)

)

INSERT INTO @ProgramFlags

VALUES

   (2,'USECUSTOMPROGRESSMSG','The task sequence shows a custom progress user interface message.'),

   (16,'DEFAULT_PROGRAM','This is a default program.'),

   (32,'DISABLEMOMALERTONRUNNING','Disables MOM alerts while the program runs.'),

   (64,'MOMALERTONFAIL','Generates MOM alert if the program fails.'),

   (128,'RUN_DEPENDANT_ALWAYS','If set, this program”s immediate dependent should always be run.'),

   (256,'WINDOWS_CE','Indicates a device program.  If set, the program is not offered to desktop clients.'),

   (1024,'COUNTDOWN','The countdown dialog is not displayed.'),

   (4096,'DISABLED','The program is disabled.'),

   (8192,'UNATTENDED','The program requires no user interaction.'),

   (16384,'USERCONTEXT','The program can only run when a user is logged on.'),

   (32768,'ADMINRIGHTS','The program must be run as the local Administrator account.'),

   (65536,'EVERYUSER','The program must be run by every user for whom it is valid. Valid only for mandatory jobs.'),

   (131072,'NOUSERLOGGEDIN','The program is only run when no user is logged on.'),

   (262144,'OKTOQUIT','The program will restart the computer'),

   (524288,'OKTOREBOOT','Configuration Manager restarts the computer when the program has finished running successfully.'),

   (1048576,'USEUNCPATH','Use a UNC path (no drive letter to access)'),

   (2097152,'PERSISTCONNECTION','Persists the connection to the drive specified in the DriveLetter property.  The USEUNCPATH bit flag must not be set.'),

   (4194304,'RUNMINIMIZED','Run the program as a minimized window.'),

   (8388608,'RUNMAXIMIZED','Run the program as a maximized window.'),

   (16777216,'HIDEWINDOW','Hide the program window.'),

   (33554432,'OKTOLOGOFF','Logoff user when program completes successfully.'),

   (134217728,'ANY_PLATFORM','Override check for platform support.'),

   (536870912,'SUPPORT_UNINSTALL','Run uninstall from the registry key when the advertisement expires.');

WITH cte AS (

   SELECT

      f.BitFlag,

      f.Meaning,

      (f.BitFlag & p.ProgramFlags)/f.BitFlag AS [Enabled],

      p.PackageID,

      p.ProgramName

   FROM

      dbo.v_Program p

      CROSS JOIN @ProgramFlags f

)  

SELECT

   PackageID,

   ProgramName,

   MAX(CASE BitFlag WHEN 2 THEN [Enabled] END) AS [USECUSTOMPROGRESSMSG],

   MAX(CASE BitFlag WHEN 16 THEN [Enabled] END) AS [DEFAULT_PROGRAM],

   MAX(CASE BitFlag WHEN 32 THEN [Enabled] END) AS [DISABLEMOMALERTONRUNNING],

   MAX(CASE BitFlag WHEN 64 THEN [Enabled] END) AS [MOMALERTONFAIL],

   MAX(CASE BitFlag WHEN 128 THEN [Enabled] END) AS [RUN_DEPENDANT_ALWAYS],

   MAX(CASE BitFlag WHEN 256 THEN [Enabled] END) AS [WINDOWS_CE],

   MAX(CASE BitFlag WHEN 1024 THEN [Enabled] END) AS [COUNTDOWN],

   MAX(CASE BitFlag WHEN 4096 THEN [Enabled] END) AS [DISABLED],

   MAX(CASE BitFlag WHEN 8192 THEN [Enabled] END) AS [UNATTENDED],

   MAX(CASE BitFlag WHEN 16384 THEN [Enabled] END) AS [USERCONTEXT],

   MAX(CASE BitFlag WHEN 32768 THEN [Enabled] END) AS [ADMINRIGHTS],

   MAX(CASE BitFlag WHEN 65536 THEN [Enabled] END) AS [EVERYUSER],

   MAX(CASE BitFlag WHEN 131072 THEN [Enabled] END) AS [NOUSERLOGGEDIN],

   MAX(CASE BitFlag WHEN 262144 THEN [Enabled] END) AS [OKTOQUIT],

   MAX(CASE BitFlag WHEN 524288 THEN [Enabled] END) AS [OKTOREBOOT],

   MAX(CASE BitFlag WHEN 1048576 THEN [Enabled] END) AS [USEUNCPATH],

   MAX(CASE BitFlag WHEN 2097152 THEN [Enabled] END) AS [PERSISTCONNECTION],

   MAX(CASE BitFlag WHEN 4194304 THEN [Enabled] END) AS [RUNMINIMIZED],

   MAX(CASE BitFlag WHEN 8388608 THEN [Enabled] END) AS [RUNMAXIMIZED],

   MAX(CASE BitFlag WHEN 16777216 THEN [Enabled] END) AS [HIDEWINDOW],

   MAX(CASE BitFlag WHEN 33554432 THEN [Enabled] END) AS [OKTOLOGOFF],

   MAX(CASE BitFlag WHEN 134217728 THEN [Enabled] END) AS [ANY_PLATFORM],

   MAX(CASE BitFlag WHEN 536870912 THEN [Enabled] END) AS [SUPPORT_UNINSTALL]

FROM

   cte

GROUP BY

   PackageID,

   ProgramName

SCCM count of particular applications report

Query:

select
    DisplayName0,
    Version0,
    Count (Distinct arp.ResourceID)
From
    dbo.v_Add_Remove_Programs ARP
Where
    DisplayName0 Like ('%A%') or
    DisplayName0 Like ('%B%') or
    DisplayName0 Like ('%C%')

Group by
    DisplayName0,
    Version0
Order by
    DisplayName0,
    Version0

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

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