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 2021-03-13T09:39:30+05:30 5 Answers 213 views Beginner
0

Answers ( 5 )

  2. Aman Kulshreshtha
    1
    2021-03-14T22:58:38+05:30
    Reply

    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
    • Anoop C Nair
      0
      2021-03-15T11:10:58+05:30
      Reply
      This answer was 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
  4. Anoop C Nair
    -1
    2021-03-13T10:30:52+05:30
    Reply

    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 permission to answer to this question .