select 
SMS_G_System_LastSoftwareScan.LastScanDate, 
SMS_R_System.Name, 
SMS_G_System_WORKSTATION_STATUS.LastHardwareScan 
from  SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId order by SMS_G_System_WORKSTATION_STATUS.LastHardwareScan

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.1%" or SMS_R_System.OperatingSystemNameandVersion like "%Windows 7%" and SMS_G_System_COMPUTER_SYSTEM.SystemType = "x64-based PC"

select 
SMS_R_System.ResourceId, 
SMS_R_System.ResourceType, 
 SMS_R_System.Name, 
SMS_R_System.SMSUniqueIdentifier, 
 SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System where SMS_R_System.MACAddresses = "XX:XX:XX:XX:XX:XX" 

***********************************************

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.PrimaryGroupID = "516"

**********************************************

This query will show you basic info on a single machine. you have to leave emprty the collection limitation to be able to browse the single machine.:



**********************************

select distinct SMS_R_System.NetbiosName, SMS_R_System.LastLogonUserName, SMS_R_System.IPAddresses, SMS_R_System.MACAddresses, SMS_R_System.OperatingSystemNameandVersion, SMS_G_System_COMPUTER_SYSTEM.Manufacturer, SMS_G_System_COMPUTER_SYSTEM.Model, SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory, SMS_G_System_PROCESSOR.CurrentClockSpeed from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_X86_PC_MEMORY on SMS_G_System_X86_PC_MEMORY.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PROCESSOR on SMS_G_System_PROCESSOR.ResourceID = SMS_R_System.ResourceId where SMS_R_System.NetbiosName = ##PRM:SMS_R_System.NetbiosName##

*************************************

This query will show the machine name, IP address, and ram memory:

********************************

select distinct 

SMS_R_System.NetbiosName, 

SMS_G_System_PC_BIOS.SerialNumber, SMS_G_System_X86_PC_MEMORY.TotalPhysicalMemory, 

SMS_R_System.IPAddresses, SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.DNSDomain 

from  SMS_R_System inner join SMS_G_System_PC_BIOS 

on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId 

inner join SMS_G_System_X86_PC_MEMORY 

on SMS_G_System_X86_PC_MEMORY.ResourceID = SMS_R_System.ResourceId 

inner join SMS_G_System_NETWORK_ADAPTER_CONFIGURATION 

on SMS_G_System_NETWORK_ADAPTER_CONFIGURATION.ResourceID = SMS_R_System.ResourceId 

order by SMS_R_System.NetbiosName

*****************************************

this simple report will show a count of all IE versions in your workstations:

***********************************

select 
    SF.FileName,
    OS.Caption0,
    replace(left(SF.FileVersion,2), '.','') as 'IE Version',
    Count (Distinct SF.ResourceID) as 'Total'
From 
    dbo.v_GS_SoftwareFile SF 
    JOIN v_FullCollectionMembership fcm on SF.ResourceID=fcm.ResourceID
    JOIN dbo.v_GS_OPERATING_SYSTEM OS ON SF.ResourceID = OS.ResourceID
    join dbo.v_GS_SYSTEM S on SF.ResourceID = S.ResourceID
Where 
    SF.FileName = 'iexplore.exe' 
    and SF.FilePath like '%Internet Explorer%'
    and S.SystemRole0 = 'Workstation'
Group by 
    SF.FileName, 
    OS.Caption0,
    replace(left(SF.FileVersion,2), '.','')
Order by 
    2

***********************************

This query will help you check obsolete clients in SCCM:



********************************************

select SMS_R_System.ResourceID,
SMS_R_System.ResourceType,
SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client from SMS_R_System where obsolete = 1

*****************************************

This report will show you a list of all servers detected in SCCM with server hostname, OS and more features:

********************************************

SELECT distinct
CS.name0 as 'Server Name',
OS.Caption0 as 'OS',
CU.Manufacturer0 as 'Manufacturer',
CU.Model0 as 'Model',
RAM.TotalPhysicalMemory0/1024 as [RAM (MB)],
processor.Name0 as 'Processor',
BIOS.ReleaseDate0 as 'BIOS Manufacture Date',
OS.InstallDate0 as 'OS Install Date'

from
v_R_System CS
FULL join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
FULL join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
FULL join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
FULL JOIN v_GS_PROCESSOR Processor on Processor.ResourceID=CS.ResourceID
FULL join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
FULL join v_GS_COMPUTER_SYSTEM CU on CU.ResourceID = CS.ResourceID

