SQL Query- Which Distribution Points are used by the Clients , during content download . Including other download sources , used for downloading content.

Sharing this query which will provide details of content download history, initiated by different clients and using different method of content download sources . It will also give info on the size of the content downloaded and on the content details too . Feel free to customize according to your Org. need

SELECT
s.Name0 as 'Device Name',ContentID = ContentID,
'Content Source' = HostName,
'Content Source Type' =
CASE 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,
'Start DateTime' = StartTime,
'Bytes Downloaded' = BytesDownloaded
FROM
v_R_System s LEFT OUTER JOIN
v_ClientDownloadHistoryDP_BG cdh ON s.ResourceID = cdh.ClientId
where cdh.StartTime >DATEADD(DAY,-2, getutcdate())


Please Note - I had to use DATEADD , else it will generate a lot of data , during the time of execution . As you can see , I have taken data only for last 2 days and you can modify the number of days , according to your need.

Hope this may help some of our folks , somewhere . Attached a sample screenshot from the Lab above.