!Update 2022-03-08! Fixed an issue with the Power BI template where the following SQL error may be returned for large boundary group strings: STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.
I was checking out some reports recently for client data sources (DP, BranchCache etc) for MEMCM content downloads and I realised that the total volume of data was being calculated incorrectly for devices that are members of more than one boundary group. Even the Client Data Sources dashboard in the MEMCM console has this issue when not selecting the ‘In a single boundary group’ option.
To illustrate, in the SQL query results below, you can see download jobs being logged more than once for a single client because they are logged for each boundary group that client is member of. You can see that the ClientId, ID (download job ID), ContentID, BytesDownloaded and HostName are duplicated – only the BoundaryGroup ID is different.
This means that if you are not explicitly selecting an individual boundary group the same download job will be included in the total download value more than once, resulting in double (or more) the amount of download data being reported!
If you are following Microsoft’s recommendations for boundary groups, you will have separate boundary groups for site assignment and each client will therefore likely be a member of more than one boundary group.
I created my own report that works a little differently in order to display more accurate total values where clients are members of multiple boundary groups. In this report, boundary groups are aggregated into a single, comma-separated value so that each download job is reported only once and all the boundary groups the client is member of are displayed in a single field.
This does mean that if your clients are members of more than one BG you may not be able to filter by an individual BG, but by the different combinations that may include that group.
You can download the Power BI template here. I haven’t tested it in multiple environments so feel free to feedback any issues.
You’ll be prompted for your SQL Server name and CM database name:
The query may be a little expensive in an environment with a large amount of data, so don’t worry if you see this for a while:
Please note: because of the use of the STRING_AGG function, your SQL server must be on 2017 or later to use the report!
If you’re not already using it, I also highly recommend the Client Data Source dashboard available from System Center Dudes.
