Custom Reports
Hi Guys,
I would like to run a custom report that would gather relevant hardware and software for each node.
So a combination of Computer Export and Software Export. Can anybody advise on how I can combine and customise these reports.
Answers (3)
So I have created the following scripts which appear to work:
SELECT
ID,
NAME,
OS_NAME,
SERVICE_PACK,
OS_VERSION,
OS_BUILD,
CS_MANUFACTURER,
CS_MODEL,
BIOS_SERIAL_NUMBER,
RAM_TOTAL,
RAM_USED,
IP,
MAC
FROM MACHINE
Select
ID,
NAME,
DISK_USED,
DISK_FREE,
DISK_SIZE,
PERCENT_USED
FROM MACHINE_DISKS
Select
ID,
DISPLAY_NAME,
PUBLISHER,
DISPLAY_VERSION
From SOFTWARE
What I need to work out is how to join them together into a neat report.
If you can't produce the report you want using the report wizard, then I would start by setting up MySQL Workbench on your machine so that you can experiment with the SQL code you'll need. Copy the code from one report, say the Computer Export report and start experimenting with the SQL to get it to the place you want. For instance, if you don't want to export all of the same fields, remove the select statements that choose those fields. Then look at the Software Export report and figure out which fields you need to add to the select statement. You'll probably also need to add some table joins to get the proper information.
Feel free to post what SQL you come up with and we can help from there.
The built-in report titled 'Software on Computer' appears to do close to what you want (Lists all software on each computer on your server).
If you wanted more detail on the machine, you would just want to create a report gathering more fields, such as:
SELECT * FROM MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT ON MACHINE_SOFTWARE_JT.MACHINE_ID=MACHINE.ID
LEFT JOIN SOFTWARE ON SOFTWARE.ID=MACHINE_SOFTWARE_JT.SOFTWARE_ID
and include Break on Columns: MACHINE.ID
***Beware that a report with this much detail can take a little while to run, depending on the size of your database.***
Comments:
-
Hi Guys,
Thanks for getting back to me. I managed to get MySQL Workbench running and connected to the DB in read only mode :-)
I have started to play with code, we have lots of information that can be pulled. what I need to work out is the exact information we need, then create a customer script that will extract that data.
What would be helpful is if I can work out how to extract all the relevant header information. for HW and SW. - hiteshapatel 12 years ago -
I have found the relevant headers and tables which I need to extract data from
Table = MACHINE
Headers = ID NAME MAC IP OS_NAME SERVICE_PACK DOMAIN OS_VERSION OS_BUILD RAM_TOTAL RAM_USED CS_MANUFACTURER CS_MODEL BIOS_SERIAL_NUMBER
Table = MACHINE DISK
Headers = ID NAME DISK_USED DISK_FREE DISK_SIZE PERCENT_USED
Table= SOFTWARE
Headers=ID DISPLAY_NAME PUBLISHER DISPLAY_VERSION
The ID's is what I assume would be used to identifiy host to software & disks?
Any idea how I would link all these together in to some logic.
Thanks. - hiteshapatel 12 years ago -
Your "headers" would be the different columns and you would pull from the differnet tables using JOIN statements. Here's a primer that might be helpful:
Primer for Writing SQL Select Statement Queries on the K1000 (w/Custom SQL Report example)
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
John - jverbosk 12 years ago