/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000 SQL Smart Label based on Windows Service

05/19/2017 1091 views
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:


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 :)
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
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'
Answered 05/25/2017 by: JasonEgg
Red Belt

  • 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!

All Answers

0
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.
Answered 05/22/2017 by: chucksteel
Red Belt

  • 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/>")
0
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).
Answered 05/24/2017 by: JasonEgg
Red Belt

  • 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.
    • 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?
      • It is restricted to Device Inventory and Resources.
      • try unchecking everything. No idea if this affects it but I am out of ideas.
  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.