help with a custom query

[rockpapergoat]

I'm trying to pull data from the JSS db to show the total number of instances of a given app within a department. I'd like a simple report, something like the licensed software totals but that includes three columns, possibly like:

Department Application Total
---------------- --------------- -------

Production Office 2008 549

So far, the application inventory search area doesn't provide enough options to group all point versions or include other search criteria, so it's not quite adequate. In this case, the department data is looked up via AD, not entered manually.

Do any of you have queries like this you might share or any pointers to get these results? As I said in my last post, the db itself doesn't appear to use relationships that would make this easier.

Thanks.

[jarednichols]

You may get the best info by running a custom app search, downloading a csv
and then making a pivot table in Excel.

j

[ernstcs]

Welp...

If you don't have department already set in your JSS for systems somehow I'm
not really sure how you're going to separate groups of systems out by
department unless you have other machine criteria to do so, like IP address
or machine name patterns (like we use here).

You most certainly can make an advanced search include all point releases of
a product. So unless I'm totally misunderstanding the question:

- Login
- Inventory Tab -> Advanced Search
- On the General Tab - Name your report to save it later if you like, and check the box below - Select how you want to export it with the View As - Choose your sorting options
- On the Criteria tab - Depending upon how you can tell a system is X dept - By IP go into Computer Info and use the IP Address Option - By Computer name use that option in Computer Info - Or if they are somehow already Smart Grouped use Computer Group - If you want to search for all computers with Word 2008 for example - Hit the + by Software Info - Select Application Title, use LIKE, "Microsoft Word.app" - Select Application Version, use LIKE, "12."
- On the Display Fields tab pick and choose what you want and hit search.

You can access Saved Searches for editing by going to:

Settings -> Inventory Options -> Saved Searches

Hope that helps.

Craig E

[rockpapergoat]

I'd like to avoid using an external app for the final report, considering all the data is in mysql now. The db should be capable of generating this type of report on its own.

Someone at my client already suggested dumping the raw data into Filemaker, which is also a redundant step in my book. Let the db do what it can do.

[henkelb]

Actually they changed this in 7.2. I sent a few minutes earlier today trying to figure out how to edit a saved search.

Now to edit a saved search you just click on the grey arrow next to the search name and choose edit from the options that pop up.

Just thought I would save someone else from scratching their head over this one like I did.

Bill Henkel
Computer Technician
Telephone Support Specialist
henkelb at westerntc.edu
608-789-6254
fax: 608-785-9287

You can access Saved Searches for editing by going to:

Settings -> Inventory Options -> Saved Searches

Hope that helps.

Craig E

[ernstcs]

Cool. I upgrade next Monday over break. Thanks!

Craig Ernst
UW-Eau Claire
(715) 836-3639

Sent from my iPhone