Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Help with MYSQL query syntax

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
WinPE
Asked:
WinPE
  • 6
  • 6
1 Solution
 
ChrisedeboCommented:
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
 
WinPEAuthor Commented:
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
 
ChrisedeboCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
WinPEAuthor Commented:
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
 
ChrisedeboCommented:
Doh indeed....

Can you give me the structure of the two tables so I can run the queries here?
0
 
WinPEAuthor Commented:
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
 
ChrisedeboCommented:
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
 
WinPEAuthor Commented:
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
 
WinPEAuthor Commented:
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
 
ChrisedeboCommented:
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
 
WinPEAuthor Commented:
'protocol_version', '10'
'version', '5.0.51b-community-nt'
'version_comment', 'MySQL Community Edition (GPL)'
'version_compile_machine', 'ia32'
'version_compile_os', 'Win32'
0
 
ChrisedeboCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now