SCCM Sql Query to extract List of all the packages and programs

SELECT Program.PackageID,
Package.Name 'Package Name',
Program.ProgramName 'Program Name',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM [v_Program] as Program
LEFT JOIN v_Package as Package on Package.PackageID = Program.PackageID
Order by Program.PackageID

SCCM SQL Query to extract machine name and its last reboot timestamp

Select sys.Netbios_Name0 AS Machine_Name, os.lastbootuptime0 AS Last_Boot_time
from v_r_system sys
inner join  v_FullCollectionMembership col on sys.resourceid=col.resourceid
left join v_gs_operating_system os on sys.resourceid=os.resourceid
where col.CollectionID = 'collectionID'

Give the collection Id and execute the report

Export AD Computers to CSV file

Import-Module ActiveDirectory
Get-ADComputer -Filter 'OperatingSystem -like "*Server 2008 R2*"' -Properties * |
 Select -Property Name,operatingSystem,@{Name="LastLogon";
Expression={[DateTime]::FromFileTime($_.lastLogon).ToString()}} |
# Export AD Computer Report to CSV file 
Export-CSV "C:\\ADComputers.csv" -NoTypeInformation -Encoding UTF8


We can generate and export all computer details to CSV file by using Export-CSV cmdlet. You can add more attributes in Select -Property field to export more AD attributes. 

SCCM – Report all software in Add and Remove Programs

Select DISTINCT SYS.Netbios_Name0,
SYS.Resource_Domain_OR_Workgr0,
SP.CompanyName,
SP.ProductName,
SP.ProductVersion
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS ON SP.ResourceID=SYS.ResourceID
JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
WHERE fcm.CollectionID = ‘<CollectionID>’
ORDER By SYS.Netbios_Name0, SP.CompanyName, SP.ProductName, SP.ProductVersion



replace <CollectionID> with your actual collection ID before you execute the query.

Query to find Number of days count of all client machines since Last Bootuptime

select a.Netbios_name0, e.Caption0 as OS, e.CSDversion0 as 'SP level',a.resourceid,a.Creation_date0,a.client0, a.ad_site_name0,a.obsolete0,
b.sms_installed_sites0 as 'sitecode',c.Lasthwscan,d.Agenttime as 'Last heartbeat discovery ran',e.LastBootUpTime0,DATEDIFF(dd, LastBootUpTime0, GETDATE())AS 'Number of Days since last bootup'
from v_r_system a
left outer join v_RA_System_SMSInstalledSites b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
Left outer join v_AgentDiscoveries d on d.resourceid=a.resourceid
left outer join v_gs_operating_system e on e.resourceid=a.resourceid
where d.AgentName='Heartbeat Discovery' and (a.client0=1 and a.Obsolete0=0 and a.Active0=1)
and a.Name0 IN (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) < 2)
order by e.LastBootUpTime0 desc

Query to find Bootuptime of all SMS client machines for a specific time frame

select a.Netbios_name0, e.Caption0 as OS, e.CSDversion0 as 'SP level',a.resourceid,a.Creation_date0,a.client0, a.ad_site_name0,a.obsolete0,
b.sms_installed_sites0 as 'sitecode',e.LastBootUpTime0,c.Lasthwscan,d.Agenttime as 'Last heartbeat discovery ran'
from v_r_system a
left outer join v_RA_System_SMSInstalledSites b on a.resourceid=b.resourceid
left outer join v_GS_WORKSTATION_STATUS c on a.resourceid=c.resourceid
Left outer join v_AgentDiscoveries d on d.resourceid=a.resourceid
left outer join v_gs_operating_system e on e.resourceid=a.resourceid
where d.AgentName='Heartbeat Discovery' and d.Agenttime >=getdate()-30

SMS Report for the packages advertised but not yet updated the DP

SELECT a.AdvertisementID,a.AdvertisementName,a.PresentTime as 'Advert Start Date',b.Name as 'Package Name',b.PackageID,count(b.PackageID) as 'Count of pending DP'
FROM v_Advertisement a
INNER JOIN v_Package b ON a.PackageID = b.PackageID
INNER JOIN v_DistributionPoint c ON b.PackageID = c.PackageID
INNER JOIN v_PackageStatusDistPointsSumm d ON c.PackageID = d.PackageID
WHERE d.state=1
GROUP BY a.AdvertisementID,a.AdvertisementName,a.PresentTime,b.PackageID,b.Name

