SQL Queries for finding Peer Cache Content

Starting in ConfigMgr 1806, only machines that are online will be returned as a Peer Cache Source. During the process of finding the content for a peer asking for it, ConfigMgr runs the MP_GetSuperPeerContentLocations stored procedure to find the content. However, that stored procedure is a bit complicated to run just for figuring out where your packages are, so here are few simpler queries.

Query #1 – List which machines that have what content, online or Not

Note: ConfigMgr will return machines that are not online.

SELECT S.Name0, SPCM.ContentID, SPCM.SiteNumber, SPCM.Version, SPCM.Flag FROM SuperPeerContentMap as SPCM
INNER JOIN v_R_System as S ON S.ResourceID = SPCM.ResourceID

Query #2 – List which online machines that have what content

SELECT S.Name0, SPCM.ContentID, SPCM.SiteNumber, SPCM.Version, SPCM.Flag FROM SuperPeerContentMap as SPCM
INNER JOIN v_R_System as S ON S.ResourceID = SPCM.ResourceID
INNER JOIN BGB_ResStatus as BGBRS ON BGBRS.ResourceID = SPCM.ResourceID
WHERE BGBRS.OnlineStatus = '1'
ORDER BY SPCM.ContentID

Query #3 – List which online machines that have what content plus network info

This query also shows the IP Address (ipv4 only), Subnet, and Boundary Group.

SELECT S.Name0, BGBNI.IPAddress, BGBNI.IPSubnet, ContentID, SPCM.SiteNumber, SPCM.Version, SPCM.Flag, BG.GroupID as BoundaryGroupID, BG.Name as BoundaryGroupName FROM SuperPeerContentMap as SPCM
INNER JOIN v_R_System as S ON S.ResourceID = SPCM.ResourceID
INNER JOIN BGB_ResStatus as BGBRS ON BGBRS.ResourceID = SPCM.ResourceID
INNER JOIN BGB_LiveData_Boundary as BGBLB ON BGBLB.ResourceID = SPCM.ResourceID
INNER JOIN BoundaryGroup as BG ON BGBLB.BoundaryGroupID = BG.GroupID
INNER JOIN BGB_NetworkInfo as BGBNI ON BGBNI.ResourceID = SPCM.ResourceID
WHERE BGBRS.OnlineStatus = '1' AND BGBNI.IPAddress NOT LIKE '%:%'
ORDER BY SPCM.ContentID