→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
danman
Engaged Sweeper III
I have a large list of both tbladusers.displayname and tbladusers.username currently in an excel spreadsheet. Is there a way to run a report against a list of usernames or displaynames to get what computer name they are currently logged into?

Thanks in advance!
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
You don't need to compare lists for this. Simply run the report below for the information you are after.
Select Top 1000000 Query1.Username, Query1.Domain As UserDomain, Query1.Firstname, Query1.Lastname, Query1.Name, Query1.Displayname, Query1.LastLogon, tblComputers.Computer, tblComputers.Domain As ComputerDomain From (Select tblCPlogoninfo.Username, tblCPlogoninfo.Domain, tblADusers.Firstname, tblADusers.Lastname, tblADusers.Name, tblADusers.Displayname, Max(tblCPlogoninfo.logontime) As LastLogon From tblCPlogoninfo Left Join tblADusers On tblADusers.Username = tblCPlogoninfo.Username And tblADusers.Userdomain = tblCPlogoninfo.Domain Group By tblCPlogoninfo.Username, tblCPlogoninfo.Domain, tblADusers.Firstname, tblADusers.Lastname, tblADusers.Name, tblADusers.Displayname) Query1 Inner Join tblCPlogoninfo On tblCPlogoninfo.logontime = Query1.LastLogon And tblCPlogoninfo.Domain = Query1.Domain And tblCPlogoninfo.Username = Query1.Username Inner Join tblComputers On tblComputers.Computername = tblCPlogoninfo.Computername Order By Query1.Domain, Query1.Username

View solution in original post

5 REPLIES 5
danman
Engaged Sweeper III
I wish they did, the list targets users in multiple OUs but the problem is that it does not target every user in the multiple OUs. So I can't do reports based solely on them. I'll work with comparing the spreadsheets. If there was some way to feed a text or cvs file in to get a report like this that'd be great though.

Thanks again for your time and help.
Hemoco
Lansweeper Alumni
danman wrote:
If there was some way to feed a text or cvs file in to get a report like this that'd be great though.

There is no built-in functionality in Lansweeper for this. This might help.
Hemoco
Lansweeper Alumni
You will either have to compare the two sheets or modify the report code. Do the users you wish to report on share a common trait that is scanned by Lansweeper? Do these users belong to a specific OU for instance? If so, the report can quite easily be modified.
danman
Engaged Sweeper III
Thank you but this provides a list of all the computers I have (5,000+). While this does give me the information I need, it also gives much more than I need. I have a list of 180~ names/ids that I need to have computer names associated to. How would I accomplish that with this? Is there no way to automate this through Lansweeper or would I have to compare the two spreadsheets against each other?

Thanks again for your time and help.
Hemoco
Lansweeper Alumni
You don't need to compare lists for this. Simply run the report below for the information you are after.
Select Top 1000000 Query1.Username, Query1.Domain As UserDomain, Query1.Firstname, Query1.Lastname, Query1.Name, Query1.Displayname, Query1.LastLogon, tblComputers.Computer, tblComputers.Domain As ComputerDomain From (Select tblCPlogoninfo.Username, tblCPlogoninfo.Domain, tblADusers.Firstname, tblADusers.Lastname, tblADusers.Name, tblADusers.Displayname, Max(tblCPlogoninfo.logontime) As LastLogon From tblCPlogoninfo Left Join tblADusers On tblADusers.Username = tblCPlogoninfo.Username And tblADusers.Userdomain = tblCPlogoninfo.Domain Group By tblCPlogoninfo.Username, tblCPlogoninfo.Domain, tblADusers.Firstname, tblADusers.Lastname, tblADusers.Name, tblADusers.Displayname) Query1 Inner Join tblCPlogoninfo On tblCPlogoninfo.logontime = Query1.LastLogon And tblCPlogoninfo.Domain = Query1.Domain And tblCPlogoninfo.Username = Query1.Username Inner Join tblComputers On tblComputers.Computername = tblCPlogoninfo.Computername Order By Query1.Domain, Query1.Username