Solved

Help with MYSQL query syntax

Posted on 2008-06-10
12
178 Views
Last Modified: 2013-12-13
Really all I need to do is a get a dump of systems, and every piece of software they contain from the database.

I know how to query for a particular software , and its attributes. But I need every piece of software and their version. The below query queries for an certain piece of software and it's version.

Basicaly I'm trying to see if there's a way I can sort this in a readable manner. So maybe it shows a system name in the far left column, and then to the right all software.names for that system.name. And then so on after that.


Sorry there's alot of other junk in that query that arent needed. (ie harddrive, processor,vendor, model,etc) Mainly just system_name, software_name, system_uuid, sofware_timestamp=system_timestamp are needed.

Thanks
SELECT s.system_last_boot
      ,s.system_timestamp
      ,s.system_ou
      ,s.system_memory
      ,s.system_os_name
      ,s.system_model
      ,s.system_vendor
      ,s.system_name
      ,s.system_uuid
     , MAX(CASE WHEN so.software_name = 'Symantec AntiVirus' THEN so.software_version END) `Version1` 
     , MAX(CASE WHEN so.software_name = 'MDAC' THEN so.software_version END) `Version2` 
     , MAX(p.processor_name) AS Processor_Name
     , MAX(h.hard_drive_size) AS hard_drive_size
FROM `system` s LEFT JOIN 
     `software` so ON so.software_uuid = s.system_uuid AND so.software_timestamp=s.system_timestamp
                      AND (so.`software_name` = 'Symantec AntiVirus' 
                           OR  so.`software_name` = 'MDAC') LEFT JOIN
     `processor` p on p.processor_uuid = s.system_uuid AND p.processor_timestamp=s.system_timestamp AND p.processor_device_id = 'CPU0' LEFT JOIN
     `hard_drive` h on h.hard_drive_uuid = s.system_uuid AND h.hard_drive_timestamp=s.system_timestamp AND h.hard_drive_index = 0
WHERE system_ou LIKE '%corp,rich,wpelab,adlab,Computers,%'
GROUP BY s.system_last_boot,s.system_timestamp,s.system_ou,s.system_memory,s.system_os_name,s.system_model,s.system_vendor,s.system_name,s.system_uuid

Open in new window

0
Comment
Question by:WinPE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 21751721
Try the below

Should return a separate row for each PC with all software and it's relevant version number concatenated into a single field.

Let me know how you get on, or if I've made a silly syntax error :o)

I'll leave the column re-ordering to you ;o)


SELECT s.system_last_boot
      ,s.system_timestamp
      ,s.system_ou
      ,s.system_memory
      ,s.system_os_name
      ,s.system_model
      ,s.system_vendor
      ,s.system_name
      ,s.system_uuid
     , GROUP_CONCAT (
       CONCAT_WS(' - Ver:',so.software_name,so.software_version) ORDER BY so.software_name DESC 
       SEPARATOR \n)
     , MAX(p.processor_name) AS Processor_Name
     , MAX(h.hard_drive_size) AS hard_drive_size
FROM `system` s LEFT JOIN 
     `software` so ON so.software_uuid = s.system_uuid AND so.software_timestamp=s.system_timestamp
     LEFT JOIN
     `processor` p on p.processor_uuid = s.system_uuid AND p.processor_timestamp=s.system_timestamp AND p.processor_device_id = 'CPU0' LEFT JOIN
     `hard_drive` h on h.hard_drive_uuid = s.system_uuid AND h.hard_drive_timestamp=s.system_timestamp AND h.hard_drive_index = 0
WHERE system_ou LIKE '%corp,rich,wpelab,adlab,Computers,%'
GROUP BY s.system_last_boot,s.system_timestamp,s.system_ou,s.system_memory,s.system_os_name,s.system_model,s.system_vendor,s.system_name,s.system_uuid,so.software_name,so.software_version

Open in new window

0
 

Author Comment

by:WinPE
ID: 21752007
Thanks Chris,

