K1000 SQL Report Help Request
01/26/2018 1008 views
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',
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'
END AS LOCATION
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%'