Device Smart Label based on User LDAP Group?
Hello,
I would like to create a smart label of all devices that are assigned to members of an Active Directory user group. Is there a way to create this type of smart label using the label wizard and/or SQL?
I've tried playing around with the wizard, but don't see any way to associate user labels into a device label.
Answers (3)
I have something that might work for you.
First, make a User LDAP label that looks for members of the AD group in question. See the example below if you need help getting started with that part.
When the User Import runs, the users in that AD group will get this label in KACE.
Next, create a SQL Device Smart Label to look for the LDAP label of the user who uses the Device:
select
M.NAME,
U.FULL_NAME,
GROUP_CONCAT(L.NAME SEPARATOR ', ') AS LABEL,
BIOS_SERIAL_NUMBER
from USER U
join MACHINE M on U.FULL_NAME = M.USER_FULLNAME
join USER_LABEL_JT UJT on U.ID = UJT.USER_ID
join LABEL L on UJT.LABEL_ID = L.ID
group by U.FULL_NAME
HAVING GROUP_CONCAT(L.NAME) LIKE '%User - LDAP label created in step 1%'
This will only return one device per user, so you may need to modify as necessary. Also works as a handy report.
Hopefully this helps!
Hi LosNats456,
in default we can't dynamically group devices on user ad attributes. What you can do is running a script which echos all of the usergroups to a textfile and doing an inventory afterwards. Over a custom inventory you can then put the device in a label. Would that work for you?
Kind Regards
Timo
the query posted from Ondrar is not working. This one is ok:
SELECT MACHINE.NAME AS SYSTEM_NAME,
SYSTEM_DESCRIPTION,
MACHINE.IP,
MACHINE.MAC,
MACHINE.ID ASTOPIC_ID
FROM MACHINE
LEFT JOIN REMOTE_HOST_KUID ON REMOTE_HOST_KUID.KUID =MACHINE.KUID
LEFT JOIN REMOTE_HOST ON REMOTE_HOST.REMOTE_HOST_ID =REMOTE_HOST_KUID.REMOTE_HOST_ID
LEFT JOIN REMOTE_HOST_PROTOCOL ON REMOTE_HOST.PROTOCOL =REMOTE_HOST_PROTOCOL.REMOTE_HOST_PROTOCOL_ID
WHERE MACHINE.NAME in (select
M.NAME
/*U.FULL_NAME,
L.NAME,
BIOS_SERIAL_NUMBER*/
from USER U
join MACHINE M on U.FULL_NAME = M.USER_FULLNAME
join USER_LABEL_JT UJT on U.ID = UJT.USER_ID
join LABEL L on UJT.LABEL_ID = L.ID and L.NAME LIKE'%User - LDAP label created in step 1%')
AD Security Group: "Chrome Deployment" contains the user John Doe
KACE Managed Install: Chrome managed install is deployed on the devices assigned to John Doe.
I had no idea this was going to be a huge undertaking. I've tried ondrar's method and managed to create an LDAP user label. I created an additional label and pasted in the provided SQL, while replacing '%User - LDAP label created in step 1%' with '%Chrome Install%' but even after forcing inventory on devices that should be included in this label, the label contains no devices.
I have no idea where I'm going wrong. Can anyone expand on the provided solution with more detail or provide an alternative? My SQL skills are extremely limited. - thowa 3 years ago