Forum GLPI-Project

slfichtner

Member

Registered: 2007-08-13

Posts: 8

Report or SQL query to dump software for each computer

Hello All:

I have a very strong need to create a report or Access report from GLPI to create a listing of computers with OS and Model, etc. and list out all the software installed on each computer.  I have tried and tried but the report never lists the software properly.  I have the MySql ODBC connection working and can import the tables just fine into MS Access and iReports.

An example report would be:

Computer   OS                   Model       Software Installed   Version
Computer1  Windows XP     Dell 270    WinZip                    9.0
                                                        Firefox                    2.0.0.11
                                                        VMware Player         2.02
...
Computer2  Windows 2000  Dell 260    WinZip                    8.1
                                                        Firefox                    2.0.0.9
                                                        VMware Player         2.01
...

What would an SQL statement look like?  What tables are used and how do they relate?

Thanks for any help.

Steve

petdoctors

Member

Registered: 2006-10-09

Posts: 21

Re: Report or SQL query to dump software for each computer

Hi Steve,

Not a complete answer, but here's two queries (tested with 0.68) that dump out the software and computers. These have been taken from the reports I setup in iReport - I hope to document some of this in the wiki when time permits. iReport is Open Source and definitiely worth a look. It will output to text files, spreadsheets, PDF etc.

The line referring to deleted stuff has been removed because it worked in 0.68 but not in 0.7 so I presume its function as changed but I've not checked yet.

Software:

select glpi_software.ID as id,
       glpi_dropdown_locations.name as location,
       glpi_software.name as itemname,
       glpi_enterprises.name as manuf,
       glpi_software.notes as notes
from glpi_software left JOIN glpi_dropdown_locations on (glpi_software.location = glpi_dropdown_locations.ID)
left join glpi_enterprises on (glpi_software.FK_glpi_enterprise = glpi_enterprises.id)
/* where glpi_software.deleted <> 'Y' */
order by location

Computers:

select glpi_computers.ID, glpi_dropdown_locations.name as location, glpi_computers.name, glpi_computers.serial, glpi_enterprises.name as manuf, glpi_computers.notes
from glpi_computers left JOIN glpi_dropdown_locations on (glpi_computers.location = glpi_dropdown_locations.ID)
left join glpi_enterprises on (glpi_computers.FK_glpi_enterprise = glpi_enterprises.id)
/*where glpi_computers.deleted <> 'Y' */
order by location

Nigel

Last edited by petdoctors (2008-01-16 12:35:00)

slfichtner

Member

Registered: 2007-08-13

Posts: 8

Re: Report or SQL query to dump software for each computer

Nigel,

Thank you for the reply.  I have iReports loaded and have been playing with it.  But I don't really understand it.

I'll look at the queries and see what I can figure out.  I'll keep an eye on the Wiki too.

Steve