I have an Asset Usage Report, which does basically everything I want, with one exception. It does not take into account disconnected sessions. As an example, if UserA logs into a machine, and disconnects, then UserB logs in to that same machine afterwards, the usage report does not show UserB having logged in at all. It shows the login time of UserA. I know that in the Event Log, we can see events for logins relating to Disconnected sessions, so I have to assume this information is already collected by LanSweeper, if I knew how to find it.
Here is the report as it is currently written:
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblAssets.IPAddress,
tblAssetCustom.Model,
Case When tblAssetCustom.Manufacturer Like '%vmware%' Then tblVmwareGuest.Name
When tblAssetCustom.Manufacturer Like '%microsoft%' Then tblHyperVGuest.Name
Else tblAssets.AssetName End As Server_Name,
Case When tblAssetCustom.Manufacturer Like '%vmware%' Then 'Virtual'
When tblAssetCustom.Manufacturer Like '%microsoft%' Then 'Virtual'
Else 'Physical' End As Type,
Case
When tblAssetCustom.Manufacturer Like '%vmware%' Then
Coalesce(tblVmwareInfo.Version, tblOperatingsystem.Caption)
When tblAssetCustom.Manufacturer Like '%microsoft%' Then
Coalesce(tblVmwareInfo.Version, tblOperatingsystem.Caption) Else 'NA'
End As [Host Version],
tsysAssetTypes.AssetTypeIcon10 As icon,
Max(SubQuery.Username) As [Last Logged On User],
Max(SubQuery.Domain) As Domain,
Max(SubQuery.LastLogon) As [Last Logon],
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tblVmwareGuest On tblAssets.AssetID = tblVmwareGuest.AssetID
Left Join tblHyperVGuest On tblAssets.AssetID = tblHyperVGuest.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblVmwareInfo On tblAssets.AssetID = tblVmwareInfo.AssetID
Left Join tblCPlogoninfo On tblAssets.AssetID = tblCPlogoninfo.AssetID
Left Join (Select Top 1000000 tblCPlogoninfo.Username,
tblCPlogoninfo.Domain,
Max(tblCPlogoninfo.logontime) As LastLogon
From tblCPlogoninfo
Group By tblCPlogoninfo.Username,
tblCPlogoninfo.Domain) SubQuery On tblCPlogoninfo.Username =
SubQuery.Username And tblCPlogoninfo.Domain = SubQuery.Domain And
tblCPlogoninfo.logontime = SubQuery.LastLogon
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tblAssets.IPAddress,
tblAssetCustom.Model,
Case When tblAssetCustom.Manufacturer Like '%vmware%' Then tblVmwareGuest.Name
When tblAssetCustom.Manufacturer Like '%microsoft%' Then tblHyperVGuest.Name
Else tblAssets.AssetName End,
Case When tblAssetCustom.Manufacturer Like '%vmware%' Then 'Virtual'
When tblAssetCustom.Manufacturer Like '%microsoft%' Then 'Virtual'
Else 'Physical' End,
Case
When tblAssetCustom.Manufacturer Like '%vmware%' Then
Coalesce(tblVmwareInfo.Version, tblOperatingsystem.Caption)
When tblAssetCustom.Manufacturer Like '%microsoft%' Then
Coalesce(tblVmwareInfo.Version, tblOperatingsystem.Caption) Else 'NA' End,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.Lastseen,
tsysAssetTypes.AssetTypename
Order By tblAssets.AssetName,
[Last Logon]