/build/static/layout/Breadcrumb_cap_w.png
10/25/2017 511 views
I have an user LDAP label that I filter from my AD. It has 5 users. Let say the LDAP label name is 'Label1'
In Label Management, I can see 5 users, each has at least one assigned device. Four users has one assigned devices, one user has four assigned devices
I want to create a report that show a list of devices which users of "Label1" are assigned to.

I contact TechSupport and they said it is a custom report with SQL.

If any expert out there has with SQL experience, I appreciate your suggestion.

0 Comments   [ + ] Show comments

Comments



Community Chosen Answer

1
Hello @ridesharing - try this:

Create a new SQL report and enter this at SQL:

SELECT    MACHINE.NAME,
          MACHINE.USER

FROM      MACHINE

JOIN      USER U1 on U1.USER_NAME = MACHINE.USER
JOIN      USER_LABEL_JT L1 on L1.USER_ID = U1.ID
JOIN      LABEL L2 on L2.ID = L1.LABEL_ID

WHERE L2.NAME = 'Label1'

ORDER BY USER ASC


cheers

Answered 11/02/2017 by: svmay
Red Belt

All Answers

0

Hi svmay,

I got this error when trying.

I am new to SQL report. What should I define L1 and U1 ? or it comes from KACE default databases ?

Thank you for this sql script.

-------------------------------------

mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM MACHINE JOIN USER U1 on U1.USER_NAME = MACHINE.USER JOIN ' at line 4] in EXECUTE( "SELECT MACHINE.NAME, MACHINE.USER, FROM MACHINE JOIN USER U1 on U1.USER_NAME = MACHINE.USER JOIN USER_LABEL_JT L1 on L1.USER_ID = U1.ID JOIN LABEL L2 on L2.ID = L1.LABEL_ID WHERE L2.NAME = 'Label1' ORDER BY USER ASC LIMIT 0")

-----------------------------------

Answered 11/06/2017 by: ridesharing
White Belt

  • Hi ridesharing

    Sorry, I didn't read the answer before.
    I have an error in the SQL code - there is a comma set incorrectly. I fixed the code in the answer above. Please try it again.