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

Please log in to comment

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.
Please log in to comment
Answer this question or Comment on this question for clarity