/build/static/layout/Breadcrumb_cap_w.png

Miscellaneous Question


I want to create a Report that would map user info taken from LDAP import and match with last user login from computer inventory

04/12/2016 860 views
Hi

I would like to be able to create a report that would map the last user login of the computer to the user name import through LDAP.

In the Computer Device inventory, we get a last logged on user, and in the Users which I have imported from Active Directory LDAP, I know the full name, and employee number (which I imported as Custom 2) of the user based on the last logged on user which is the user name account.

How can I write a query that would map this?

Computer Name - from computer inventory, Last logged on user - from computer inventory and we would use this as a key to look up the other information from the Users information taken from LDAP, Full Name - from Users information, employee number - Custom 2 in Users

I have never used SQL query before so I am not sure how to relate the 2 data together.  Please help.  Thank you.
0 Comments   [ + ] Show comments

Comments


All Answers

0
To relate two tables to each other you use a join statement. 

In this case you want to join to the user table based on the value in MACHINE.LAST_USER. Unfortunately that column includes the domain of the user in some cases so we'll have to deal with that along the way. Here's the query that I wrote that does this:
SELECT MACHINE.NAME, REPLACE(MACHINE.LAST_USER, "DOMAIN\\", "") as LASTUSER, USER.FULL_NAME, USER.CUSTOM_2 
FROM ORG1.MACHINE
LEFT JOIN USER on USER.USER_NAME = REPLACE(MACHINE.LAST_USER, "DOMAIN\\", "")
WHERE MACHINE.LAST_USER != ""
If you have not setup a tool like MySQL Workbench to look at the database I highly recommend it. The K1000's database structure is easy to follow and you'll get a much better understanding of how things work if you can look at the tables.

Answered 04/13/2016 by: chucksteel
Red Belt

  • Hi
    Thank you for the answer. I kind of figured it out after I wrote my question, and I was able to do with a JOIN. What is the difference between JOIN and LEFT JOIN?
    Also, I notice you are referencing ORG1.MACHINE directly, so it is possible to have a report include ORG2.MACHINE as well -- I mean to be able to include machines from both ORG1 and ORG2, and any other ORGs ?
    • Joins:
      https://www.quora.com/SQL-What-is-the-difference-between-inner-join-left-join-right-join-and-full-join

      I don't have multiple orgs so I can't be certain but I believe that the R1 user can only access the ORG1 tables. When you connect to the database with a tool like MySQL Workbench you can verify that.

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