kurtdepre/osdanalytics

This toolset will allow to create "better" reporting for Tasksequences in MEMCM.

OSDAnalytics

DISCLAIMER: These scripts/code/templates serve as an example on how you can implement a OSD reporting solution for ConfigMgr or MDT. These are not production ready and can break stuff, Use at your own Risk.

DISCLAIMER 2: the WebApi as provided in this example is NOT secured and anyone can Start/Complete/Fail a TS if they know the GUID.

1. Introduction

This toolset will allow to create "better" reporting for Tasksequences in MEMCM, the native reports do provide a lot of information, but it can be tough to find trends in there because task sequences & computer resources get deleted and the information get lost.

This toolkit creates a separate DB + tables as well as a webservice (My network admins/DBAs don't like direct writes to the DB and would not allow the trafic), combined with some powershell script to run during the TS to capture information. PowerBi (or other reporting tools) can then be used to analyze the information.

2. Pre-reqs

• A Database host Like SQL Server, SQL Express or Azure SQL.
• A Webserver to host the WebApi like IIS (Probably Azure, Linux or docker containers work as well but just not tested).
• PowerBI Desktop
• Some Basic IIS, MEMCM, SQL & Power BI Knowledge

3. Getting Started

4. Installing the Database

The quickest way to install the DB is to open SQL Management studio, logon to you SQL server and run the CreateDBAndTables.sql script in the SQLScripts folder. Make sure you have Either a SQL login that has write permisions to the freshly created DB or a Windows Login(that will be used to run the WebApi also)

5. Installing the WebApi

1. Copy the content of the Webservice Binaries folder to a location on your webserver.

2. Change the "DBConnection" in the appsettings.json file to point to your environment.

3. Make sure IIS is installed & running.

4. install the ASP.net Core Hosting bunddle

5. Create a new application pool in IIS

6. Create a website in IIS and point it to the folder used in step 1. (Use the application pool create in step 5)

7. Test DB connectivity from webservice

8. Clean the testrecords from the DB (Warning this will delete all data in the DB) using the TruncateAfterTest.sql script found in the SQLScripts folder.

6. Import the template TS or Create your own

TODO: More info to add The next step is to adapt your Tasksequences to include the monitoring steps. you can import the template from the folder Example TS export , to see how to do this. Don't forget to change the URL of the webApi to point to your server.

7. Create Reports

• Import the open the power BI template found in the PowerBi folder, and enter the parameters requested.
• Save your report
• If applicable publish to the power BI service.

8. Cleaning up timed out OSD

TODO: More info to add Sometimes staging never finish (Network issue, hard crash, TS does not start again after reboot). For this we can schedule a script that automatically Fails all OSD sessions running for more then 48 hours. see the script Timeout.ps1 in folder Powershell Scripts, This can be run as sheduled taks to perform this cleanup.

Customizing the data in the dashboard

1. Edit the StartMonitoring script, add a line $object | Add-Member -MemberType NoteProperty -Name -Value '' for each additional property to capture.
2. Open the power BI report -> Transform Data ->Transform Data
3. Click on the little wheel next to source in the applied steps section
4. Add a line 'JSON_VALUE(Data, '$.') AS ,' the below JSON_VALUE(Data, '$.Model') AS Model, in the SQL statement field.
5. Pray to the SQL Gods
6. Apply changes
7. Use your new field in the dashboard.

IIS Pool Settings

If you use Windows auth for your DB, make sure it is running under an account that has access to the database.

Windows Auth

To add more info

Test Connectivity

1. Browse to http(s)://yourserver/
2. Click on the arrow to expand the StartMonitoring section
3. Click on the Try it out button
4. Fill a dummy value in the request field and press execute
5. The response should be a code 200 and contain a guid (unique on each run)
6. Congratz your WebApi is working