Close Menu
    Facebook X (Twitter) Instagram
    Tuesday, May 13
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»SCCM 2007»SCCM report for all advertisment with specific status plus patch compliance status ,other sccm reports

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

    Eswar KonetiBy Eswar KonetiSeptember 22, 7:38 pm5 Mins Read SCCM 2007 888 Views
    Share
    Facebook Twitter LinkedIn Reddit

    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

    Advertisemt status compliance status configmgr custom reports multiple SQL quieries Patch Report RAM changed Reports SCCM SQL SSRS
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    Investigating Co-Management Issues with Windows Endpoints in SCCM/Intune

    October 26, 10:45 pm

    Leave a ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.