SCCM Report to get the download details for patches

Question

Hi All,

I would like to create a report where we can see the download source of patches on internet clients when connected over CMG.

solved 1
Aman Kulshreshtha 5 months 2021-03-13T09:39:30+05:30 5 Answers 49 views Beginner 0

Answers ( 5 )

    1
    2021-03-14T22:58:38+05:30

    Thanks Anoop for the suggestion but I did not find what is required.

    Below is something I try to figure out and I can see the download location for the updates.

    SELECT
    ResourceID = s.ResourceID,
    ID = his.ID,
    ClientId = his.ClientId,
    ‘Start DateTime’ = StartTime,
    ‘Start Date’ = StartTime,
    ‘Bytes Downloaded’ = BytesDownloaded,
    ContentID = ContentID,
    ‘Distribution Point Type’ = DistributionPointType,
    ‘Download Type’ = src.DownloadType,
    ‘Host Name’ = src.HostName,
    ‘BoundaryGroupID’ = CASE WHEN cbg.BoundaryGroup IS NULL THEN 10000000 ELSE cbg.BoundaryGroup END,
    c.PkgID,
    c.Content_ID,
    c.Content_UniqueID,
    Content_XREF = CASE WHEN c.PkgID IS NULL THEN src.ContentID ELSE c.pkgid + ‘ | ‘ + src.ContentID END,
    ‘Content Source Type’ =
    CASE src.DistributionPointType
    WHEN 1 THEN ‘Cloud Distribution Point’
    WHEN 2 THEN ‘Management Point’
    WHEN 3 THEN ‘Peer Cache’
    WHEN 4 THEN ‘Distribution Point’
    WHEN 5 THEN ‘BranchCache’
    WHEN 6 THEN ‘DO Peer (Delivery Optimization)’
    WHEN 7 THEN ‘DO Server (Delivery Optimization)’
    WHEN 8 THEN ‘Microsoft Update’
    ELSE ‘Unknown’
    END,
    BGServerUniqueKey = CONVERT(NVARCHAR(10), CASE WHEN cbg.BoundaryGroup IS NULL THEN 10000000 ELSE cbg.BoundaryGroup END) + ‘ | ‘ + src.HostName

    FROM
    v_R_System s LEFT OUTER JOIN
    ClientDownloadHistory his ON s.ResourceID = his.ClientId LEFT OUTER JOIN
    ClientDownloadHistorySources src on src.DownloadHistoryID=his.ID LEFT OUTER JOIN
    ClientDownloadHistoryBoundaryGroups cbg on cbg.DownloadHistoryID=his.ID LEFT OUTER JOIN
    v_Content c ON src.ContentID = c.Content_UniqueID
    UNION
    SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’Cloud Distribution Point’,NULL UNION
    SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’Management Point’,NULL UNION
    SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’Peer Cache’,NULL UNION
    SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’Distribution Point’,NULL UNION
    SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’BranchCache’,NULL UNION
    SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’DO Peer (Delivery Optimization)’,NULL UNION
    SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’DO Server (Delivery Optimization)’,NULL UNION
    SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’Microsoft Update’,NULL UNION
    SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,’Unknown’,NULL

    Can you if this is accurate

    Best answer
    • This answer is edited.

      I tried this query and it’s a very complex one … It worked for me after some tweaks .. It works. But not sure whether this helps to get details that your client required

      SELECT
      'ResourceID' = ResourceID,
      'ID' = his.ID,
      'ClientId' = his.ClientId,
      'Start DateTime' = StartTime,
      'Start Date' = StartTime,
      'Bytes Downloaded' = BytesDownloaded,
      'ContentID' = ContentID,
      'Distribution Point Type' = DistributionPointType,
      'Download Type' = src.DownloadType,
      'Host Name' = src.HostName,
      'BoundaryGroupID' = CASE WHEN cbg.BoundaryGroup IS NULL THEN 10000000 ELSE cbg.BoundaryGroup END,
      c.PkgID,
      c.Content_ID,
      c.Content_UniqueID,
      Content_XREF = CASE WHEN c.PkgID IS NULL THEN src.ContentID ELSE c.pkgid + ' | ' + src.ContentID END,
      'Content Source Type' = CASE src.DistributionPointType
      WHEN 1 THEN 'Cloud Distribution Point'
      WHEN 2 THEN 'Management Point'
      WHEN 3 THEN 'Peer Cache'
      WHEN 4 THEN 'Distribution Point'
      WHEN 5 THEN 'BranchCache'
      WHEN 6 THEN 'DO Peer (Delivery Optimization)'
      WHEN 7 THEN 'DO Server (Delivery Optimization)'
      WHEN 8 THEN 'Microsoft Update'
      ELSE 'Unknown'
      END,
      BGServerUniqueKey = CONVERT(NVARCHAR(10), CASE WHEN cbg.BoundaryGroup IS NULL THEN 10000000 ELSE cbg.BoundaryGroup END) + ' | ' + src.HostName
      
      FROM v_R_System LEFT OUTER Join
      ClientDownloadHistory his ON ResourceID = his.ClientId LEFT OUTER JOIN
      ClientDownloadHistorySources src on src.DownloadHistoryID=his.ID LEFT OUTER JOIN
      ClientDownloadHistoryBoundaryGroups cbg on cbg.DownloadHistoryID=his.ID LEFT OUTER JOIN
      v_Content c ON src.ContentID = c.Content_UniqueID
      UNION
      SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL, 'Cloud Distribution Point',NULL UNION
      SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Management Point',NULL UNION
      SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Peer Cache',NULL UNION
      SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Distribution Point',NULL UNION
      SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'BranchCache',NULL UNION
      SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,6,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'DO Peer (Delivery Optimization)',NULL UNION
      SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,7,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'DO Server (Delivery Optimization)',NULL UNION
      SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Microsoft Update',NULL UNION
      SELECT NULL,NULL,NULL,GetDate(),GetDate(),NULL,NULL,100,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Unknown',NULL
    -1
    2021-03-13T10:30:52+05:30

    Hello – I think you are better of creating a cmpivot query to check and find out the details from the log files.

    First of all you need to find out the download details from the logs and search that logs to understand.

    Sample query and post details below

    https://www.anoopcnair.com/analyze-sccm-client-logs-using-cmpivot-configmgr/

    ccmlog (‘UpdatesDeployment’) | where (LogText like ‘%0x87d00215%’) | distinct Device

Leave an answer

Sorry, you do not have a permission to answer to this question .