You are not logged in.

Announcement

 Téléchargez la dernière version stable de GLPI      -     Et vous, que pouvez vous faire pour le projet GLPI ? :  Contribuer
 Download last stable version of GLPI                      -     What can you do for GLPI ? :  Contribute

#1 2008-01-15 19:45:45

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

Offline

#2 2008-01-16 12:31:03

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)

Offline

#3 2008-01-17 15:00:13

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

Offline

Board footer

Powered by FluxBB