/build/static/layout/Breadcrumb_cap_w.png

Add Assignee Custom Field to Machine Report

Hello, I'm running the report "Computers MIA for X Days" which is built in.  I have added "Assignee Name" to show the the user machine that is assigned, and this works well.  However, I can't figure out how to add a custom field from the assigned user.  What I mean is we use the "Custom 1" and "Custom 2" fields from the LDAP import to display certain things.  For Custom 2, we are bringing in Department.  I want this to show on the report as well.  Is this possible?

The default report is as follows in SQL:

SELECT MACHINE.NAME AS SYSTEM_NAME, SC.CONNECT_TIME, ASSET_OWNER.FULL_NAME AS ASSIGNEE_NAME

FROM MACHINE  LEFT JOIN KBSYS.KUID_ORGANIZATION KUID_ORG ON KUID_ORG.KUID = MACHINE.KUID

LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID AND substring(SCHEMA(),4) = KUID_ORG.ORGANIZATION_ID

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 ((SC.DISCONNECT_TIME < now() ) AND (concat(SC.CLIENT_CONNECTED) = '0') AND ((TIMESTAMP(SC.DISCONNECT_TIME) > NOW() OR TIMESTAMP(SC.DISCONNECT_TIME) <= DATE_SUB(NOW(),INTERVAL 14 DAY))))

ORDER BY SYSTEM_NAME


Thanks in advance!


0 Comments   [ + ] Show comments

Answers (2)

Posted by: Hobbsy 4 years ago
Red Belt
0

The challenge you have is that the custom fields within the User accounts and not held in the USER table. They are in fact held in the USER_FIELD_VALUE table and joined to the USER

I have put together the following that works in KACE and will display a machine name, user and your custom field 1, but you may need to work on the WHERE Statement that you need. Paste it in as an SQL report and run it to see what you get


SELECT DISTINCT MACHINE.NAME, USER.USER_NAME, USER_FIELD_VALUE.FIELD_VALUE as Department

FROM (MACHINE

      INNER JOIN ORG1.USER USER ON (MACHINE.USER = USER.USER_NAME))

     INNER JOIN ORG1.USER_FIELD_VALUE USER_FIELD_VALUE

        ON (USER_FIELD_VALUE.USER_ID = USER.ID)

WHERE (TIMESTAMP(MACHINE.LAST_INVENTORY) <= DATE_SUB(NOW(),INTERVAL 14 DAY)) AND USER_FIELD_VALUE.FIELD_ID = '1'

ORDER BY MACHINE.NAME

Posted by: chucksteel 4 years ago
Red Belt
0

Here's the query I came up with:

SELECT MACHINE.NAME AS SYSTEM_NAME, 
SC.CONNECT_TIME, SC.DISCONNECT_TIME,
ASSET_OWNER.FULL_NAME AS ASSIGNEE_NAME,
DEPARTMENT.FIELD_VALUE AS 'Department'
FROM MACHINE  
LEFT JOIN KBSYS.KUID_ORGANIZATION KUID_ORG ON KUID_ORG.KUID = MACHINE.KUID
LEFT JOIN KBSYS.SMMP_CONNECTION SC ON MACHINE.KUID = SC.KUID AND substring(SCHEMA(),4) = KUID_ORG.ORGANIZATION_ID
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
LEFT JOIN USER_FIELD_VALUE DEPARTMENT on DEPARTMENT.USER_ID = ASSET.OWNER_ID AND FIELD_ID = 2
WHERE 
SC.CLIENT_CONNECTED = 0
and SC.DISCONNECT_TIME <= DATE_SUB(NOW(),INTERVAL 14 DAY)
ORDER BY SYSTEM_NAME

Some notes:

  • As Hobbsy said, the custom fields are in the USER_FIELD_VALUE but his query doesn't limit the rows in that table to a specific custom field. In my query the join limits the rows to just FIELD_ID = 2 (department). You will need to create a similar join statement for your other custom fields that you want to include.
  • I simplified that where clause because that thing was crazy.



Comments:
  • Correction: Hobbsy does limit the rows to FIELD_ID = 1 in the where statement instead of the join statement. I prefer to do that in the join statement so that you can alias each join to match the custom field's name, making the query more human readable (in my opinion). - chucksteel 4 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