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.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

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.

 

Answered 05/16/2012 by: hiteshapatel
Senior White Belt

Please log in to comment
1

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.

 

 

Answered 05/11/2012 by: chucksteel
Red Belt

Please log in to comment
1

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.***

Answered 05/15/2012 by: jaredv
Fourth Degree Green Belt

  • 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.
  • 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.
  • 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
Please log in to comment
Answer this question or Comment on this question for clarity