Safari Version Report

[reidg]

I need to generate a report or Inventory Search that lists all computers and the version of Safari they are running.

I realize I can create an Extension Attribute for Safari Version, but since that information is already in the database, it seems redundant to have to add the Extension Attribute.

Any ideas on how to get Safari Version into a report?

Thanks,
Reid

[millersc · Accepted solution]

Computers -> Search Inventory (left panel) -> Search (drop down to Applications) -> Safari (no version)

This work for you?

[millersc · Accepted solution]

Computers -> Search Inventory (left panel) -> Search (drop down to Applications) -> Safari (no version)

This work for you?

[reidg]

That worked. I needed to enter the Application as Safari.app. Just "Safari" didn't return any results.

Thanks for you help.

[millersc]

@reidg you can adjust your Search settings. Click on your user name (right side), Preferences -> Search Preferences. If you change settings to "contains" its more broad searching, than "exact" or "starts with". I find it much easier to search this way then filter deeper if I need to.

[reidg]

Good tip. Thanks.

[reidg · Accepted solution]

I ended up writing a SQL query that would export all computers and the version of Safari they are running in addition to some other relevant info such as last check in.

This allows me to do some customized reporting and data manipulation in Excel.

USE jamfsoftware;
SELECT applications.application_name as App,
    applications.application_version as App_Version,
    computers_denormalized.computer_name as Sharing_Name, 
    computers.logged_in_user as LoggedIn_User,
    computers_denormalized.username as Enrollment_UserName,
    computers_denormalized.model as Model,
    computers_denormalized.operating_system_version as OS_Version,
    DATE_FORMAT(FROM_UNIXTIME(computers_denormalized.last_report_date_epoch/1000), '%m/%d/%y %h:%i%p') as Last_Report
FROM computers_denormalized LEFT JOIN (applications, computers) 
ON (computers_denormalized.last_report_id = applications.report_id AND computers_denormalized.computer_id=computers.computer_id)
WHERE applications.application_name LIKE 'Safari%'
ORDER BY App_Version;

[reidg]

BTW, the above query can be used for any application by changing Safari% to the desired app name such as 'iBooks Author%'.

[Quan_nong]

Hi @reidg How would I use your SQL query to get the Safari versions and computer?

[reidg]

@Quan.nong , the easiest way would be to use MySQL Workbench to connect to the database and run the query. You can download it for different operating systems here: https://dev.mysql.com/downloads/workbench/

[Quan_nong]

@reidg Is the achievable if JSS is a cloud instance rather then local and running its own Mysql

[mm2270]

@Quan.nong No, you have no access to the MySQL db when running a cloud instance. And doing it via an sql query isn't completely necessary to get this info.

[Quan_nong]

@mm2270 Do you have any advice to best achieve this?

[mm2270]

@Quan.nong If you want a report showing the version of Safari for all your Macs, create a new advanced search. You can optionally save it with a name if you plan on using it again.
The only criteria required would be Application Title | Has | "Safari.app" Feel free to add in additional criteria, like Last Check-in or whatever if you want to narrow the scope.
Under the Display tab, enable whatever columns you might want in the report, including any EA values.
Run the report, which, if you just put in Safari and nothing else, in theory should bring up every Mac under management, since they should all have Safari.
Now export the report, choose .csv, and on the final screen when asked "Choose the inventory item on which to base your results" or something to that effect, click the "Applications" button.
When you export it, it will add 2 additional columns to the front of your report, with Application Title and Application Version. The machines will get grouped with like versions in sections. It's hard to explain exactly, but give it a try and you'll see what I mean.

Some cleanup and manipulation will likely be needed to make it into something more useful, but it should give you a good starting point to jump from.

[maristchris]

Has anyone used this process? I am getting a file called "58 computers..." that indicates that there should be data but all i get is a header...

To clarify, I am using the native export function, not the SQL command.

[reidg]

@maristchris I ran through Mike's suggestion and the export has data. Are you seeing data in any of your other Advanced Searches if you export them?

[rvera]

@reidg thanks for the query, I updated it for a more recent version of jamf

SELECT application_details.name as App_Name,
    application_details.version as App_Version,
    computers_denormalized.computer_name as Sharing_Name, 
    computers.logged_in_user as LoggedIn_User,
    computers_denormalized.operating_system_version as OS_Version,
    DATE_FORMAT(FROM_UNIXTIME(computers_denormalized.last_report_date_epoch/1000), '%m/%d/%y %h:%i%p') as Last_Report
FROM computers_denormalized JOIN (applications, computers)
ON (computers_denormalized.last_report_id = applications.report_id AND computers_denormalized.computer_id=computers.computer_id )
JOIN application_details ON applications.application_details_id = application_details.id
WHERE application_details.name LIKE 'Safari%'
ORDER BY App_Version;