/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Find devices which assigned to a LDAP user group

10/25/2017 609 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.

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