Scripting

Hello Ninjas.  I've been trying to make some custom sql statements to generate some reports based on software metering for our K1000.  I'm using MySQL Query Browser to look through the tables to try to find all the relevant data I need, but while I do that I thought I'd post what I'm trying to do here and see if anyone has done something similar.

I've enabled metering for a few applications at our company with the intent of determining what applications are used by our users.  Currently I have several Adobe products metered, as well as office products not included in the suites (visio and project).  We will be moving to the latest releases for these soon and in doing so we are trying to determine if the users actually use the programs at all.  

I can look in the software catalog of the K1000 and check each individual application to see the usage details, but the reports that the wizard creates leave out alot of the good data.  I have since been picking apart the sql from those reports to try to make my own, but I'm having trouble finding what data is in what table, and how to format my sql statement to present the data in the way I want.

I'm very much a beginner at writing sql statements, but I know what data I need and where some of it is.  My end goal is to make a report that shows:

1. The name of each machine that has metered software installed

     2. Under each machine, the name of each metered application (version agnostic)

          3. Under each metered application, the usage statistics (launches, hours used, last used)

That is the first report I would like to create so that we can scrutinize each users application usage and needs.  The second report I would like to create should show:

1. Each metered application without regard to version (I imagine using a regex statement to group software like visio 2007, 2010, and 2013 all in one heading)

     2. For each metered application, which machines have it installed

          3. For each machine, the usage statistics (again hours used, launches, and last used)

So far I've been working in SAM_METER_DATA, MACHINE, and SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE tables primarily to translate software and machine names.  It's mostly the actual metering data and usage statistics that is proving hard to find and link.  I've read several blogs and previous questions that have been invaluable in getting me this far, now it's just a matter of finding relevant data and writing the code.

I'll post what I've got as I get it, but f anyone has any insight to this, or if anyone has made a report similar to this I would love to have some input.  Thanks for reading everyone.