I was looking to add user's managers to the Manager field in KACE, but as we all know, it's not that easy. So I searched and found this: Adding Manager information to your KACE user table, which I used as a start. That method didn't work for us exactly in our environment, so I came up with my own.
Step 1
In your User Import Schedule, add DistinguishedName and Manager to the Attributes to retrieve, if you don't already have them.
Map the DistinguishedName attribute to one of the standard fields. We don't use Pager Number, so I'm using that in this example. (I tried mapping this to a custom field, but not being a SQL expert, I couldn't figure out the JOINs between two USERs and USER_FIELD_VALUEs. If you know how to do it, leave a message in the comments section below!)
Map the Manager attribute to one of the custom fields. In this example, it's the 4th custom field.
Now, when your import runs, you'll have the user's distinguished name, and their manager's distinguished name in the user's record, and can then match them up.
Step 2
Create a new Custom Ticket Rule.
Select SQL:
SELECT
USER.ID,
USER.USER_NAME,
USER.MANAGER_ID,
USER.PAGER_PHONE, -- The standard field I decided to use for the DistinguishedName
UFV.FIELD_ID AS USER_FIELD_VALUE_FIELD_ID,
UFV.FIELD_VALUE AS USER_FIELD_VALUE_FIELD_VALUE,
MGR.ID,
MGR.USER_NAME AS MANAGER_NAME
FROM USER
JOIN USER_FIELD_VALUE UFV ON UFV.USER_ID = USER.ID
JOIN USER MGR ON MGR.PAGER_PHONE = UFV.FIELD_VALUE AND UFV.FIELD_ID = 4 -- 4 is the custom field number for the Manager attribute. Here we're joining the manager's USER table on the distinguished name fields.
WHERE
USER.PAGER_PHONE LIKE 'CN%' -- So we're only working on imported users
ORDER BY USER.ID
UPDATE
USER
JOIN USER_FIELD_VALUE UFV ON UFV.USER_ID = USER.ID
JOIN USER MGR ON MGR.PAGER_PHONE = UFV.FIELD_VALUE AND UFV.FIELD_ID = 4
SET USER.MANAGER_ID = MGR.ID -- The actual Manager field is the manager's user ID, so that's what we're setting here.
WHERE
USER.PAGER_PHONE LIKE 'CN%'
Step 3
Schedule the rule to run after the import. Be sure to give the import enough time to complete.
Step 4
The Manager field is now populated!
Hope this helps someone!
Comments