SCCM report for all advertisment with specific status plus patch compliance status ,other sccm reports

In my daily work routine,i used to work with some of the web report to present it to management as well for troubleshooting purpose.I come across with many reports that are required.some reports that have taken from the internet. Here i am listing all of the reports that are useful and will be posting more and more.

Report for list of machines where static IP exists :

select
CS.Name0,
NAC.IPAddress0
from
dbo.v_GS_COMPUTER_SYSTEM cs
JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC on CS.ResourceId = NAC.ResourceId
Where
NAC.IPAddress0 !=''
and DHCPEnabled0 = 0

Report for list of machines where and When RAM changed ?

select
CS.Name0,
CS.UserName0,
RAM.TotalPhysicalMemory0,
RAM.TimeStamp,
HRAM.TotalPhysicalMemory0,
Max(HRAM.TimeStamp)
from
dbo.v_GS_COMPUTER_SYSTEM CS,
dbo.v_GS_X86_PC_MEMORY RAM,
dbo.v_HS_X86_PC_MEMORY HRAM
Where CS.ResourceID = RAM.ResourceID
and CS.ResourceID = HRAM.ResourceID
and RAM.TotalPhysicalMemory0 != HRAM.TotalPhysicalMemory0
Group by
CS.Name0,
CS.UserName0,
RAM.TotalPhysicalMemory0,
RAM.TimeStamp,
HRAM.TotalPhysicalMemory0

Report for How to find Netbios name if you have GUID:

Select Sys.Name0, Sys.SMSID0, Sys.Domain0, Sys.SystemRole0 from
dbo.v_GS_SYSTEM Sys Where Sys.SMSID0 = @GUID

Prompt for GUID:

select SMS_Unique_Identifier0 from v_R_System

Report for How to monitor Windows Services:

select Distinct
CS.Name0,
SER.Displayname0,
SER.Started0,
SER.StartMode0,
SER.State0
from
dbo.v_GS_COMPUTER_SYSTEM CS,
dbo.v_GS_SERVICE SER,
dbo.v_FullCollectionMembership FCM
where
CS.ResourceId = SER.ResourceID
and CS.ResourceId = FCM.ResourceID
and SER.displayname0 like '%firewall%'
and (SER.State0 != 'Running' or Isnull(SER.State0,'')='')
and FCM.CollectionID = 'SMS00001'

Report for Patch compliance Progress report:

select CS.Name0, CS.UserName0,
case
when (sum(case when UCS.status=2 then 1 else 0 end))>0 then ('Needs '+(cast(sum(case when UCS.status=2 then 1 else 0 end)as varchar(10))+ ' Patches'))

else 'Good Client'

end as 'Status',

ws.lasthwscan as 'Last HW scan',

FCM.collectionID

from

v_UpdateComplianceStatus UCS

left outer join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = UCS.ResourceID

join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID

join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'

left join v_gs_workstation_status ws on ws.resourceid=CS.resourceid

left join v_fullcollectionmembership FCM on FCM.resourceid=CS.resourceid

Where

UCS.Status = '2'

and FCM.collectionid = 'SMS00001'

Group by

CS.Name0,

CS.UserName0,

ws.lasthwscan,

FCM.collectionID

Order by

CS.Name0,

CS.UserName0,

ws.lasthwscan,

FCM.collectionID

Report for List the machines that belongs to which collection:

select a.CollectionId, b.Name from dbo.v_R_System r
join dbo.v_FullCollectionMembership a on R.ResourceID = a.ResourceID
join dbo.v_Collection b on b.CollectionID = a.CollectionID
Where R.Name0 =@machine

Prompt:

select Name0 from v_R_System

Report for Software inventory that is not reported for X (7 days) days:

This report will give you list of machines where the clients are not reported their software inventory data for more than 7 days and also pulls the machine which doesn't report their inventory at all to server(blank)

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 (ResourceId in (select ResourceID from SMS_R_System inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceID = SMS_R_System.ResourceId where DATEDIFF(dd,SMS_G_System_LastSoftwareScan.LastScanDate,GetDate()) > 7) or ResourceId not in (select ResourceID from SMS_G_System_LastSoftwareScan))

Report for All advertisements with specific status :

