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
- API Key from your organization
- Org ID
- PowerShell ISE
- Windows 10 or Server 2016+
- PowerBi Desktop
- Directory path of C:\temp\
- PowerShell API script (GrabAXAPIData.ps1)
- Reports Demo v1 PowerBi template (Automox_Reports_Example_v1_wAPI.pbit from Github repository)
Configuration
- Create a new folder on your C: drive called temp.
- Download and save the PowerShell API script to C:\temp
- Download and save the Automox_Reports_Example_v1_wAPI.pbit to C:\temp
- Open the script to edit and enter your API key & ORG ID, and then save it.
- 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\
- 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.
- Once the data loads and you see the pre-defined reports, save the file as a PowerBi file with the .pbix extension.
- 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.
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.
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.
Policies
Policies gives you a great view of all your policies in the Automox console.
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.
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.
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.
Software Inventory
The Software Inventory report displays all software installed on all your devices.
Health Check
The Health Check displays information about your devices, and can help you troubleshoot connectivity/patching issues.
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:
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.
- Download the Scheduled Task template XML from AX_ScheduledTask_PowerBI_Dashboard_Data.xml Github repository.
- Open Task Scheduler on your host server.
- Right click and select Import Task.
- Select and import the XML File.
- Change the security principle that the API script should run as (Example: SYSTEM or a privileged domain user.
- 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.