WHERE CS.Operating_System_Name_and0 LIKE '%nt%server%' and CS.Client0 = 1

group by
CS.Name0,
OS.Caption0,
CU.Manufacturer0,
CU.Model0,
RAM.TotalPhysicalMemory0,
BIOS.ReleaseDate0,
OS.InstallDate0,
Processor.Name0,
BIOS.ReleaseDate0
Order by CS.Name0

************************************************

Report that shows you all Adobe products filtred by machine name and AD site:

The following script will clean all machine sccm cache, Watch OUT: It works only on machines with SCCM 2007 client.

'$~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'
'
' NAME: Clean Cache
'
' AUTHOR:
' DATE  : 01/03/2013
'
' COMMENT:
'
'Exemplary damages arising out of or in any way relating to the use of this script,
'including without limitation damages for loss of goodwill, work stoppage,
'lost profits, loss of data, and computer failure or malfunction.
'You bear the entire risk as to the quality and performance of this script.
'$~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Dim objFSO
Dim objFolder
Dim objSubFolder
Dim winsh
Dim winenv

'deletes folders with a date modified of 7 day or older
Const intDaysOld = 7
set winsh = CreateObject("WScript.Shell")
set winenv = winsh.Environment("Process")
windir = winenv("WINDIR")
Set objFSO = CreateObject("Scripting.FileSystemObject")
'looks for \system32\ccm\cache for 32bit
if objFSO.FolderExists (windir & "\system32\ccm\cache") Then
Set objFolder = objFSO.GetFolder(windir & "\system32\ccm\cache")
For Each objSubFolder In objFolder.SubFolders
                If objSubFolder.DateLastModified < DateValue(Now() - intDaysOld) Then
           objSubFolder.Delete True
    End If
Next
  Wscript.quit
End if
'looks for \sysWOW64\ccm\cache for 64bit
if objFSO.FolderExists (windir & "\sysWOW64\ccm\cache") Then
Set objFolder = objFSO.GetFolder(windir & "\sysWOW64\ccm\cache")
For Each objSubFolder In objFolder.SubFolders
                If objSubFolder.DateLastModified < DateValue(Now() - intDaysOld) Then
           objSubFolder.Delete True
    End If
Next
End if

This Report will show the exact machine last boot up time, HW scan, last heartbeat:

This is a report that check the status of machines and detect if they're not contacting wit SCCM :

This Dashboard will show all active deployments on your SMS  - SCCM with all percentage of each one:

Some customers could ask for a live dashboard to follow a specific deployment. This one will help.

It's a detailed dashboard pointing to the deployment advertisement:

*****************************************************************************
declare @Total int
declare @Accepted int
declare @AdvName VARCHAR(100)
set @AdvName = 'type here your advertisement name'

SELECT @Total=count(*), @Accepted=sum(case LastState when 0 then 0 else 1 end)
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)