Description: This report gives the count of each distribution points in which a specific advertised package is not updated.

SMS Report to list all the machines that haven't reported its discovery/inventory since past 30 days

select sys.Netbios_Name0,
sys.Client_Type0,
sys.Creation_date0 as 'Creation Date',
sys.Client_Version0,
sys.User_Domain0,
sys.User_Name0,
inst.sms_assigned_sites0 as 'sitecode',
disc.AgentTime as 'Last Discovery date',
hw.LastHWScan as 'Last HW Scan',
sw.LastScanDate as 'Last SW Scan'
from v_R_System sys
left join
(
select ResourceId, MAX(AgentTime) as AgentTime
from v_AgentDiscoveries
group by ResourceId
) as disc on disc.ResourceId = sys.ResourceID
left join v_GS_WORKSTATION_STATUS hw on hw.ResourceID = sys.ResourceID
left join v_GS_LastSoftwareScan sw on sw.ResourceID = sys.ResourceID
left join v_RA_System_SMSAssignedSites inst on inst.resourceid=sys.resourceid
where
disc.AgentTime < getdate()-30
or hw.LastHWScan < getdate()-30
or sw.LastScanDate < getdate()-30
and (sys.Client0 = 1 and sys.Obsolete0=0)
and sys.Name0 in (SELECT Name0
FROM v_r_system
GROUP BY Name0
HAVING COUNT(*) < 2)
order by disc.AgentTime asc

SMS Report/Query to find the current IP Address of client machines

SELECT
[ComputerName],
[IP Address],
[Subnet Mask],
[Timestamp]
FROM
(
select
row_number() over (partition by cfg.IPAddress0 order by cfg.Timestamp desc) as rownum,
sys.netbios_name0 AS [ComputerName],
cfg.IPAddress0 AS [IP Address],
cfg.IPSubnet0 AS [Subnet Mask],
cfg.Timestamp AS [Timestamp]
FROM
dbo.v_R_System AS sys
INNER JOIN dbo.v_GS_Network_Adapter_Configur AS cfg
ON sys.resourceID = cfg.resourceID
AND cfg.IPEnabled0 = 1
WHERE
sys.obsolete0 = 0
AND sys.decommissioned0 = 0
AND sys.client0 = 1
AND sys.Obsolete0=0
AND cfg.IPaddress0 NOT LIKE '%,%'
AND cfg.IPAddress0 NOT LIKE '0.0%')as dt
where rownum=1
order by [Timestamp] desc

Collection query to find windows 2003 server with Service pack 1 version

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_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Obsolete = 0 and SMS_R_System.Client = 1 and SMS_G_System_OPERATING_SYSTEM.Caption like "%windows%2003%" and SMS_G_System_OPERATING_SYSTEM.CSDVersion like "Service Pack1"

Collection query to find all machines with 1 GB or more free space in C Drive

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_LOGICAL_DISK on SMS_G_System_LOGICAL_DISK.ResourceID = SMS_R_System.ResourceId where SMS_R_System.Client = 1 and SMS_R_System.Obsolete = 0 and SMS_R_System.Active = 1 and SMS_G_System_LOGICAL_DISK.DriveType = 3 and SMS_G_System_LOGICAL_DISK.DeviceID = "C:" and SMS_G_System_LOGICAL_DISK.FreeSpace >= 1024

SMS Report to find machines with duplicate serial numbers

SELECT
a.Name0,
a.UserName0,
d.sms_installed_sites0 as 'Site Code',
e.Lasthwscan as 'Last hardware Scan',
repeat.SerialNumber0
FROM
v_GS_COMPUTER_SYSTEM a,v_r_system c,v_RA_System_SMSInstalledSites d,v_GS_WORKSTATION_STATUS e,
v_GS_PC_BIOS b,
(SELECT b.SerialNumber0 FROM v_GS_PC_BIOS b GROUP BY
b.SerialNumber0 HAVING (Count(b.SerialNumber0)>1)) as
repeat
WHERE
b.ResourceID = a.ResourceID
and a.ResourceID=c.resourceid
and b.resourceid=d.resourceid
and b.resourceid=e.resourceid
and b.SerialNumber0 = repeat.SerialNumber0
and (c.client0=1 and c.obsolete0=0 and c.Active0=1)
order by
b.SerialNumber0,
a.Name0,
a.UserName0