Forum GLPI-Project

Rabotnik

Member

Registered: 2024-07-09

Posts: 6

MySQL query to GLPI database

Hello,

I am a new GLPI Admin and I need assistance.

I need to perform a MySQL query on the GLPI database to retrieve information about programs installed on computers within a certain period. Specifically, I want to know which programs were installed on computers from July 1st to July 7th, 2024, etc.

Unfortunately, the reports generated through the UI do not meet my needs.

Therefore, I need to create a MySQL query that will be incorporated into a Python script. This script should generate a text file with one entry per line, listing the date, the name of the installed program, and the computer name. This file will then be imported into our SIEM system.

ANY help is greatly appreciated.

Last edited by Rabotnik (2024-07-09 15:52:46)

Rabotnik

Member

Registered: 2024-07-09

Posts: 6

Re: MySQL query to GLPI database

Hello friends. Is it very difficult to solve?

Rabotnik

Member

Registered: 2024-07-09

Posts: 6

Re: MySQL query to GLPI database

Hi!
Maybe you could show me where I can find the most information to get an answer to my question?

joseluis.teixeira

Member

From: PT - GMR

Registered: 2013-05-07

Posts: 51

Re: MySQL query to GLPI database

Something like this?

SELECT  `date_mod`,  `date_creation`, glpi_softwares.* FROM `db_glpi`.`glpi_softwares` 
WHERE `date_creation` >= "2024-07-1" AND `date_creation` <= "2024-07-7"

Rabotnik

Member

Registered: 2024-07-09

Posts: 6

Re: MySQL query to GLPI database

Hello joseluis.teixeira,

Thank you for sharing your initial query. It serves as a great starting point for me.

Currently, I've made some adjustments to your query as follows:

USE glpi;

SELECT date_mod, date_creation, glpi_softwares.*
FROM glpi_softwares
WHERE date_creation >= '2024-07-01' AND date_creation <= '2024-07-22';

However, I'm encountering an issue where I'm not receiving information about the computers on which software was installed. I will share my revised query once it's completed.

Best regards.

Rabotnik

Member

Registered: 2024-07-09

Posts: 6

Re: MySQL query to GLPI database

Hi
The main problem is to find a table that would link glpi_computers and glpi_softwares to form a query describing a computer on which a certain software was installed during the sought period.
Please help me find such a table.

Rabotnik

Member

Registered: 2024-07-09

Posts: 6

Re: MySQL query to GLPI database

The linking table is glpi_items_softwareversions. Here is the query that solves my task:

SELECT 
    glpi_computers.name AS HOSTNAME, 
    glpi_softwares.name AS PROGRAM_NAME, 
    glpi_softwareversions.name AS VERSION, 
    glpi_items_softwareversions.date_install AS INSTALLATION_DATE
FROM 
    glpi_items_softwareversions
LEFT JOIN 
	glpi_computers ON  glpi_items_softwareversions.items_id = glpi_computers.id
LEFT JOIN
	glpi_softwareversions ON glpi_items_softwareversions.softwareversions_id = glpi_softwareversions.id
LEFT JOIN 
	glpi_softwares ON glpi_softwareversions.softwares_id = glpi_softwares.id
WHERE 
 glpi_items_softwareversions.date_install >= '2024-07-01' AND  glpi_items_softwareversions.date_install <= '2024-07-26';