Custom Report with AD Computer Name / Last User to Login

[EalTesIN]

Hello,

Can someone help me build a report to export all the active computer names and the last logged in user for each computer. Image below shows which fields I need, so ideally a column named computer and another column named user. This way I cab associate real names with computer names.

[rom]

Personally, I stay away from the relationships as it's super hard to report on if you want to join them to other tables to get data.  I'll spare the details.

I would also recommend getting the most logged in user, versus the last user, to determine who probably owns the computer.

Fortunately, this has already been provided here -  just remove the fields that you don't need:

https://community.lansweeper.com/t5/forum/most-logged-in-user-for-asset/m-p/32688

[rader]

I'd start with the built-in report: Assets: Asset to User relations.

Key in on the RelationType column (Used By) and see the answer in the RelationUser column.

The rest can be removed as you see fit.

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Case
    When tsysOS.osname Is Not Null Then tsysOS.osname
    When tblLinuxSystem.OSRelease Is Not Null Then tblLinuxSystem.OSRelease
    When tblMacOSInfo.KernelVersion Is Not Null Then tblMacOSInfo.KernelVersion
    When tsysAssetTypes.AssetType = -1 And Coalesce(tblSccmAsset.OsCaption,
      tblSccmAsset.OperatingSystemNameandVersion, '') <> '' Then
      Coalesce(tblSccmAsset.OsCaption,
      tblSccmAsset.OperatingSystemNameandVersion)
    Else ''
  End As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysAssetRelationTypes.RelationTypeIcon10 As Icon3,
  tsysAssetRelationTypes.Name As RelationType,
  tblAssetUserRelations.Comments As RelationComments,
  tblAssetUserRelations.StartDate As RelationStartDate,
  tblAssetUserRelations.EndDate As RelationEndDate,
  Case
    When tblAssetUserRelations.Upn Is Not Null Then tblAzureAdUser.DisplayName
    When tblAssetUserRelations.Userdomain Is Null Then ''
    When tblADusers.Displayname Is Null Or
      tblADusers.Displayname = '' Then tblAssetUserRelations.Userdomain + '\' +
      tblAssetUserRelations.Username
    Else tblADusers.Displayname
  End As hyperlink_name_RelationUser,
  Case
    When tblAssetUserRelations.Upn Is Not Null Then '/user.aspx?upn=' +
      tblAssetUserRelations.Upn
    When tblAssetUserRelations.Userdomain Is Null Then ''
    Else '/user.aspx?username=' + tblAssetUserRelations.Username +
      '&userdomain=' + tblAssetUserRelations.Userdomain
  End As hyperlink_RelationUser,
  Replace(tblAssetUserRelations.Ou, ',', ', ') As hyperlink_name_Ou,
  Case
    When tblAssetUserRelations.Ou Is Null Then ''
    Else '/Report/report.aspx?det=web50findusersbyOU&title=' +
      Replace(tblAssetUserRelations.Ou, '&', '%26') + '&@OU=' +
      Replace(tblAssetUserRelations.Ou, '&', '%26')
  End As hyperlink_Ou,
  tblADGroups.Name As [hyperlink_name_AD Group],
  Case
    When tblAssetUserRelations.AdObjectId Is Null Then ''
    Else '/Report/report.aspx?det=web50usersbyadgroup&title=' + tblADGroups.Name
      + '&@id=' + Cast(tblAssetUserRelations.AdObjectId As nvarchar(10))
  End As [hyperlink_AD Group],
  Case
    When tblAssetUserRelations.EndDate < GetDate() Then '#dddddd'
    Else '#ffffff'
  End As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetUserRelations On
      tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
      tsysAssetRelationTypes.RelationTypeID
  Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Outer Join tblADusers On
      tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
      tblADusers.Username = tblAssetUserRelations.Username
  Left Outer Join tblADGroups On tblADGroups.ADObjectID =
      tblAssetUserRelations.AdObjectId
  Left Outer Join tblAzureAdUser On tblAzureAdUser.UserPrincipalName =
      tblAssetUserRelations.Upn
  Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
Where tblState.Statename = 'Active'
Order By Case
    When tblAssetUserRelations.EndDate Is Null Then 1
    Else 0
  End Desc,
  RelationEndDate Desc,
  RelationStartDate Desc,
  tblAssets.AssetName,
  tblAssets.Domain