Solved

Help with MYSQL query syntax

Posted on 2008-06-10
12
177 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

751 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