All Assets with last logon user

[Moonbeam]

Hey everyone,

we are currently working on an integration for another system. For that, we need a report with a specific set of columns. I've built a report with the needed columns and the result is useless, since it returns around 1 million results Whereas the real result should give me back around 700 results (used the built-in report Windows: Last logon detected during scanning, per user ).

Here the statement:

Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.Domain,
tblADusers.Username As Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tsysOS.OSname As OS,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Mac,
tblADComputers.OU,
tblState.Statename As State,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan],
tblAssets.Lasttried As [Last scan attempt],
tblAssets.Description,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.FQDN,
tblAssetCustom.DNSName,
tblAssetCustom.LastPatched,
tblAssetCustom.LastFullbackup,
tblAssetCustom.LastFullimage,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
Case tblAssetCustom.PreventCleanup
When 0 Then 'No'
When 1 Then 'Yes'
End As PreventCleanup,
tblAssets.Scanserver,
tblADusers.email
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblMonitor On tblAssets.AssetID = tblMonitor.AssetID,
tblADusers
Order By tblAssets.AssetName

What I want to achieve is a list with every Asset listed and the last logon user with every specified column. I suppose it's just the fault of the Join statements but I can't understand it in a reasonable timeframe.

I've tried to just add the columns to the built-in report with no luck.

Thank you so much in advance

[Mister_Nobody]

You can try to use last logon subquery from this report

https://community.lansweeper.com/t5/product-discussions/report-need-last-user-login-from-specific-se...

[Moonbeam · Accepted solution]

I haven't tried the subquery since I just got it fixed with a coworker. It was just a nonexistent join between tblAssets and tblADusers with the username. Staring at the editor for so long can really make one blind.

Thank you for your reply.