Grafana Dashboards for SCCM (Part 3)

Updates overview Dashboard

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

Links

SCCM Dashboards with Grafana (Part 1 — Preview, pre-requisites, installation)

SCCM Dashboards with Grafana (Part 2 — Client, distribution and OS info…)

SCCM Dashboards with Grafana (Part 3 — Updates and SCEP Definitions)

This is the tricky part. No, seriously it is, but it’s also the best.

Before we go implementing let’s have an overview of the design around it:

We will run SQL jobs to move data (updates deployed as required or available to collections) to our custom Database named Grafana. The aim is to get data from SCCM on deployment status of all machines (servers/workstations) into our database so we can then run any number of queries through Telegraf scripts on any sub-collection without causing load on SCCM DB.

We will also run some direct queries to get: maintenance windows, definition updates for SCEP into our InfluxDB

Take note that the SQL Jobs can be intensive (based on your environment) so please set their schedule to something with minimal impact (more frequent than 15 mins will not help, go for longer if the load on the server is affected)

Setting all this up will get us close to live data on updates status for our environment and specific collections.

Scripts and Queries are hosted on GitHub:

DarkAllien/SCCM

Set-up steps:

1. On the SCCM Database server run these queries:

SCCM/Grafana/Database_Setup/create_database.sql

2. Modify this section in the dbo.servers.patching….. SQL Job creator queries to match your set-up:

3. Run all dbo.servers.patching…. queries on the SCCM DB server

4. Create the SQL Server Jobs (for ex:)

5. Create the coll_stats.csv file in you Telegraf scripts folder and populate for your needs

ps: The FilterNames in the file are used by the Grafana dashboard, so make sure you have the same ones in both places

6. Add a call to updates.ps1

7. Make sure the account/machine running Telegraf has access to run the queries.

8. Restart the Telegraf service to load the new configuration.

9. Check telegraf.log file for errors.

10. Log into your Grafana instance and import the Pie Chart plugin and the SCCM — Updates Dashboard.