Last user column in software report

[g8riccg]

Hi everyone
I'm evaluating to buy a Premium Lansweeper license for the company I work (about 150 users) but I've a question:

- is there any chance to get a column with the "last user" in case I get a report in order to understand where a certain software has been installed?

I.E. I'd like to know which computers have Microsoft Office Professional 2010 installed but I need also to understand who use these computers (description field is blank).

Thank you for helping

G.

[kcovingt]

Thanks for letting me know why it didn't work and for cleaning it up. Worked like a champ and you guys are awesome as usual

[Hemoco]

You don't need tblCPlogoninfo, which stores all user logon events. The last logged on user is stored in tblAssets.Username/tblAssets.Userdomain. Also note that tblADusers must be linked to other tables on both the Username and Userdomain fields, as a username in itself is not unique. Modified query:

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers.Displayname,
tsysIPLocations.IPLocation,
tblSoftwareUni.softwareName,
tblAssets.Lastseen,
tsysOS.OSname
From tblAssets
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblSoftwareUni.softwareName Like 'ACME Software'
Order By tsysIPLocations.IPLocation,
tblAssets.AssetName

[kcovingt]

I'm trying to create the same kind of report as the one listed in this post but with the TblComputers table removed in the newer versions of Lansweeper I'm getting stuck.

Here is my attempt but for some machines I'm getting multiple users instead of just the last user that logged in. The last user logged in is the piece that is stumping me as I'm not sure what variable I need to reference.

Select Distinct Top 1000000 tblAssets.AssetName,
  tsysIPLocations.IPLocation,
  tblSoftwareUni.softwareName,
  tblAssets.Lastseen,
  tsysOS.OSname,
  tblADusers.Displayname
From tblAssets
  Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
    tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
  Inner Join tblADusers On tblCPlogoninfo.Username = tblADusers.Username
Where tblSoftwareUni.softwareName Like 'ACME Software'
Order By tsysIPLocations.IPLocation,
  tblAssets.AssetName

Thanks for the help!

[Hemoco · Accepted solution]

This question was answered through email, but we are posting the reply here as well should someone else need it.


An example report can be seen below. Replace “YourSoftware” with the name of the software package you would like to report on.

Select tblComputers.Computername, tblComputers.Computer, tblComputers.Domain,
tblComputers.Username As [Last User], tblSoftware.softwareName As
[Software Name], tblSoftware.softwareVersion As [Software Version]
From tblComputers Inner Join
tblSoftware On tblComputers.Computername = tblSoftware.ComputerName
Where tblSoftware.softwareName Like '%YourSoftware%'
Order By tblComputers.Domain, tblComputers.Computer, tblSoftware.softwareName,
tblSoftware.softwareVersion

To use the specified report, do the following:
• Open the Lansweeper configuration console and select Reports & Alerts\Report Builder. Hit the “New” button.
• Copy the SQL code provided and paste it at the bottom of the newly created report, replacing the default SQL code.
• Click somewhere near tblComputers so the new code applies.
• Give the report a “View name” and a “Report name” and hit the “Save” button.
• Double-click on the report in the report list to see its results and export options.