SCCM SQL query to get all Package deployment status to specific collection

Select
Left(Ds.SoftwareName, CharIndex('(',(Ds.SoftwareName))-1)as 'PackageName',
Ds.ProgramName 'Program_name',
Ds.CollectionName as 'CollectionName',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose',
Ds.NumberTotal as 'Target',
Ds.NumberSuccess as 'Success',
Ds.NumberInProgress as 'Progress',
Ds.NumberErrors as 'Errors',
Ds.NumberOther as 'ReqNotMet',
Ds.NumberUnknown as 'Unknown'
from v_DeploymentSummary Ds
join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID
Where Ds.FeatureType = 2 and ds.CollectionID='Collectionid'
order by Ds.DeploymentTime desc

SCCM SQL query to get all Package deployment status to all collection

Select
Left(Ds.SoftwareName, CharIndex('(',(Ds.SoftwareName))-1)as 'PackageName',
Ds.ProgramName 'Program_name',
Ds.CollectionName as 'CollectionName',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' End as 'Purpose',
Ds.NumberTotal as 'Target',
Ds.NumberSuccess as 'Success',
Ds.NumberInProgress as 'Progress',
Ds.NumberErrors as 'Errors',
Ds.NumberOther as 'ReqNotMet',
Ds.NumberUnknown as 'Unknown'
from v_DeploymentSummary Ds
join v_Advertisement Vaa on Ds.OfferID = Vaa.AdvertisementID
Where Ds.FeatureType = 2
order by Ds.DeploymentTime desc

SCCM SQL query to get all application deployment status to Specific collection

Select
Vaa.ApplicationName as 'ApplicationName',
Ds.CollectionName as 'CollectionName',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' when Ds.DeploymentIntent = 3 Then 'Simulate' End as 'Purpose',
Ds.NumberTotal as 'Target',
Ds.NumberSuccess as 'Success',
Ds.NumberInProgress as 'Progress',
Ds.NumberErrors as 'Errors',
Ds.NumberOther as 'ReqNotMet',
Ds.NumberUnknown as 'Unknown',
Vaa.CreationTime as 'CreationTime',
Vaa.LastModificationTime as 'LastModifiedTime',
Vaa.LastModifiedBy as 'LastModifiedBy'
from v_DeploymentSummary Ds
left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
Where Ds.FeatureType = 1 and ds.CollectionID='CollectionID'
order by Ds.DeploymentTime desc

SCCM SQL query to get all application deployment status to all collection

Select
Vaa.ApplicationName as 'ApplicationName',
Ds.CollectionName as 'CollectionName',
CASE when Ds.DeploymentIntent = 1 Then 'Required' when Ds.DeploymentIntent = 2 Then 'Available' when Ds.DeploymentIntent = 3 Then 'Simulate' End as 'Purpose',
Ds.NumberTotal as 'Target',
Ds.NumberSuccess as 'Success',
Ds.NumberInProgress as 'Progress',
Ds.NumberErrors as 'Errors',
Ds.NumberOther as 'ReqNotMet',
Ds.NumberUnknown as 'Unknown',
Vaa.CreationTime as 'CreationTime',
Vaa.LastModificationTime as 'LastModifiedTime',
Vaa.LastModifiedBy as 'LastModifiedBy'
from v_DeploymentSummary Ds
left join v_ApplicationAssignment Vaa on Ds.AssignmentID = Vaa.AssignmentID
Where Ds.FeatureType = 1
order by Ds.DeploymentTime desc

SCCM SQL Query to get Dependent Application to an application

If oracle Client is marked as dependency application for the most of the application in the environment, then this below query will come in handy.

Just update application name in the highlighted place in the below query.

select parentapp.displayname [Application Name],

parentapp.CreatedBy [Created By],

ParentApp_F.Name AS [ParentAppFolder] ,

ParentApp_DT.Technology AS [ParentAppDeploymentType],

case when parentapp.IsEnabled='1' then 'Yes' else 'No' end as IsEnabled,

case when parentapp.IsDeployed='1' then 'Yes' else 'No' end as IsDeployed,

parentapp.NumberOfDeploymentTypes [No of DT],

parentapp.NumberOfDeployments [No of Deployments],

parentapp.NumberOfDevicesWithApp,

parentapp.NumberOfDevicesWithFailure

From dbo.fn_ListApplicationCIs(1033) ParentApp