I get this error:

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 'ORDER BY so.software_name DESC
       SEPARATOR \n)
     , MAX(p.processor_na' at line 11




I cleaned up the orriginal query I gave you to get rid of some of the junk. I had someone help me with this query to. I tried getting rid of the 'processor' and 'hard_drive' lines, but it gives me grouping errors after that.
SELECT s.system_timestamp
      ,s.system_name
      ,s.system_uuid
     , MAX(CASE WHEN so.software_name = 'Symantec AntiVirus' THEN so.software_version END) `Version1` 
     , MAX(CASE WHEN so.software_name = 'MDAC' THEN so.software_version END) `Version2` 
FROM `system` s LEFT JOIN
     `software` so ON so.software_uuid = s.system_uuid AND so.software_timestamp=s.system_timestamp
                      AND (so.`software_name` = 'Symantec AntiVirus' 
                           OR  so.`software_name` = 'MDAC') LEFT JOIN
     `processor` p on p.processor_uuid = s.system_uuid AND p.processor_timestamp=s.system_timestamp AND p.processor_device_id = 'CPU0' LEFT JOIN
     `hard_drive` h on h.hard_drive_uuid = s.system_uuid AND h.hard_drive_timestamp=s.system_timestamp AND h.hard_drive_index = 0
GROUP BY s.system_timestamp,s.system_name,s.system_uuid

Open in new window

0
 
LVL 7

Accepted Solution

by:
Chrisedebo earned 500 total points
ID: 21752103
Give the following a try.
SELECT s.system_timestamp
      ,s.system_name
      ,s.system_uuid
      ,GROUP_CONCAT (
       CONCAT_WS(' - Ver:',so.software_name,so.software_version) ORDER BY so.software_name DESC 
       SEPARATOR '\n')
FROM `system` s LEFT JOIN  `software` so 
		ON so.software_uuid = s.system_uuid AND so.software_timestamp=s.system_timestamp
GROUP BY s.system_timestamp,s.system_name,s.system_uuid

Open in new window

0
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 

Author Comment

by:WinPE
ID: 21752185
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 'ORDER BY so.software_name DESC
       SEPARATOR '\n')
FROM `system` s LEFT JO' at line 5

DOH
0
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 21752257
Doh indeed....

Can you give me the structure of the two tables so I can run the queries here?
0
 

Author Comment

by:WinPE
ID: 21752312
To be honest I'm so new to this, I dont really know how to... Could you advise on how to do this...

Sorry for the lack of knowledge here.
0
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 21752346
select * from `system`

then paste the first line (maybe the first two to give me a sample of the data)

then the same for software

select * from `software`
0
 

Author Comment

by:WinPE
ID: 21752421
ohh that's all you wanted...

I did just the values you probably need. Otherwise there's like 20 different fields.

SELECT s.`system_uuid`, s.`system_name`, s.`system_timestamp` FROM system s;
'server1.blah.blah.com', 'server1', 20080610174010
'server2.blah.blah.com', 'server2', 20080523180509

Open in new window

0
 

Author Comment

by:WinPE
ID: 21752452
Here's software, with first 6+ lines.


SELECT s.`software_id`, s.`software_uuid`, s.`software_name`, s.`software_version`, s.`software_timestamp` FROM software s;
327616, 'server1.blah.blah.com', 'Symantec AntiVirus', '10.0.2021.2', 20080611002106
327614, 'server1.blah.blah.com', 'Microsoft Application Error Reporting', '11.0.5228.1', 20080611002106
327615, 'server1.blah.blah.com', 'Windows Server 2003 Service Pack 1 Administration Tools Pack', '5.2.3790.1830', 20080611002106
327610, 'server1.blah.blah.com', 'Update for Windows Server 2003 (KB931836)', '1', 20080611002106
327611, 'server1.blah.blah.com', 'Security Update for Windows Server 2003 (KB932168)', '1', 20080611002106
327612, 'server1.blah.blah.com', 'Update for Windows Server 2003 (KB933360)', '1', 20080611002106
327613, 'server1.blah.blah.com', 'LiveUpdate 2.6 (Symantec Corporation)', '2.6.18.0', 20080611002106
327608, 'server1.blah.blah.com', 'Security Update for Windows Server 2003 (KB930178)', '1', 20080611002106
327609, 'server1.blah.blah.com', 'Security Update for Windows Server 2003 (KB931784)', '1', 20080611002106
303937, 'server2.blah.blah.com', 'Codec - Audio - tssoft32', '1.01', 20080610080306
303938, 'server2.blah.blah.com', 'MDAC', '2.82.3959.0', 20080610080306
303939, 'server2.blah.blah.com', 'DirectX 9c', '4.09.00.0904', 20080610080306

Open in new window

0
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 21752578
Sorry, one more question. Do you by any chance know the version of MySQL that you are using?


SHOW VARIABLES LIKE "%version%";

Open in new window

0
 

Author Comment

by:WinPE
ID: 21752598
'protocol_version', '10'
'version', '5.0.51b-community-nt'
'version_comment', 'MySQL Community Edition (GPL)'
'version_compile_machine', 'ia32'
'version_compile_os', 'Win32'
0
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 21752660
I'm confused....

I'm using version 4 and it seems to work ok....

when I put the '\n' without the single quotes it gives me an error, but my error says line 1, not line 5.....

have you got anything else running above this bit?
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question