How can I associate a user's manager to them in a K1000 report?
We have used LDAP sync to successfully include the users department # & manager name in the K1000 user records. My boss wants a report to show computer name, user name, dept, timezone and manager.
All of this information shows up in the report wizard for the 'device' topic except manager which I've only been able to find in 'user'. If I create a report with a single topic of either 'device' or 'user' I can get the fields associated with those, but if I try to create a report with a topic and a subtopic, it will not generate a report for me.
I have copied the SQL code for each report but if I try to combine them to get all of the information from 'device' and 'user' I get errors. I did find a reference to "MANAGER_ID" in some documentation but that only provides a Manager ID number and I can't figure out how to get this to show up as a manager's actual name.
I've attached pictures of where I got 'device' and 'user' and a shot of the output using Manager_ID.
I'm not very experienced with SQL queries but can usually manage to work out what needs to be done by creating a report with the wizard and then studying the SQL code, however this time I can't get it to work. Any help will be appreciated.
Answers (3)
Do you want just the fields listed in the screenshot with the manager ID replaced with the actual managers name or was there any additional fields not listed?
Here is an SQL example
SELECT MACHINE.NAME AS 'System Name',
MACHINE.USER_NAME AS ' User Name',
U1.FULL_NAME AS 'Assignee Name',
MACHINE.TZ_AGENT as 'Time Zone',
U2.FULL_NAME as 'Manager'
FROM MACHINE
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN USER U1 ON U1.ID = ASSET.OWNER_ID
LEFT JOIN USER U2 ON U2.ID = U1.MANAGER_ID
ORDER BY 'System Name'
Comments:
-
Thanks for the help with this. My report also includes a Department number which I was able to add to your script.
Also, our naming convention for US based computers is to include the user name in the computer name so I am trying to only display computers in the US where the computer name does not contain the user name for compliance checks. I can get it to work if I don't include the JOIN for U2.
Original SQL from KACE wizard:
SELECT MACHINE.NAME AS SYSTEM_NAME,
MACHINE.USER_NAME,
ASSET_OWNER.FULL_NAME AS ASSIGNEE_NAME,
ASSET_OWNER.BUDGET_CODE AS DEPARTMENT,
TZ_AGENT AS TIMEZONE,
MANAGER_ID FROM MACHINE
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN USER ASSET_OWNER ON ASSET_OWNER.ID = ASSET.OWNER_ID WHERE ((TZ_AGENT like 'America%') AND (MACHINE.NAME not regexp MACHINE.USER_NAME))
ORDER BY SYSTEM_NAME
This returns everything I need except it shows the Manager ID number instead of a name.
Your SQL modified to include Department # returns all computers:
SELECT MACHINE.NAME AS 'System Name',
MACHINE.USER_NAME AS ' User Name',
U1.FULL_NAME AS 'Assignee Name',
U1.BUDGET_CODE AS 'Department',
MACHINE.TZ_AGENT as 'Time Zone',
U2.FULL_NAME as 'Manager'
FROM MACHINE
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN USER U1 ON U1.ID = ASSET.OWNER_ID
LEFT JOIN USER U2 ON U2.ID = U1.MANAGER_ID
ORDER BY 'System Name'
If I try adding any WHERE statement to the "LEFT JOIN USER U1" line, I get an error 1064 saving the script that tells me to check the syntax near the "JOIN U2" line:
LEFT JOIN USER U1 ON U1.ID = ASSET.OWNER_ID WHERE ((MACHINE.TZ_AGENT like 'America%') AND (MACHINE.NAME not regexp MACHINE.USER_NAME))
LEFT JOIN USER U2 ON U2.ID = U1.MANAGER_ID - ScottAday 3 years ago
Top Answer
Your SQL has a syntax error, you should be able to use this version.
SELECT MACHINE.NAME AS 'System Name',
MACHINE.USER_NAME AS ' User Name',
U1.FULL_NAME AS 'Assignee Name',
U1.BUDGET_CODE AS 'Department',
MACHINE.TZ_AGENT as 'Time Zone',
U2.FULL_NAME as 'Manager'
FROM MACHINE
LEFT JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN USER U1 ON U1.ID = ASSET.OWNER_ID
LEFT JOIN USER U2 ON U2.ID = U1.MANAGER_ID
WHERE ((MACHINE.TZ_AGENT like 'America%') AND (MACHINE.NAME not regexp MACHINE.USER_NAME))
ORDER BY 'System Name'
Comments:
-
Thank you! I knew it would be something simple and I think I tried just about everything except moving the "Where" clause because I thought it was part of the other line. Obviously I need to learn more SQL syntax. - ScottAday 3 years ago
