Custom API Dashboard through PowerBI

Here we describe how you can leverage PowerBI and PowerShell to get your data and present it in a familiar way that everyone can understand.  

Using the Automox API and some of the many already available API examples found on the Automox community site, you can pull your Automox data into PowerBI and display this into a report model.  

You can find more API scripts and tricks in our Automox Developer Portal & API Guide

Disclaimer

  • This isn't a replacement for Automox reporting but an example of using a business intelligence tool with the Automox API.

Instructions

Prerequisites

Configuration

  1. Create a new folder on your C: drive called temp.
  2. Download and save the PowerShell API script to C:\temp
  3. Download and save the Automox_Reports_Example_v1_wAPI.pbit to C:\temp
  4. Open the script to edit and enter your API key & ORG ID, and then save it.

    PowerBI_10.png


  5. Click the Run script button to execute the script.  This will generate the required files for the PowerBi reports.  The data files are saved in C:\temp\
    PowerBI_11.png

  6. Double click to open Automox_Reports_Example_v1_wAPI.pbit from C:\temp\. The template will open and load the data files from the previous step. 
    PowerBI_12.png

  7. Once the data loads and you see the pre-defined reports, save the file as a PowerBi file with the .pbix extension.
  8. When you want to refresh your report data, just execute the PowerShell script, then click the refresh button in PowerBi.

Reporting Examples

Organization Overview

The Organization Overview displays information about all your devices in the Automox console. 

PowerBI_1.png

One glance, and you can see device agent versions, devices online, devices needing a reboot, devices by operating system, and more. 

 

Device Details

Device Details displays essential information about all your devices.

PowerBI_2.png

With a few clicks, you can sort a column, filter, and export your data.

 

Groups, Policies, and Devices

Groups, Policies, and Devices show the relationship between each.

PowerBI_3.png

 

Policies

Policies gives you a great view of all your policies in the Automox console.

PowerBI_4.png

Click on a column to sort, or use a filter to focus on a specific policy.

Policy Stats

Policy Stats gives you a great view of your policy compliance in the Automox console.

PowerBI_5.png

Click on a column to sort, or use a filter to focus on a specific policy.

 

Pre-Patch Report

The Pre-Patch Report displays a list of all the scheduled patches per device, along with severity and exposure information, allowing you to effectively assess internal impact.

PowerBI_6.png

Click on a column to sort or use a filter to focus on a specific device or patch name.

 

Patches Waiting

The Patches Waiting Report displays packages available to install in your organization.

PowerBI_7.png

 

Software Inventory

The Software Inventory report displays all software installed on all your devices.

PowerBI_8.png

Health Check

The Health Check displays information about your devices, and can help you troubleshoot connectivity/patching issues. 

PowerBI_9.png

Health Check - Column Definitions

Column

Definition

Agent Version

Shows what version of the amagent is installed

PS version

PowerShell version on the device

Auto Update Option

Provides the Automatic Update options last collected from each agent during the last scan before environment data was collected. The settings can be set in different ways:

  1. By the OS Patch Management settings in the Automox group the agent belongs to.
  2. If the OS Patch Management settings in the group is set to keep settings, it will be set locally, by GPO, or if there is another management system configuring patch processes

    Our best practice is to only use one method to manage the patch management settings. As an example, if you want Automox to manage the settings, set the "Windows and macOS Patch Management" setting to "Disable OS Automatic Updates", and set the "Windows Update Source" to either "Windows Update" or "WSUS", depending on where you want the devices of that group to search. 

Disable other patch configuration settings (example, if you use SCCM/Jamf, stop setting patch configurations via agent settings. If you set Windows Update settings with GPO, remove those settings or set to unconfigured in GPO. The important point is to use only one source to configure these settings. 

{"ENABLED": "0", "OPTIONS": "off"} for Windows, and {"ENABLED": "0", "OPTIONS": ""} for macOS are the preferred settings. This means that the device will not automatically patch itself, and the Automox policy will define what patches to download, and the schedule for installation. 

WSUS Config

WSUS settings defined on the client. WSUS_Server is blank if using Windows Updates as the source, and will list the WSUS server if WSUS is the source. WSUS_Managed, 0 = not WSUS managed, and 1 = WSUS managed. REACHABLE is only relevant if WSUS managed, 0 = Not reachable, 1= Reachable. 

WSUS Update Source

Lists if there was an error connecting to the update source, and if it was connected. This will present the devices that failed the update source check visible in the console under compatibility tests. 

WMI Check

Windows only, check for WMI corruption

Need Reboot

Device needs a reboot

Last Disconnect time

Lists when a device last disconnected from Automox. if it is empty, it is active. You can use this to identify devices that have not checked recently. 

Last Refresh Time

Lists the last time a scan was performed. If the date is missing or is very old, the device has either been offline for a substantial period, or the agent is no longer working correctly. 

Note: if the device is active and not on agent 40 or higher, please try running the Catalog Worklet named Install Latest Agent On Systems With Old Agents.

Pending Patches

Number of patches needed based on policy at last successful scan. Can be compared to Column (patches) for analysis if what is available vs targeted to install.

OS Version

Review for Automox supported OS versions

Free (GB)

Free disk space

 

Scheduled Task

Optional: A Scheduled Task can be configured to regularly run the API script and update the PowerBI data files.

  1. Download the Scheduled Task template XML from AX_ScheduledTask_PowerBI_Dashboard_Data.xml Github repository.
  2. Open Task Scheduler on your host server.
  3. Right click and select Import Task.
  4. Select and import the XML File.
  5. Change the security principle that the API script should run as (Example: SYSTEM or a privileged domain user.
  6. Define when the scheduled task should run. By default it is scheduled to run three times a day at 6am, 12pm, and 6pm.

Considerations

If the name of or path to the API script changes, you will need to update this by going to Actions → Edit, and then modifying the file name or path after -File in the Add Arguments section.

Was this article helpful?
0 out of 0 found this helpful