Devices by Boundary and Network Information in SCCM

Ioan Popovici

Published in

MEM.Zone

3 min readMar 13, 2019

Got to have this report for boundaries review :)

🚨🚨 IMPORTANT NOTICE🚨🚨

🦄 We moved to our brand new blog at MEM.Zone 🦄

🚨🚨 IMPORTANT NOTICE🚨🚨

Report release history

This all started with a simple boundary review when I figured It might be handy to have a boundary report. After some research It started to dawn on me that this would not be an easy task.

In the SCCM DB there is no correlation between boundaries and IP’s so there goes the easy way. After a lot of banging my head on the desk this is what I came up with. It’s not pretty but I did my best considering my limited SQL knowledge.

Here goes nothing…

Notes
Three sql user defined functions are needed as a pre-requisite.

Import the SSRS Report

Download the report file

## Device by Boundary and Network Report
SIT Devices by Boundary and Network.rdl

Internet Explorer on and navigate to http://YOUR_REPORT_SERVER_FQDN/Reports
Choose a path and upload the previously downloaded report files.
Replace the DataSource in the reports. You can use just one datasource if your CM and Reporting DBs are on the same server.

Install Support Functions

You can create a new database to host the support function or just add it to the CM database. Fair warning, this counts as ‘modifying the CM database’to Microsoft and they might deny support because of it. The right way to do this is to create a separate database for this purpose.

Create the ‘CM_Tools’ database

Create the ‘CM_Tools’ database by executing the following code in your SQL Management Studio:

/* Create support function database */
CREATE DATABASE CM_TOOLS

Create a new role and give it execute rights.

/* Grant EXECUTE rights */
USE CM_TOOLS
CREATE ROLE dbo_execute
GRANT EXECUTE ON SCHEMA::dbo TO dbo_execute

Add SSRS reporting user to the newly created role. Remember to add your own SSRS service account below.

/* Add user to db_execute role */
EXECUTE sp_addrolemember N'db_execproc', 'Domain\SSRS_Service_User'

If you change the ‘CM_Tools’ name to something else you will need to modify the hardcoded function calls in report ‘DeviceAndBoundaryData’ dataset and ‘ufn_IsIPInSubnet’ support function.

## Modify hardcoded function calls
...
/* Support function */
...CM_TOOLS.dbo.ufn...
...

Create the ufn_CIDRFromIPMask function

Gets the CIDR (‘/’) from a IP Subnet Mask.

Create the ufn_IsIPInRange function

Checks if the IP is in the specified IP range.

Create the ufn_IsIPInSubnet function

Checks if the IP is in the specified subnet using the subnet mask.

You should end up with something like this

Notes
Please read the instructions carefully before asking for help!

Add report view permissions

By default some of the views I’m using for reporting are restricted for reporting purposes. You will need to add reporting access.

GRANT SELECT ON vSMS_Boundary TO smsschm_users;
GRANT SELECT ON vSMS_BoundaryGroup TO smsschm_users;
GRANT SELECT ON vSMS_BoundaryGroupMembers TO smsschm_users;