Solved

Help with MYSQL query syntax

Posted on 2008-06-10
12
176 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

761 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