K1000 SQL Smart Label based on Windows Service
I am trying to create a smart label that will group devices based on a service startup-type.
The K1000 has the Inventory > Services section that records each service, creating a different record for each version of the service and each start-up state of that version. I know that I can open up one of the records and associate a label to it but that's not very "Smart". If Windows 10 was just seen as Windows 10, it might be different, but, I'm sure you've noticed, with every release of 10, there's a new version of Windows that you have to associate many parts of the K1 to (Custom Software, Scripts, etc.).
So I wanted to create a smart label that would, for instance, label computers that have the Remote Registry service in a state other than manual/demand.
I created the following, working query in MySQL Workbench:
I've spun it a few different ways. Here was an earlier way:
For some reason, it is not working. I tried creating a SQL report in the K1 just to test if there was something different from the MySQL Workbench and the MariaDB that's on the K1 and it worked as expected.
Anyone have any ideas? Is there a log I can check that records errors in labeling? I'm up for anything. I know I can accomplish this be creating another Custom Software entry but if the information is already there, I want to use it.
I might be slow to respond because it's quittin time on Friday. Be back Monday :)
The K1000 has the Inventory > Services section that records each service, creating a different record for each version of the service and each start-up state of that version. I know that I can open up one of the records and associate a label to it but that's not very "Smart". If Windows 10 was just seen as Windows 10, it might be different, but, I'm sure you've noticed, with every release of 10, there's a new version of Windows that you have to associate many parts of the K1 to (Custom Software, Scripts, etc.).
So I wanted to create a smart label that would, for instance, label computers that have the Remote Registry service in a state other than manual/demand.
I created the following, working query in MySQL Workbench:
SELECT
M.NAME
FROM MACHINE M
INNER JOIN MACHINE_NTSERVICE_JT MNTS ON (MNTS.MACHINE_ID=M.ID)
INNER JOIN NTSERVICE NTS ON (NTS.ID=MNTS.NTSERVICE_ID)
WHERE NTS.NAME="RemoteRegistry" AND STARTUP_TYPE!="SERVICE_DEMAND_START"
I've spun it a few different ways. Here was an earlier way:
SELECT
M.NAME
FROM NTSERVICE NTS
INNER JOIN MACHINE_NTSERVICE_JT MNST ON (MNST.NTSERVICE_ID=NTS.ID)
INNER JOIN MACHINE M ON (M.ID=MNST.MACHINE_ID)
WHERE ((NTS.NAME="RemoteRegistry") AND (NTS.STARTUP_TYPE!="SERVICE_DEMAND_START"))
For some reason, it is not working. I tried creating a SQL report in the K1 just to test if there was something different from the MySQL Workbench and the MariaDB that's on the K1 and it worked as expected.
Anyone have any ideas? Is there a log I can check that records errors in labeling? I'm up for anything. I know I can accomplish this be creating another Custom Software entry but if the information is already there, I want to use it.
I might be slow to respond because it's quittin time on Friday. Be back Monday :)
0 Comments
[ + ] Show comments
Answers (3)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
When you create a smart label with the wizard it selects these fields:
select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
I'm not sure if it really needs all of them, but try using that in your query.
Comments:
-
I gave it a shot, testing it successfully in MySQL Workbench first, but the label still did not apply after forcing inventory of a handful of machines matching the criteria.
I have created other custom SQL Smart Labels previously that had not required those lines. Here is a simple one that works based off of the returned value of a custom inventory item. Notice the string field value is the only column selected (no computer name or other Machine table data)
SELECT
MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE
FROM MACHINE
Inner Join MACHINE_CUSTOM_INVENTORY ON (MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID)
WHERE (MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=8337) AND (MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE="False<br/>") - Techman D 7 years ago
Posted by:
JasonEgg
7 years ago
I have had no problems with machine smart labels as long as the machine ID is included in the select clause. Try including the machine ID as the first column in the query and give it the alias TOPIC_ID (this is exactly what the wizard does).
Comments:
-
That didn't work either. When creating with the Wizard, I have seen TOPIC_ID used as well as a couple other aliases. I attempted them to no avail.
I to have not had a problem with creating the machine smart labels before, whether I used the Machine.ID or not. - Techman D 7 years ago-
Odd. If you go into the label details page, is their anything checked under "Restrict Label Usage To" and/or is there a label group assigned to it? - JasonEgg 7 years ago
Posted by:
JasonEgg
7 years ago
Top Answer
I played around with the query and got it to work. I think the error spawned from no spaces in the where clause comparisons? I also changed double-quotes to single-quotes but I don't know if that affects anything.
SELECT
MACHINE.ID,
MACHINE.NAME
FROM MACHINE
JOIN MACHINE_NTSERVICE_JT MNTS ON (MNTS.MACHINE_ID=MACHINE.ID)
JOIN NTSERVICE NTS ON (NTS.ID=MNTS.NTSERVICE_ID)
WHERE NTS.NAME = 'RemoteRegistry'
AND STARTUP_TYPE != 'SERVICE_DEMAND_START'
Comments:
-
You are awesome. That did it! Thank you for the help. I'll add a reminder to make sure there are spaces and single quotes for good measure. Thanks again! - Techman D 7 years ago
