Discussion :

[Apo76]

Bonjour a tous !
Je suis actuellement en train de créer des rapports pour glpi mais j'ai un problème.

Je souhaiterais récupérer tous les PC de la base et afficher :
nom
numéro de série
antivirus
version de l'antivirus

mais, si un pc n'a pas d'antivirus, je veux quand même l'afficher dans le résultat, que les sysadmin puissent le voir.

la base de données est constituée de 151 tables ( que je vais vous épargner car seulement 4~5 tables sont nécessaires à ma requête )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
--
-- Structure de la table `glpi_computers`
--
 
CREATE TABLE IF NOT EXISTS `glpi_computers` (
  `ID` int(11) NOT NULL auto_increment,
  `FK_entities` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_entities (ID)',
  `name` varchar(255) collate utf8_unicode_ci default NULL,
  `serial` varchar(255) collate utf8_unicode_ci default NULL,
  `otherserial` varchar(255) collate utf8_unicode_ci default NULL,
  `contact` varchar(255) collate utf8_unicode_ci default NULL,
  `contact_num` varchar(255) collate utf8_unicode_ci default NULL,
  `tech_num` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_users (ID)',
  `comments` text collate utf8_unicode_ci,
  `date_mod` datetime default NULL,
  `os` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_os (ID)',
  `os_version` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_os_version (ID)',
  `os_sp` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_os_sp (ID)',
  `os_license_number` varchar(255) collate utf8_unicode_ci default NULL,
  `os_license_id` varchar(255) collate utf8_unicode_ci default NULL,
  `auto_update` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_auto_update (ID)',
  `location` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_locations (ID)',
  `domain` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_domain (ID)',
  `network` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_network (ID)',
  `model` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_model (ID)',
  `type` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_type_computers (ID)',
  `is_template` smallint(6) NOT NULL default '0',
  `tplname` varchar(255) collate utf8_unicode_ci default NULL,
  `FK_glpi_enterprise` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_manufacturer (ID)',
  `deleted` smallint(6) NOT NULL default '0',
  `notes` longtext collate utf8_unicode_ci,
  `ocs_import` smallint(6) NOT NULL default '0',
  `FK_users` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_users (ID)',
  `FK_groups` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_groups (ID)',
  `state` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_state (ID)',
  `ticket_tco` decimal(20,4) default '0.0000',
  PRIMARY KEY  (`ID`),
  KEY `location` (`location`),
  KEY `os` (`os`),
  KEY `FK_glpi_enterprise` (`FK_glpi_enterprise`),
  KEY `date_mod` (`date_mod`),
  KEY `tech_num` (`tech_num`),
  KEY `name` (`name`),
  KEY `type` (`type`),
  KEY `model` (`model`),
  KEY `FK_groups` (`FK_groups`),
  KEY `FK_users` (`FK_users`),
  KEY `os_sp` (`os_sp`),
  KEY `os_version` (`os_version`),
  KEY `network` (`network`),
  KEY `domain` (`domain`),
  KEY `auto_update` (`auto_update`),
  KEY `ocs_import` (`ocs_import`),
  KEY `FK_entities` (`FK_entities`),
  KEY `is_template` (`is_template`),
  KEY `deleted` (`deleted`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=333 ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `glpi_dropdown_software_category`
--
 
CREATE TABLE IF NOT EXISTS `glpi_dropdown_software_category` (
  `ID` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate utf8_unicode_ci default NULL,
  `comments` text collate utf8_unicode_ci,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `glpi_software`
--
 
CREATE TABLE IF NOT EXISTS `glpi_software` (
  `ID` int(11) NOT NULL auto_increment,
  `FK_entities` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_entities (ID)',
  `recursive` tinyint(1) NOT NULL default '0',
  `name` varchar(255) collate utf8_unicode_ci default NULL,
  `comments` text collate utf8_unicode_ci,
  `location` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_locations (ID)',
  `tech_num` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_users (ID)',
  `platform` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_os (ID)',
  `is_update` smallint(6) NOT NULL default '0',
  `update_software` int(11) NOT NULL default '-1' COMMENT 'RELATION to glpi_software (ID)',
  `FK_glpi_enterprise` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_manufacturer (ID)',
  `deleted` smallint(6) NOT NULL default '0',
  `is_template` smallint(6) NOT NULL default '0',
  `tplname` varchar(255) collate utf8_unicode_ci default NULL,
  `date_mod` datetime default NULL,
  `notes` longtext collate utf8_unicode_ci,
  `FK_users` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_users (ID)',
  `FK_groups` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_groups (ID)',
  `oldstate` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_state (ID)',
  `ticket_tco` decimal(20,4) default '0.0000',
  `helpdesk_visible` int(11) NOT NULL default '1',
  `category` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_software_category (ID)',
  PRIMARY KEY  (`ID`),
  KEY `platform` (`platform`),
  KEY `location` (`location`),
  KEY `FK_glpi_enterprise` (`FK_glpi_enterprise`),
  KEY `date_mod` (`date_mod`),
  KEY `tech_num` (`tech_num`),
  KEY `name` (`name`),
  KEY `FK_groups` (`FK_groups`),
  KEY `FK_users` (`FK_users`),
  KEY `update_software` (`update_software`),
  KEY `FK_entities` (`FK_entities`),
  KEY `is_template` (`is_template`),
  KEY `is_update` (`is_update`),
  KEY `deleted` (`deleted`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3497 ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `glpi_softwareversions`
--
 
CREATE TABLE IF NOT EXISTS `glpi_softwareversions` (
  `ID` int(11) NOT NULL auto_increment,
  `sID` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_software (ID)',
  `state` int(11) NOT NULL default '0' COMMENT 'RELATION to glpi_dropdown_state (ID)',
  `name` varchar(255) collate utf8_unicode_ci default NULL,
  `comments` text collate utf8_unicode_ci,
  PRIMARY KEY  (`ID`),
  KEY `sID` (`sID`),
  KEY `name` (`name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3740 ;

Pour le moment, j'ai donc fait une ébauche de requette :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT * 
    FROM `glpi_computers` C
    LEFT OUTER JOIN  glpi_inst_software ISOFT
        ON C.ID = ISOFT.cID NOT IN ( SELECT * FROM SOFT WHERE SOFT.category = 2)
    LEFT OUTER JOIN  glpi_softwareversions VSOFT
        ON ISOFT.vID = VSOFT.ID 
    LEFT OUTER JOIN glpi_software SOFT
        ON VSOFT.ID = SOFT.ID
    WHERE SOFT.category = 2

category 2 étant la catégorie des antivirus.
Mais le problème avec ma requête, c'est que j'affiche UNIQUEMENT les PC qui ont un antivirus, et je voudrais voir les deux.

Si quelqu'un a une idée ?

D'avance merci

[sevyc64]

C'est quoi ce NOT IN (...) qui traine dans ta requete.

A première vue, pour moi, il n'est pas necessaire.

[DeTraX]

C'est quoi ce NOT IN (...) qui traine dans ta requete.

A première vue, pour moi, il n'est pas necessaire.

Même remarque.

Ca donne quoi à l'exécution quand tu l'enlèves ?

[Apo76]

Ça faisait partie de mes essais "à tatons" il ne sert a rien.
Si je le vire je récupère la liste des pc ayant un antivirus

[tatayo]

Bonjour,
Perso je ferai ceci:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT * 
	FROM `glpi_computers` C
	LEFT OUTER JOIN  glpi_inst_software ISOFT
		ON C.ID = ISOFT.cID and SOFT.category = 2
	LEFT OUTER JOIN  glpi_softwareversions VSOFT
		ON ISOFT.vID = VSOFT.ID 
	LEFT OUTER JOIN glpi_software SOFT
		ON VSOFT.ID = SOFT.ID

Ainsi tu as toutes les machines, et les données de l'antivirus le cas échéant.

Tatayo.

[Apo76]

Merci !
J'ai essayé ta requête mais je prends un beau petit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

#1054 - Unknown column 'SOFT.category' in 'on clause'

ce qui me parait normal étant donné qu'on crée l'alias SOFT au niveau de la dernière jointure.

J'ai donc essayé de faire ma requête sans alias, mais ça ne marche pas non plus, je prend des erreusr du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Erreur
 
requête SQL: Documentation
 
SELECT *
FROM `glpi_computers` C
LEFT OUTER JOIN glpi_inst_software ISOFT ON glpi_software.ID = ISOFT.cID
AND SOFT.category =2
LEFT OUTER JOIN glpi_softwareversions VSOFT ON ISOFT.vID = VSOFT.ID
LEFT OUTER JOIN glpi_software SOFT ON VSOFT.ID = SOFT.ID
LIMIT 0 , 30
 
MySQL a répondu:Documentation
#1054 - Unknown column 'glpi_software.ID' in 'on clause'

[DeTraX]

Merci !
J'ai essayer ta requette mais je prend un beau petit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

#1054 - Unknown column 'SOFT.category' in 'on clause'

ce qui me parait normal etant donné qu'on crée l'alias SOFT au niveau de la derneire jointure.

et si tu remets simplement la restriction au bon endroit il se passe quoi ?

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SELECT * 
	FROM `glpi_computers` C
	LEFT OUTER JOIN  glpi_inst_software ISOFT
		ON C.ID = ISOFT.cID 
	LEFT OUTER JOIN  glpi_softwareversions VSOFT
		ON ISOFT.vID = VSOFT.ID 
	LEFT OUTER JOIN glpi_software SOFT
		ON VSOFT.ID = SOFT.ID and SOFT.category = 2

[tatayo]

Merci !
J'ai essayer ta requette mais je prend un beau petit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

#1054 - Unknown column 'SOFT.category' in 'on clause'

ce qui me parait normal etant donné qu'on crée l'alias SOFT au niveau de la derneire jointure.
[/code]

Autant pour moi, j'ai collé la condition au mauvais endroit.

Pour ton deuxième essai, ça ne risque pas de fonctionner car tu utilises mal les alias (exemple glpi_software, tu lui mets un alias maus tu l'utilises telle quelle dans la première jointure).
Tatayo.

[Apo76]

Merci pour votre aide, en me basant sur ta requête, je suis arrivé a :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 
C.name AS nom,
C.comments AS comm,
SOFT.name AS antin,
VSOFT.name AS antiv
 
 
    FROM `glpi_computers` C
    LEFT OUTER JOIN  glpi_inst_software ISOFT
        ON C.ID = ISOFT.cID 
    LEFT OUTER JOIN  glpi_softwareversions VSOFT
        ON ISOFT.vID = VSOFT.ID 
    RIGHT OUTER JOIN glpi_software SOFT
        ON VSOFT.sID = SOFT.ID AND SOFT.category = 2
 
GROUP BY C.ID
ORDER BY C.name DESC

Il ne me reste plus qu'à trouver un moyen pour récupérer aussi les enregistrements n'ayant pas d'antivirus ( dans la même requête sinon c'est moins drôle ... )

pensez vous qu'un UNION serait adapté ?

[DeTraX]

Attention à ta requête :

• pour les logiciels antivirus qui ne sont installés sur aucun poste, tu vas sélectionner des lignes avec un nom d'ordinateur à Null... à toi d'évaluer l'impact sur ton traitement. En gros je ne suis pas vraiment convaincu par le RIGHT OUTER JOIN (même si tu as réussi à me mettre le doute)
• la clause GROUP BY ne sert à rien

Essaye la requête ci-dessous, j'espère que je n'ai pas fait de faute de frappe en aveugle, mais ça devrait marcher.
Dans la parenthèse on sélectionne la liste des Postes/Programme/Version pour les postes ayant un antivirus valide et on fait une jointure avec la liste des postes en prenant également les postes qui ne sont pas dans la liste.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT C.name AS nom,
C.comments AS comm,
AVSOFT.name AS antin,
AVSOFT.vname AS antiv
FROM `glpi_computers` C
LEFT OUTER JOIN 
(SELECT ISOFT.cID,     
SOFT.name,
VSOFT.name AS vName,  
         FROM   glpi_inst_software ISOFT
INNER JOIN  glpi_softwareversions VSOFT
ON  ISOFT.vID = VSOFT.ID 
INNER JOIN  glpi_software SOFT
ON  VSOFT.sID = SOFT.ID
WHERE SOFT.category = 2) AVSOFT
ON C.ID = AVSOFT.cID 
ORDER BY C.name DESC

[Apo76]

Attention à ta requête :

• la clause GROUP BY ne sert à rien

je l'ai ajoutée afin de pas me retrouver avec ~70 enregistrements cohérents au lieu de 3 489 enregistrements totalement incohérents

j'ai essayé ta requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 MySQL a répondu:Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM   glpi_inst_software ISOFT
                INNER JOIN  glpi_softwareversions VSOFT
                ' at line 10

j'ai donc été re-consulter http://sqlpro.developpez.com/cours/s...ntures/#LIII-B voir si je ne voyais pas d'où venait le problème de syntaxe mais je n'ai rien vu :/

[DeTraX]

une "," en trop à la fin de la liste des champs

[Apo76]

Effectivement, je ne l'avais pas vue !

Je teste ça demain matin

[Apo76]

Parfait !

Sans virgule, ça tourne au poil

Merci !

( Pour information, ma requête finale :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT 
			E.completename AS site,
			C.name AS nomm,
			C.serial AS serial,
			C.comments AS comm,
			NET.ifaddr AS ip,			
			OS.name AS os,
			OS_sp.name AS sp,
			AVSOFT.name	 AS antin,
			AVSOFT.vname AS antiv
		FROM `glpi_computers` C
		LEFT OUTER JOIN `glpi_entities` E
			ON C.FK_entities = E.`ID`
		LEFT OUTER JOIN `glpi_dropdown_os` OS
			ON C.os = OS.`ID`
		LEFT OUTER JOIN `glpi_networking_ports` NET
			ON C.ID = NET.`on_device` 
		LEFT OUTER JOIN `glpi_dropdown_os_sp` OS_sp
			ON C.os_sp = OS_sp.`ID`
 
		LEFT OUTER JOIN (
		SELECT ISOFT.cID,     
				SOFT.name,
				VSOFT.name AS vName
         FROM   glpi_inst_software ISOFT
				INNER JOIN  glpi_softwareversions VSOFT
						ON  ISOFT.vID = VSOFT.ID 
				INNER JOIN  glpi_software SOFT
						ON  VSOFT.sID = SOFT.ID
		WHERE SOFT.category = 2 ) AVSOFT
 
		ON C.ID = AVSOFT.cID 
		WHERE C.deleted=0 
		ORDER BY nomm DESC

)