Safari Version Report

reidg
New Contributor III

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

2 ACCEPTED SOLUTIONS

millersc
Valued Contributor

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

This work for you?

View solution in original post

reidg
New Contributor III

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;

View solution in original post

15 REPLIES 15

millersc
Valued Contributor

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

This work for you?

reidg
New Contributor III

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

Thanks for you help.

millersc
Valued Contributor

@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
New Contributor III

Good tip. Thanks.

reidg
New Contributor III

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
New Contributor III

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

Quan_nong
Contributor

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

reidg
New Contributor III

@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
Contributor

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

mm2270
Legendary Contributor III

@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
Contributor

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

mm2270
Legendary Contributor III

@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
New Contributor III

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
New Contributor III

@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
New Contributor II

@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;