Requête SQL complexe

Version imprimable

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:

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:

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

09/08/2010, 09h49
sevyc64

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

A première vue, pour moi, il n'est pas necessaire.
09/08/2010, 10h53
DeTraX

Citation:

Envoyé par sevyc64

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 ?
09/08/2010, 11h16
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

Bonjour,
Perso je ferai ceci:

Code:

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.

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

Code:

#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:

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'

Citation:

Envoyé par Apo76

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

Code:

#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:

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

09/08/2010, 14h52
tatayo

Citation:

Envoyé par Apo76

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

Code:

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

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

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é ?

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:

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

Citation:

Envoyé par DeTraX

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 :D

j'ai essayé ta requête :
Code:

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 :/

09/08/2010, 16h45
DeTraX

une "," en trop à la fin de la liste des champs :)
09/08/2010, 21h35
Apo76

Effectivement, je ne l'avais pas vue !

Je teste ça demain matin :)

Parfait !

Sans virgule, ça tourne au poil :)

Merci !

( Pour information, ma requête finale :

Code:

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

)