Adding CORRECT Last User to a Report

[paulruvalcaba]

I would like the added luxury to include the Last User's First and last name to each of the reports I make.

I am trying to add it to this report for mapped drives.
=========================================================

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.IPAddress,
tblMappedDrives.Driveletter,
tblMappedDrives.RemotePath
From tblAssets
Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Order By tblAssets.AssetName,
tblMappedDrives.Driveletter

=========================================================


Yes, I have the username but I cannot figure out how to get the Last User to work.
I have tried,
htblusers.name,
web50repuseraduserattributes.Firstname,
web50repuseraduserattributes.Lastname

[Esben_D]

As far as mapped drives info, the table contains information about a Windows computer's mapped drives. Only persistent mapped drives found in the client machine's registry under HKEY_CURRENT_USER\Network are scanned.

In the report above though, you're looking at the mapped drives of that computer, not necessarily of the user listed, I think you can only see the user linked to the mapped drive from tblMappedDrives.Username. However, this can't be mapped to tbladusers, because the username format for the two tables is different.

tbladusers.username = Username
tblmappeddrives.username = Domain\Username

There is a way to go around this by using the substring of the mapped drive table to join the two tables. Depending on the length of you domain name you'll have to modify the report. In my test case, the domain has two letters, so the value in tblmappeddrives.username = AB\John.Doe
To just get John.Doe back I had to let the substring start at position 4 (length of the substring is 20 characters, you can increase this if you have long usernames)

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.IPAddress,
tblMappedDrives.Driveletter,
tblMappedDrives.RemotePath,
From tblAssets
Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADusers On
tblADusers.Username = SubString(tblMappedDrives.Username, 4, 20)
Where tblAssets.Username = SubString(tblMappedDrives.Username, 4, 20)
Order By tblAssets.AssetName,
tblMappedDrives.Driveletter

[paulruvalcaba · Accepted solution]

Thanks for your help

I figured it out:


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.IPAddress,
tblMappedDrives.Driveletter,
tblMappedDrives.RemotePath
From tblAssets
Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADusers On
tblADusers.Username = SubString(tblMappedDrives.Username, 9, 20)
Where tblAssets.Username = SubString(tblMappedDrives.Username, 9, 20) And
tblMappedDrives.Driveletter != 'X:' And tblMappedDrives.Driveletter != 'Z:'
Order By tblAssets.AssetName,
tblMappedDrives.Driveletter

Charles.X wrote:
As far as mapped drives info, the table contains information about a Windows computer's mapped drives. Only persistent mapped drives found in the client machine's registry under HKEY_CURRENT_USER\Network are scanned.

In the report above though, you're looking at the mapped drives of that computer, not necessarily of the user listed, I think you can only see the user linked to the mapped drive from tblMappedDrives.Username. However, this can't be mapped to tbladusers, because the username format for the two tables is different.

tbladusers.username = Username
tblmappeddrives.username = Domain\Username

There is a way to go around this by using the substring of the mapped drive table to join the two tables. Depending on the length of you domain name you'll have to modify the report. In my test case, the domain has two letters, so the value in tblmappeddrives.username = AB\John.Doe
To just get John.Doe back I had to let the substring start at position 4 (length of the substring is 20 characters, you can increase this if you have long usernames)

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.IPAddress,
tblMappedDrives.Driveletter,
tblMappedDrives.RemotePath,
From tblAssets
Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADusers On
tblADusers.Username = SubString(tblMappedDrives.Username, 4, 20)
Where tblAssets.Username = SubString(tblMappedDrives.Username, 4, 20)
Order By tblAssets.AssetName,
tblMappedDrives.Driveletter

[paulruvalcaba]

After a quick google search to figure out how the substring line works I changed the 4 to a 9 and it worked.

Now what I'm trying to do is show only the results that do not include the X or the Z drive.
Is that an Except clause before the Order By?

Charles.X wrote:
As far as mapped drives info, the table contains information about a Windows computer's mapped drives. Only persistent mapped drives found in the client machine's registry under HKEY_CURRENT_USER\Network are scanned.

In the report above though, you're looking at the mapped drives of that computer, not necessarily of the user listed, I think you can only see the user linked to the mapped drive from tblMappedDrives.Username. However, this can't be mapped to tbladusers, because the username format for the two tables is different.

tbladusers.username = Username
tblmappeddrives.username = Domain\Username

There is a way to go around this by using the substring of the mapped drive table to join the two tables. Depending on the length of you domain name you'll have to modify the report. In my test case, the domain has two letters, so the value in tblmappeddrives.username = AB\John.Doe
To just get John.Doe back I had to let the substring start at position 4 (length of the substring is 20 characters, you can increase this if you have long usernames)

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.IPAddress,
tblMappedDrives.Driveletter,
tblMappedDrives.RemotePath,
From tblAssets
Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADusers On
tblADusers.Username = SubString(tblMappedDrives.Username, 4, 20)
Where tblAssets.Username = SubString(tblMappedDrives.Username, 4, 20)
Order By tblAssets.AssetName,
tblMappedDrives.Driveletter

[paulruvalcaba]

I removed the comma after RemotePath to get the code to not give a syntax error but it retrieved 0 results.

Our domain is 7 characters and usernames are basically the first name and a 4 digit number, so that's roughly at least 8 characters and up to 15 or so depending on the name.

Charles.X wrote:
As far as mapped drives info, the table contains information about a Windows computer's mapped drives. Only persistent mapped drives found in the client machine's registry under HKEY_CURRENT_USER\Network are scanned.

In the report above though, you're looking at the mapped drives of that computer, not necessarily of the user listed, I think you can only see the user linked to the mapped drive from tblMappedDrives.Username. However, this can't be mapped to tbladusers, because the username format for the two tables is different.

tbladusers.username = Username
tblmappeddrives.username = Domain\Username

There is a way to go around this by using the substring of the mapped drive table to join the two tables. Depending on the length of you domain name you'll have to modify the report. In my test case, the domain has two letters, so the value in tblmappeddrives.username = AB\John.Doe
To just get John.Doe back I had to let the substring start at position 4 (length of the substring is 20 characters, you can increase this if you have long usernames)

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblAssets.IPAddress,
tblMappedDrives.Driveletter,
tblMappedDrives.RemotePath,
From tblAssets
Inner Join tblMappedDrives On tblAssets.AssetID = tblMappedDrives.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblADusers On
tblADusers.Username = SubString(tblMappedDrives.Username, 4, 20)
Where tblAssets.Username = SubString(tblMappedDrives.Username, 4, 20)
Order By tblAssets.AssetName,
tblMappedDrives.Driveletter

[Esben_D]

Add the table tbladusers and the following fields:

tblADusers.firstname
tblADusers.lastname

[paulruvalcaba]

Charles.X wrote:
Add the table tbladusers and the following fields:

tblADusers.firstname
tblADusers.lastname

For some strange reason that did not work. It gives me a list of every user and attaches them to the Asset even though they have never logged into that asset before. I'm thinking it just connects the users who also have that drive mapped.

As a group policy every users has an X and Z drive mapped when they log into any workstation. So something with the code or the way lansweeper is relating the information is not allowing ONLY users who have logged into that workstation to show up.

Any help?