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.
Aman Kulshreshtha 
Leave an answer
Sorry, you do not have permission to answer to this question .
Answers ( 5 )
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
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
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
it is client’s demand. I can verify it by seeing the logs.
Can we get a report by quering the state messages or something.
Thanks,
Aman
Ok got you. Can you check the software update default reports
https://www.anoopcnair.com/configmgr-default-reports-software-updates-sccm/