This report gives you all percentage count with specific status for all advertisement in your environment.In order to get the list of machines where it is advertised with specific status,this report has to be linked to default report i.e 106.It works with SCCM ,not tested in SMS.
SELECT
AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number AS 'Number of clients with this Status',
ROUND(100.0*AdvState.number/SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END),1) AS 'Percent with this Status',
SUM(CASE AdvTotal.LastState WHEN 0 THEN 0 ELSE 1 END) AS 'Total # Clients with Accepted Status'

FROM v_ClientAdvertisementStatus AdvTotal, v_Advertisement AdvName,
(SELECT a.AdvertisementID,a.LastStateName, count(*) as 'number'
FROM v_ClientAdvertisementStatus a, v_AdvertisementStatusInformation b
WHERE a.LastStatusMessageID = b.MessageID
AND b.MessageStateName = @status
GROUP BY a.AdvertisementID, a.LastStateName
) AS AdvState

WHERE AdvState.AdvertisementID = AdvTotal.AdvertisementID
AND AdvState.AdvertisementID = AdvName.AdvertisementID

GROUP BY AdvState.AdvertisementID, AdvName.AdvertisementName, AdvState.LastStateName, AdvState.number
ORDER BY AdvName.AdvertisementName

Prompt:

SELECT DISTINCT
a.MessageStateName

FROM
v_AdvertisementStatusInformation a,
v_ClientAdvertisementStatus b

WHERE a.MessageID = b.LastStatusMessageID
ORDER BY MessageStateName

Report for count of unidentified and uncategorized software's:

SELECT
ISC.NormalizedPublisher,
ISC.NormalizedName,
ISC.NormalizedVersion,
ISC.CategoryName,
count(*) as 'count'
FROM
dbo.v_GS_Installed_Software_Categorized ISC
WHERE
ISC.FamilyName In ('Unidentified','Uncategorized')
group by
ISC.Normaliz
edPublisher,
ISC.NormalizedName,
ISC.NormalizedVersion,
ISC.CategoryName
order by
ISC.NormalizedPublisher,
ISC.NormalizedName,
ISC.NormalizedVersion,
ISC.CategoryName

Report for Recently installed programs:

Gives list of machines where Programs installed on the machines past 2 weeks :

SELECT
CS.Name0,
CS.UserName0,
ISW.ProductName0,
ISW.VersionMajor0,
ISW.VersionMinor0,
ISW.Publisher0,
ISW.RegisteredUser0,
ISW.InstallDate0,
ISW.InstallSource0
FROM
dbo.v_GS_COMPUTER_SYSTEM CS,
dbo.v_GS_INSTALLED_SOFTWARE ISW
WHERE
ISW.ResourceID = CS.ResourceID
ORDER BY
ISW.InstallDate0 DESC,
CS.Name0,
CS.UserName0,
ISW.ProductName0

Report/collection for clients where Group policy not updated for past 1 week:

This gives list of machines where the group policy database file not updated recently.Before creating the web report,software inventory has to be enabled for secedit.sdb file which will be present in %windir%\security\database.

collection:

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_SoftwareFile

on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId

where

SMS_G_System_SoftwareFile.FileName = "secedit.sdb"

and DATEDIFF(dd,SMS_G_System_SoftwareFile.ModifiedDate,GetDate()) > 7

Now you have to figure out why it is not updating

Web Report:

select a.Name0 ,a.User_Name0 ,a.Operating_System_Name_and0
, CONVERT(VARCHAR(12),b.ModifiedDate,107)As "GPO Date Last Applied"
from v_R_System a join v_GS_SoftwareFile b on b.ResourceID=a.ResourceID
where b.FileName='secedit.sdb'
and DATEDIFF(dd,b.ModifiedDate,GetDate()) > 7
order by b.ModifiedDate

Report for list of computers with file size sum(like .jpeg or mp3 etc) :

To get this report,you've actually added *.jpg/mg/avi in software in software inventory client agent ,inventory collection tab.

select SYS.Netbios_Name0,SUM(SF.FileSize)
From v_GS_SoftwareFile SF
join v_R_System SYS on SYS.ResourceID = SF.ResourceID
Where SF.FileName like '%.jpeg%'
group by SF.Filesize,Netbios_Name0
ORDER BY SYS.Netbios_Name0

Report for list all collections and their parent collection:

SELECT
COL.CollectionID,
COL.Name,
COL.Comment,
CTSC.parentCollectionID
FROM
dbo.v_Collection COL,
dbo.v_CollectToSubCollect CTSC
WHERE
CTSC.subCollectionID = COL.CollectionID