Greatest User

[FloBob]

Situation:
When logging into my network I record several key information about every computer. The only two fields that concern me at the moment are Username and Computername. I am trying to retrieve the user of each machine. My problem is sometimes users will log into machines that are not theirs. This situation can make my queries somewhat inacurate as I will explain below.

Sample data:

Username Computername
JSmith Comp1
TJones Comp2
TJones Comp1
JSmith Comp1


Perfect World:

What I would like is to individualise this information to the specific party in one complete sql statement. So that I can run the statment with each machine as the variable and allow it to pull the appropriate information for the user.

Example:

JSmith uses comp1
Tjones uses comp2

What I have tried so far:

Max() this seems to treat them as alphabetic characters and returns the user with the highest alpha/numeric value that has logged onto the computer.

Min() see Max()

Last() this seems to be the most accurate option I have, because it should be correct 90% of the time. Though it still has its inherent flaws as the primary user does not have to be the last user necessarily.

I have tried to look for information in both my help file and this website, if someone would please point me in a direction I would greatly appreciate it. Thank you.

 

[freakazeud]

Hi,
what do you want to achieve at the end? Query the last user who was recorded? Why would you use max on the username or computer name? I suggest not using last as it is inconsistent. Why don't you just add a datefield to the table and record the date and time the record was recorded and then max on that field? This way you will always get the exact results you are looking for!
HTH
Good luck

 

[FloBob]

Thank you for your reply, however the issue is not the last user that logs onto the machine but the user who logs onto the machine most. The primary user so to speak. I believe that using date would only allow me to find the last user who logged on which I can accomplish with last(). Thank you again do you have any other suggestions?

 

[freakazeud]

Ah...ok!
Mhh well you would need to do a count of how many times individual users have logged in. You can use Dcount to acomplish this. Then compare your results with each other and figure out which Dcount has the highest value. Then display that user based on these results.
This might be a little tricky if you have lots of users!
HTH
Good luck

 

[FloBob]

aye, I do.. the issue here is I would like it to be dynamic to populate both a form and a report. So that a computer inventory is constantly being updated. I would need solution that is not very cumbersome, I was hoping SQL had a function I had not been previously aware of. If anyone has any clue as to how to accomplish what I am asking, I would be in your debt.

 

[ScottGem]

What's not clear here is how you are recording logins. You should have a time stamp field that indicates the login time. You should also have a logout process and record that time as well.

Without that all you can tell is the number of times a person has logged in on a particular machine. A simply group by in User and computer will tell you that.

 

[FloBob]

Thanks Scott, I have tried group by queries but it does not produce one Username.. Maybe I haven't explained my problem with clarity. I would like a query that produces one username when I give it one computer. So that it will filter out all other usernames that might of logged onto the machine. If five people log onto a computer and I group that computers records into a query I will get five records. What I would like is it to return one record with just the username that logs into that computer most. Basically I would like to attach the computers to their users in a report. I have a lot of machines to do this with so manually getting this information is not really productive. I hope this clears up any confusion as to what I'm looking for. I greatly appreciate your interest and help. Thanks again.

Edit: This is by no means a security function for my network. It is simply to get a list of computers, their hardware, software, and some other information. Mostly for tax purposes. I dont really care when they log off, but I do care that something has changed since the last logon. What I have does do that rather well. The last and key step is to have it track what user is using that machine the most to attach it to a department, I cannot do that without knowing this information.

 

[ScottGem]

You need to use 2 queries then. Query One would list all the users for a specified computer grouped by user and counting the number or time of logins. Query Two would grab the Top 1 of Query One returning one record with the Username of the most frequent user.

 

[DragonCoder]

Either two queries as ScottGem mentioned or add a field with total time per login and run a query off the sum. Can't see any other way to do it.