1
0
-1
3 answers
- 10-1
Completed query below.
SELECT system.id AS `system.id`, system.icon AS `system.icon`, system.name AS `system.name`, system.domain AS `system.domain`, system.os_family AS `system.os_family`, windows.user_name AS `windows.user_name`, os_installation_date AS `os_installation_date`, software.name as `software.name`, processor.description AS `processor.description` FROM software LEFT JOIN system ON (software.system_id = system.id AND software.current = 'y') LEFT JOIN orgs ON (orgs.id = system.org_id) LEFT JOIN windows ON (windows.system_id = system.id AND windows.current = 'y') LEFT JOIN processor ON (processor.system_id = system.id AND processor.current = 'y') WHERE @filter AND software.name LIKE 'Microsoft Office%' AND (software.name LIKE '%Starter%' OR software.name LIKE '%Basic%' OR software.name LIKE '%Personal%' OR software.name LIKE '%Home%' OR software.name LIKE '%Student%' OR software.name LIKE '%Business%' OR software.name LIKE '%Standard%' OR software.name LIKE '%Ultimate%' OR software.name LIKE '%Enterprise%' OR software.name LIKE '%Professional%' OR software.name LIKE '%Professional Plus%') AND (software.name LIKE '%2003%' OR software.name LIKE '%2007%' OR software.name LIKE '%2010%' OR software.name LIKE '%2013%' OR software.name LIKE '%2016%' OR software.name LIKE '%365%')
Add your comment... - 10-1
- 10-1
Hello Wes,
When I'm building reports I will often start by looking through the existing reports to see if there is one that includes a field(s) I want to use. In your case, the Report → Hardware → Memory and Processors includes the processor description field. To view the sql for this report select Manage → Queries → List Queries from the menu, then click the Details button for Memory and Processors.
The field you want is processor.description, but notice in the example report that this is linked to the system.id via an inner join: system.id = processor.system_id AND processor.current = 'y'
Best,
Mark H
Add your comment...
I am very new at this and I have been working at customizing my own query. How could I add the processor.description to the below query I have been working on. I've tried adding the line processor.description as `processor.description`, but I get an error. I think because its a field?
SELECT system.id AS `system.id`, system.icon AS `system.icon`, system.name AS `system.name`, system.domain AS `system.domain`, system.os_family AS `system.os_family`, windows.user_name AS `windows.user_name`, os_installation_date AS `os_installation_date`, software.name as `software.name` FROM software LEFT JOIN system ON (software.system_id = system.id) LEFT JOIN orgs ON (orgs.id = system.org_id) LEFT JOIN windows ON (windows.system_id = system.id AND windows.current = 'y') WHERE @filter AND software.current = 'y' AND software.name LIKE 'Microsoft Office%' AND (software.name LIKE '%Starter%' OR software.name LIKE '%Basic%' OR software.name LIKE '%Personal%' OR software.name LIKE '%Home%' OR software.name LIKE '%Student%' OR software.name LIKE '%Business%' OR software.name LIKE '%Standard%' OR software.name LIKE '%Ultimate%' OR software.name LIKE '%Enterprise%' OR software.name LIKE '%Professional%' OR software.name LIKE '%Professional Plus%') AND (software.name LIKE '%2003%' OR software.name LIKE '%2007%' OR software.name LIKE '%2010%' OR software.name LIKE '%2013%' OR software.name LIKE '%2016%' OR software.name LIKE '%365%')