Solved

Help with MYSQL query syntax

Posted on 2008-06-10
12
172 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
  • 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now