Asset to Users Relations Audit Report Not Working

[dshuttleworth]

Hi,

Long time Lansweeper business and need to get more out of the product in terms of asset to user relations. We recently fed back that there are far too many clicks involved to assign an asset to a user but that aside, want to report on those assignments.

I've used the following report from the library (https://www.lansweeper.com/resources/report/active-directory-users/asset-to-users-relations-audit/) but having just assigned a device to a user (via 'Used by') this does not appear in the report. I can't see why there would be any delay in this information appearing but first question I suppose is: is there a delay in this appearing on the report?

Second question if there is no delay: can someone test the behaviour in their environment / Lansweeper test env?

Thanks

[Katgroup]

There shouldn't be any delay; if there were, the entire report would be blocked while loading.

In my testing, the UsedBy column seems to work fine. I'm interested to see if anyone else has the same issue.

[dshuttleworth · Accepted solution]

Thanks for testing! The Lansweeper support team provided me with an updated report via attachment that has solved the issue - it looks as though the issue in our environment may have been to do with the way names are concatenated in the library report. 

The report that support provided was this:

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.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.Userdomain Is Null Then ''
    Else '/user.aspx?username=' + tblAssetUserRelations.Username +
      '&userdomain=' + tblAssetUserRelations.Userdomain
  End As hyperlink_RelationUser,
  Replace(tblADusers.Ou, ',', ', ') As hyperlink_name_Ou,
  '/Report/report.aspx?det=web50findusersbyOU&title=' + Replace(tblADusers.Ou,
  '&', '%26') + '&@OU=' + Replace(tblADusers.Ou, '&', '%26') 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 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