[Answered] Exporting a list of computers with a specific service
Hello All,
I am trying to export a list of computers with specific services installed. I have a label made up of all the services I want to track and I have tried exporting to a csv, but that only shows all the different labels and the numbers of computers with them installed.
Any suggestions?
2 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
dugullett
11 years ago
You should be able to change the "View By" to the label you need, and then export to csv. If that doesn't work for you try something like the query below.
SELECT DISTINCT M.NAME, IP, USER_LOGGED FROM MACHINE M LEFT JOIN MACHINE_NTSERVICE_JT SJT ON SJT.MACHINE_ID = M.ID LEFT JOIN NTSERVICE NS ON SJT.NTSERVICE_ID= NS.ID WHERE NS.NAME = '<SERVICE NAME>' ORDER BY M.NAME
Comments:
-
This is good, but I need a label full of various services to export all the computers. - TylerAngyal 11 years ago
-
SELECT DISTINCT M.NAME,
GROUP_CONCAT(DISTINCT NS.NAME SEPARATOR '\n') AS 'Service Name',IP, USER_LOGGED
FROM MACHINE M
LEFT JOIN MACHINE_NTSERVICE_JT SJT ON SJT.MACHINE_ID = M.ID
LEFT JOIN NTSERVICE NS ON SJT.NTSERVICE_ID= NS.ID
WHERE NS.NAME RLIKE '<SERVICE NAME 1>|<SERVICE NAME 2>|<SERVICE NAME 3>'
GROUP BY M.NAME
ORDER BY M.NAME - dugullett 11 years ago-
AHHHHH see that did it! Thanks a lot! - TylerAngyal 11 years ago
NTSERVICE.PRODUCT_VERSION as Version, NTSERVICE.COMPANY_NAME as Manufacturer from NTSERVICE
join NTSERVICE_LABEL_JT X on NTSERVICE.ID = X.NTSERVICE_ID AND X.LABEL_ID = 122
left join MACHINE_NTSERVICE_JT on MACHINE_NTSERVICE_JT.NTSERVICE_ID = NTSERVICE.ID
left join NTSERVICE_LABEL_JT on NTSERVICE.ID = NTSERVICE_LABEL_JT.NTSERVICE_ID
left join LABEL on LABEL.ID = NTSERVICE_LABEL_JT.LABEL_ID
group by NTSERVICE.ID ORDER BY NAME
So this just returns a list of the services in the label with the amount of computers for each. I need to have a list of all the computers that has one or all of the services in the label.
The real issue is that we need to remove Dameware from a bunch of machines but not all of them have and Add/Remove entry.
Thank you for your help. - TylerAngyal 11 years ago