Left Join dbo.vFolderMembers ParentApp_FM on ParentApp_FM.InstanceKey = ParentApp.ModelName

       Left Join dbo.vSMS_Folders ParentApp_F on ParentApp_F.ContainerNodeID = ParentApp_FM.ContainerNodeID

       Left Join fn_ListDeploymentTypeCIs(1033) ParentApp_DT ON ParentApp_DT.AppModelName = ParentApp.ModelName

       Left Join vSMS_AppRelation_Flat R on R.FromApplicationCIID = ParentApp.CI_ID

       Left Join fn_ListApplicationCIs_List(1033) ChildApp on ChildApp.CI_ID = R.ToApplicationCIID And ChildApp.IsLatest = 1

       Left Join dbo.vFolderMembers ChildApp_FM on ChildApp_FM.InstanceKey =ChildApp.ModelName

       Left Join dbo.vSMS_Folders ChildApp_F on ChildApp_F.ContainerNodeID = ChildApp_FM.ContainerNodeID

       Left Join fn_ListDeploymentTypeCIs(1033) ChildApp_DT ON ChildApp_DT.AppModelName = ChildApp.ModelName

where ChildApp.DisplayName='Application_Name_in_Arp' and

parentapp.IsLatest='1'

group by parentapp.DisplayName,parentapp.CreatedBy,parentapp.NumberOfDeploymentTypes,parentapp.NumberOfDeployments,

parentapp.NumberOfDevicesWithApp,

parentapp.NumberOfDevicesWithFailure,

parentapp.IsEnabled,

parentapp.IsDeployed,

ParentApp_F.Name,

ParentApp_DT.Technology,

ParentApp.IsSuperseded

Adding Single machine to multiple collection (Direct membership rule to the collection)

# Adding Single machine to multiple collection (Direct membership rule to the collection)

$Sitecode = "SMS"
# add the site code
$PathToSCCMModule = "C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1"
Import-Module $PathToSCCMModule
$Collections= get-content "C:\users\USER_Name\Desktop\Collectionlists.txt"

cd "$Sitecode`:"
Foreach ($Collection in $Collections)

{ "$Collection"; add-CMDeviceCollectionDirectMembershipRule -CollectionName $collection -ResourceId ((Get-CMDevice -name "WINDOWS7TESTMachine").ResourceID) }

#Test machine name WINDOWS7TEST which is being added to the collections list txt file.

Adding multiple machines to single collection (Direct membership collection)

# Adding multiple machines to single collection (Direct membership collection)

$Sitecode = "SMS"
# add the site code 
$PathToSCCMModule = "C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1"
Import-Module $PathToSCCMModule
$Computers = get-content "C:\users\USER_Name\Desktop\Testmachine.txt"

cd "$Sitecode`:"
Foreach ($Computer in $Computers)

{ Add-CMDeviceCollectionDirectMembershipRule -CollectionName "WIndows 7 collection" -ResourceId ((Get-CMDevice -name $Computer).ResourceID) }

#Collection name in yellow WIndows 7 collection

Powershell to get all the application name from SCCM server WMI

Get-WmiObject -ComputerName Primary_server_name -Namespace root\sms\site_Sitecode -Class sms_applicationlatest | select localizeddisplayname



Primary_server_name replace it with primary server name
Sitecode replace it with the Site code Example ABC

SCCM SQL Report to extract list of machines based on ARP entry by its name on specific collection

Select v_R_System.Name0 AS System_Name,

v_Add_Remove_Programs.DisplayName0 AS Display_Name, 

v_Add_Remove_Programs.Publisher0 AS Publisher, 

v_Add_Remove_Programs.Version0 AS Version, 

v_Add_Remove_Programs.prodID0 AS Product_ID, 

CASE

        WHEN b.OSLanguage0 in (1033) THEN 'English'

        WHEN b.OSLanguage0 in (1036) THEN 'French'

        Else 'Unknown'

END as 'OS_language', v_R_System.User_name0 AS User_Name

FROM v_Add_Remove_Programs

JOIN  v_R_System ON v_Add_Remove_Programs.ResourceID = v_R_System.ResourceID

join v_gs_Operating_system b on v_R_System.resourceid=b.resourceid

JOIN v_FullCollectionMembership fcm on v_Add_Remove_Programs.ResourceID=fcm.ResourceID

WHERE  fcm.CollectionID ='CollectionID' and v_Add_Remove_Programs.DisplayName0 like '%ARP Name %'

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