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)
Please log in to answer
Posted by:
JasonEgg
6 years ago
> 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)
