I want to create a Report that would map user info taken from LDAP import and match with last user login from computer inventory
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
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
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.
Comments:
-
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 ? - tuyen 8 years ago-
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. - chucksteel 8 years ago
