Published
Sep 08 2018 06:33 AM
10.4K
Views
First published on CloudBlogs on May 04, 2015
System Center Configuration Manager and Microsoft Intune allow IT Pros to manage PCs and mobile devices, keep software up-to-date, set configuration and security policies, and monitor system status. The SCCM database and reports hold a wealth of information about the state of these devices – including hardware inventory, compliance states, software update levels, and information about malware detections. In this post I am excited to announce a new way to get even better insights into your SCCM data using Microsoft Power BI .
This view is only the beginning, however – there are even more insights behind each tile. By clicking on the “Detected Malware Report” we can see details on the types and severity of malware detected in the environment (see next image).
Selecting a value on the report will cross filter the other visuals. For example, selecting a malware classification category like “Trojan Downloader” (on the bottom right), will filter all the charts on the page. This allows you to quickly see when and where this particular class of malware was detected.
The level of customization available is really impressive. For example, you can:
Another customization example: Instead of a bar chart for the data above, we could choose to show it as a tree map in order to easily see the relative prevalence of each malware category detected in the environment.
More details about interacting with reports are available
here
.
Finally, the
Q&A
feature can be used to ask specific questions about your SCCM data. For example, you can ask questions such as, “Show count of non-compliant mobile devices.”
Next, open the “Power Query” pane by selecting the “Show Pane” option on the “POWER QUERY” menu.
Note: If you do not see this option you may need to download and install
Microsoft Power Query for Excel
and then reload your workbook.
Finally, select the “Configuration Manager” group and click “Refresh,”
The first time you refresh the queries, Excel will prompt you to review each of the queries and optionally enter credentials. Depending on your environment, you may need to also edit the Data Source Setting and change the default “Encryption” setting. Once the data has been imported, you will need to save the workbook.
For an optimal experience, it is best to save your workbook to your OneDrive for Business folder so that any changes you make can be refreshed in Power BI.
Now, navigate to
www.PowerBI.com
and sign in with your work account. If you don't already have an account, it's quick and free to sign up!
Once in Power BI, you can load your SCCM data through the “Get Data” link in the top left corner.
Select the workbook you imported to your SCCM data into and click “Connect.”
The workbook contains a set of pre-built queries and reports that can be pinned to the dashboard – but you can always add additional queries using Power Query, and you can create additional reports in the Power BI designer.
System Center Configuration Manager and Microsoft Intune allow IT Pros to manage PCs and mobile devices, keep software up-to-date, set configuration and security policies, and monitor system status. The SCCM database and reports hold a wealth of information about the state of these devices – including hardware inventory, compliance states, software update levels, and information about malware detections. In this post I am excited to announce a new way to get even better insights into your SCCM data using Microsoft Power BI .
Exploring your System Center Configuration Manager data
SCCM data can be made available in Power BI using an Excel workbook (with the steps described here ). As soon as you connect your data, you’ll have a wealth of information at your fingertips. For example, now you can quickly answer critical questions like, “Who in my environment are at risk for security exploits?” and many others. Here’s an example report that we created which starts with compliance information for mobile devices managed by Intune, software updates managed with SCCM, and malware detected with System Center Endpoint Protection.
This view is only the beginning, however – there are even more insights behind each tile. By clicking on the “Detected Malware Report” we can see details on the types and severity of malware detected in the environment (see next image).
Selecting a value on the report will cross filter the other visuals. For example, selecting a malware classification category like “Trojan Downloader” (on the bottom right), will filter all the charts on the page. This allows you to quickly see when and where this particular class of malware was detected.
The level of customization available is really impressive. For example, you can:
- Edit the report and change the visual type.
- Pin a view to your dashboard
- Remove it from the report
- And a lot more.
Another customization example: Instead of a bar chart for the data above, we could choose to show it as a tree map in order to easily see the relative prevalence of each malware category detected in the environment.
More details about interacting with reports are available
here
.
Finally, the
Q&A
feature can be used to ask specific questions about your SCCM data. For example, you can ask questions such as, “Show count of non-compliant mobile devices.”
Connecting to System Center Configuration Manager
To connect SCCM data with Power BI, you’ll need to first download the “Getting Started” workbook and open it on a machine in your environment that has read access to your SCCM database. The spreadsheet includes all of the queries and views you will need to get started – now all you need to do is setup the data source. To do this, update the SQL Server FQDN and Database Name values in the workbook to point to your SCCM site database.
Next, open the “Power Query” pane by selecting the “Show Pane” option on the “POWER QUERY” menu.
Note: If you do not see this option you may need to download and install
Microsoft Power Query for Excel
and then reload your workbook.
Finally, select the “Configuration Manager” group and click “Refresh,”
The first time you refresh the queries, Excel will prompt you to review each of the queries and optionally enter credentials. Depending on your environment, you may need to also edit the Data Source Setting and change the default “Encryption” setting. Once the data has been imported, you will need to save the workbook.
For an optimal experience, it is best to save your workbook to your OneDrive for Business folder so that any changes you make can be refreshed in Power BI.
Now, navigate to
www.PowerBI.com
and sign in with your work account. If you don't already have an account, it's quick and free to sign up!
Once in Power BI, you can load your SCCM data through the “Get Data” link in the top left corner.
Select the workbook you imported to your SCCM data into and click “Connect.”
The workbook contains a set of pre-built queries and reports that can be pinned to the dashboard – but you can always add additional queries using Power Query, and you can create additional reports in the Power BI designer.
* * *
In conclusion: We all really hope this helps kick-start your analysis of System Center Configuration Manager data. We are always interested in hearing your feedback. Please use http://support.powerbi.com to let the team know about your experience – and tell them what we can do better!
1 Comment