Report user logon by unique Assets

[SPAMHATER007]

Want report of each asset a user has logged onto.

Here is the report I have so far, based on an existing report, just only want to list an asset once instead of everytime user logged on. Right now I export into Excel and run Pivot table.

Select Top 1000000 tblCPlogoninfo.Username,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblCPlogoninfo.Domain As Userdomain,
tblCPlogoninfo.logontime As LogonTime
From tblAssets
Inner Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblState.Statename = 'Active'
Order By tblCPlogoninfo.Username,
tblAssets.AssetName,
tblAssets.Domain,
LogonTime Desc



Thanks, Tony

[AZHockeyNut]

I think this is close....stress the "think" part. 🙂

Select Top 1000000 t.Username,
t.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
t.Domain As Userdomain,
t.logontime As LogonTime
from (select logontime, username, domain, assetid, row_number() over(partition by assetid order by logontime desc) as rn
from tblCPlogoninfo) as T
inner join tblassets on tblassets.assetid = t.assetid
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblState.Statename = 'Active' and rn = 1
order by t.Username,
tblAssets.AssetName,
tblAssets.Domain,
LogonTime