The Request:
Good Day Everyone! I come to you this lovely afternoon with a post on a custom report request that came my way - that oddly enough was nowhere to be found as an offered solution within SCCM or on the wonderful World Wide Web! Last week a customer emailed me asking for a custom report that showed all PXE Enabled Distribution Points within their hierarchy, but also wanted to know the Boundary Group they were a reference in, and the Site Code in which that Boundary Group resided in. Sounded simple enough? Let's give it a go!
Starting Points:
As with any custom report request, I start in the views where I know my information lives. In this case it's within the below snipped views:
What's provided in these views? Let's talk about that.
- View: vSMS_BoundaryGroupSiteSystems - this view provides information on all of the Site Systems within each Boundary Group from your hierarchy. Keep in mind this will include any site system specified in the "References" tab of your boundary group. Naturally this will include more than just a DP in most cases. Keep this thought!
- View: v_DistributionPointInfo - this view provides information on all properites and information about your distribution points. This would be the UI equivalent of Administration -> Distribution Points -> (Choose DP) Properties.
- View: vSMS_BoundaryGroup - this view provides detailed information on boundary group information, such as Name, how many boundaries (members), under-the-hood GroupID, GUID, and more!
I knew with the above 3 views that I had all the information right in front of me, now it was time to start choosing specific information from each. For this I leveraged GroupIDs from vSMS_BoundaryGroupSiteSystems and vSMS_BoundaryGroup. If these two were the same, I knew I could join it as a where statement (which they were). Next I needed the Server/DP Name, and the Primary Site Code. SiteCode came from vSMS_BoundaryGroup, and Server/DP Name (in a readable form) came from v_DistributionPointInfo.
Finally - it's time to join it all together!
Select vSMS_BoundaryGroup.Name [Boundary Group Name], vSMS_BoundaryGroupSiteSystems.SiteCode [Primary Site Code], v_DistributionPointInfo.ServerName [Distribution Point Server Name], v_DistributionPointInfo.IsPXE [PXE Enabled] from vSMS_BoundaryGroup, vSMS_BoundaryGroupSiteSystems, v_DistributionPointInfo
Where v_DistributionPointInfo.NALPath = vSMS_BoundaryGroupSiteSystems.ServerNALPath and vSMS_BoundaryGroup.GroupID = vSMS_BoundaryGroupSiteSystems.GroupID and v_DistributionPointInfo.IsPXE = 1
Order By vSMS_BoundaryGroup.Name asc
There you have it! The results will output the Site Code, Boundary Group Name, Distribution Point Server Name, and PXE information, like below:
Take the above query, move it around, make it look pretty in Report Builder and you now have a satisfied customer and a completed request!
Told you it'd be an easy one!
- Until Later!!!