SELECT LastAcceptanceStateName as 'Status', count(*) as 'Number of Resources', 
      ROUND(100.0*count(*)/@Total,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastAcceptanceStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID

SELECT LastStateName as 'Status of Targeted Resources', count(*) as 'Number of Resources', 
       ROUND(100.0*count(*)/@Accepted,1) as 'Percent of Resources',
ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID
FROM v_ClientAdvertisementStatus INNER JOIN
v_Advertisement ON v_ClientAdvertisementStatus.AdvertisementID = v_Advertisement.AdvertisementID
WHERE (v_Advertisement.AdvertisementName LIKE @AdvName)
group by LastStateName, ProgramName, AdvertisementName, v_ClientAdvertisementStatus.AdvertisementID


SELECT a.Netbios_name0 as 'Host Name', a.Resource_Domain_OR_Workgr0,
site.sms_installed_sites0 as 'Sitecode',
a.Client0,
a.Obsolete0,
adv.AdvertisementName,
adv.AdvertisementID,
pkg.Name AS 'Package Name',
adv.ProgramName,
advstate.LastAcceptanceStatusTime,
advstate.LastAcceptanceStateName,
advstate.LastAcceptanceMessageIDname,
advstate.LastStatusmessageIDName,
advstate.LaststateName,
advstate.LastExecutionResult,
advstate.LastStatusTime, 
advstate.LastExecutionContext
FROM v_Advertisement adv
INNER JOIN v_Package pkg ON adv.PackageID = pkg.PackageID
INNER JOIN v_ClientAdvertisementStatus advstate on adv.AdvertisementID=advstate.AdvertisementID
INNER JOIN V_R_SYSTEM a ON a.Resourceid=advstate.resourceid
INNER JOIN v_GS_WORKSTATION_STATUS HW ON a.resourceid=hw.resourceid
INNER JOIN v_GS_LastSoftwareScan sw ON a.resourceid=sw.resourceid
LEFT OUTER JOIN v_RA_System_SMSInstalledSites site ON a.resourceid=site.resourceid
WHERE ADV.AdvertisementName like @AdvName

order by advstate.LaststateName

*****************************************************************************

the result will be like this:

This is a full soft. inventory report. It could be a little bit confusing depending how many machines you have in your SCCM and how many softwares installed, but it will help you for a first overview:

This Report will count all OS in SCCM :

************************************************************************************************

select
    OS.Caption0,
    CS.SystemType0,
    Count(*)
from
    dbo.v_GS_COMPUTER_SYSTEM CS Left Outer Join dbo.v_GS_OPERATING_SYSTEM OS on CS.ResourceID = OS.ResourceId
Group by
    OS.Caption0,
    CS.SystemType0
Order by
    OS.Caption0,
    CS.SystemType0

**************************************************************************************************

This report will show the HD space status of all the machines in SMS/SCCM:

****************************************************************************************
SELECT 
SYS.Name,
LDISK.DeviceID0,  
ou.System_OU_Name0,
LDISK.FreeSpace0 as [Free space (MB)],
LDISK.FreeSpace0/1024 as [Free space (GB)],
LDISK.Size0/1024 as [Total space (GB)],
LDISK.FreeSpace0*100/LDISK.Size0 as C074
FROM v_FullCollectionMembership SYS
join v_RA_System_SystemOUName ou ON sys.ResourceID = ou.ResourceID
join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID
JOIN v_R_System RSYS ON SYS.ResourceID = RSYS.ResourceID
WHERE
LDISK.DriveType0 =3 AND
LDISK.Size0 > 0
AND SYS.CollectionID = 'SMS00001'
ORDER BY SYS.Name, LDISK.DeviceID0

*******************************************************************************************

This is a different hardware inventory report:


Select

SD.Name0 'Machine Name',
SD.User_Name0,
SD.AD_Site_Name0,
OU.System_OU_Name0,
CS.Manufacturer0 Manufacturer,
CS.Model0 Model,
SN.SerialNumber0 'Serial Number',
OS.Caption0 + Space(1) + OS.CsdVersion0 'Operating System'

From v_R_System SD

Join v_GS_COMPUTER_SYSTEM CS On SD.ResourceID = CS.ResourceID

Join v_GS_SYSTEM_ENCLOSURE SE On SD.ResourceID = SE.ResourceID

Join v_GS_PC_BIOS SN ON SD.ResourceID = SN.ResourceID

Join v_GS_OPERATING_SYSTEM OS On SD.ResourceID = OS.ResourceID

INNER JOIN (SELECT ResourceID, MAX(System_OU_Name0) AS System_OU_Name0 FROM dbo.v_RA_System_SystemOUName GROUP BY ResourceID) AS ou ON ou.ResourceID = SD.ResourceID

 Where SD.Client0 = 1

This is a very useful SMS report for beginners:

it's a hardware inventory report completed by Machine name user name, and OU name:

Select distinct

SD.Name0 'Machine Name',
SD.User_Name0,
SD.AD_Site_Name0,
OU.System_OU_Name0,
CS.Manufacturer0 Manufacturer,
CS.Model0 Model,
PR.Name0 'CPU',
ME.TotalPhysicalMemory0 AS [Memory (KBytes],
SN.SerialNumber0 'Serial Number',
OS.Caption0 + Space(1) + OS.CsdVersion0 'Operating System'

From v_R_System SD

Join v_GS_COMPUTER_SYSTEM CS On SD.ResourceID = CS.ResourceID

Join v_GS_SYSTEM_ENCLOSURE SE On SD.ResourceID = SE.ResourceID

Join v_GS_PC_BIOS SN ON SD.ResourceID = SN.ResourceID

Join v_GS_OPERATING_SYSTEM OS On SD.ResourceID = OS.ResourceID

Join v_GS_PROCESSOR PR ON SD.ResourceID = PR.ResourceID

Join v_GS_X86_PC_MEMORY ME ON SD.ResourceID = ME.ResourceID

INNER JOIN (SELECT ResourceID, MAX(System_OU_Name0) AS System_OU_Name0 FROM dbo.v_RA_System_SystemOUName GROUP BY ResourceID) AS ou ON ou.ResourceID = SD.ResourceID

 Where SD.Client0 = 1