/build/static/layout/Breadcrumb_cap_w.png

K1000 SQL Report Help Request

Hello Everyone, I have been trying to write a very simple report and can't seem to get it working.  I need a house a house wide report of office products that include System name, Software name (office) and version number. I have been trying to tweak the below query to no avail.  Any help would be appreciated. 

SELECT MACHINE.NAME AS 'COMPUTER NAME', MACHINE.IP AS 'IP', SAM_VIEW_DISCOVERED_SUITES.NAME AS 'SOFTWARE', 
/*
SAM_VIEW_DISCOVERED_SUITES.PRODUCT_NAME, SAM_VIEW_DISCOVERED_SUITES.MAJOR_VERSION,
*/
CASE WHEN MACHINE.IP LIKE '192.168.0.%' THEN 'OFFICE 1'
    WHEN MACHINE.IP LIKE '192.168.1.%' THEN 'OFFICE 2'
    WHEN MACHINE.IP LIKE '192.168.2.%' THEN 'OFFICE 3'
    ELSE MACHINE.IP
END AS LOCATION
FROM SAM_VIEW_DISCOVERED_SUITES  
LEFT JOIN SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE ON SAM_VIEW_DISCOVERED_SUITES.ID = SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.ID 
LEFT JOIN MACHINE ON MACHINE.ID = SAM_VIEW_MACHINE_DISCOVERED_SOFTWARE.MACHINE_ID
WHERE SAM_VIEW_DISCOVERED_SUITES.NAME like '%office%'

1 Comment   [ + ] Show comment
  • This query reports what I believe you want, can you explain what you are trying to add? - chucksteel 6 years ago
    • Hello Chuck, thanks for the reply. What I need is the version added, in example: Microsoft Office 365 ProPlus - en-us (16.0.8625.2139), I would also like to take off the location information but that is less important. - tramiksim 6 years ago

Answers (1)

Posted by: JasonEgg 6 years ago
Red Belt
0
> What I need is the version added, in example: Microsoft Office 365 ProPlus - en-us (16.0.8625.2139)
Your query is currently using the "Software Catalog" module in KACE instead of the "Software" module. Software Catalog is great for grouping software together and a perfect use case is reporting on software "suites" like Office. However, this grouping means you'll lose some info like exact version number of the various pieces of software inside a "suite." There is probably a way to get the data you want in this report, but I have a feeling it would be rather difficult.

Aside: You actually do have the code already in the query to show the "Major Version" of the suite, which might be helpful but isn't exactly what you want. The fields do not show up because the code is "commented out." In MySQL, blocks of code can be commented (ignored) by using /* before and */ after the code. So you can delete those characters and get more fields in your query, namely "Product Name" and "Major Version".

> I would also like to take off the location information
Anything separated by commas in the SELECT portion of a query is a field which will be displayed in your report. So if you don't want the "LOCATION" field to show, find everything between the commas that denote that field and delete it (including any preceding commas). Even though the "LOCATION" field in your report has multiple lines, the same logic applies: delete the block that starts with "CASE" and ends with "LOCATION" (and delete the preceding comma)
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