I really wanted a new way of displaying outdated computers in MunkiReport (fantastic tool by the way, thanks to the authors/ contributors!) but I was deeply confused by the terminology, and this post is an attempt to write down my needs and prepare for getting it exactly right in future…
What is the goal?
I need to be able to have a report that shows me
– details of each machine under management
– versions of each of my management utilities
So I can detect and fix issues with machines falling behind in updates. It’s really just a spreadsheet that looks something like this- (mock up)
MunkiReport displays information in several different ways- and should be perfect for this
- Modules- which are related bits of info, usually around a single subject like ‘Encryption’ or ‘Drive Size’
- Reports- a grouping of similar widgets
- Listings- sortable, filterable tables containing data about machines
- Dashboards- a customisable single pane of glass view that can often be triggered by a hotkey
Updated- I stole the descriptions of ‘Reports’ and ‘Listings’ from here
There’s a new method of making your own Module, and I went quite a long way into design before realising this was primarily aimed at people who want to store new data in MySQL, for retrieval and display in only a single manner- each module is restricted to displaying data via 3 different built in display types.
At this point I realised that all of the data I wanted was already in the MySQL database, I really just needed a way to display it- also when making a module you can’t use existing data- it assumes you have new fields to add…
A couple of people on the MacAdmins Slack tried to help (thanks Mosen!) but I was still unable to get this done- apparently what I described is a ‘Listing’ but there’s no instructions for creating one, and my efforts failed.
Why not do it the hard way?
I set up the Java connector to Zoho Analytics, imported the data and was able to construct my ‘Listing’ with relative ease. Analytics was totally brilliant in helping me join tables, set defaults and generally hurt the data until it did what I wanted.
However it’s still a dirty solution for the following reasons-
- It’s not live data- has to be imported
- The Java connector is just like every other one in existence- horrific
- Won’t auto launch when you reboot the computer
- If you import too often you use precious API calls- these are limited
- Using a completely different tool when MunkiReport is ideal- if I can learn to use it!
So I’ll put a few details below of how the report is constructed in Zoho Analytics in case it helps me back port it to MR
MySQL Query- this was really easy to construct in Zoho Analytics, would have taken a lot longer without this tool
SELECT "reportdata"."archive_status","Location"."location","machine"."serial_number","reportdata"."long_username","mdm_status"."mdm_enrolled","mdm_status"."mdm_enrolled_via_dep","sophos"."product_version","sophos"."virus_data_version","sophos"."installed","munkireport"."manifestname","munkireportinfo"."version","munki_facts"."fact_value","applications"."version" FROM "reportdata", "Location", "machine", "mdm_status", "sophos" , "munkireport", "munkireportinfo", "munki_facts", "applications" WHERE "machine"."serial_number"="reportdata"."serial_number" AND "machine"."serial_number"="mdm_status"."serial_number" AND"reportdata"."machine_group"="Location"."machine_group" AND"machine"."serial_number"="sophos"."serial_number" AND"machine"."serial_number"="munkireport"."serial_number" AND"machine"."serial_number"="munkireportinfo"."serial_number" AND"machine"."serial_number"="munki_facts"."serial_number" AND"fact_key"='munki_version' AND"machine"."serial_number"="applications"."serial_number" AND"applications"."name"='connectwisecontrol-9caxxxxxxxxxxxx' ORDER BY "machine"."serial_number";
And here is the results- not pretty, but functional (displaying some old data here)
