Hi! I'm not familiar enough with SQL to create this on my own, and I found another post on this site that has almost exactly what I need.
That post can be found here: http://www.itninja.com/blog/view/how-to-show-how-many-office-installs-and-what-version-of-office-per-site
The SQL there is just missing something that lets me determine the device name. The SQL in that post works perfectly, however it brings back the results from my entire organization. I only need it for specific devices, in particular ones that start with certain letters/numbers. For instance, devices that start with G1A or G1B. Can someone help me modify this SQL to include that kind of variable? Thanks in advance!

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%'
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

  • This content is currently hidden from public view.
    Reason: Removed by member request
    For more information, visit our FAQ's.
Please log in to comment

Answer Chosen by the Author

1
After the last line add:
AND MACHINE.NAME like "G1A%"
Answered 10/11/2017 by: chucksteel
Red Belt

  • Thank you! That works perfectly!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share