Notification

Icon
Error

Custom All Assets Report Help - Custom all assets report not returning everything and duplicating others.

Posted: Tuesday, November 12, 2019 11:13:54 PM(UTC)
Matt Fuhrman

Matt Fuhrman

Member Original PosterPosts: 3
0
Like
Hello,

I created a custom report a while back to list all my assets with all the pertinent information I need (make, model, serial number, etc.) plus who it's used by and where it is located at. Last I checked it was pulling all my assets, except the one location asset that doesn't have a location as it's the main location asset all the others are rolled up under.

I haven't touched the report in a while and my technician that normally handles inventory has left us so I'm trying to pick it up again. I went through and cleaned up the assets as best I could to remove duplicates and things we no longer had etc. I then clicked on the assets menu option to show me all assets and info and exported that to excel. I then ran my custom report and exported it to excel and compared them.

They are pretty close, however I have 3 or 4 assets that are being duplicated in my report (i.e. show up as 2 or 3 rows exactly the same in my report but only once in the built in report) and 4 or 5 assets that are in the built in assets report but not in my custom report.

I am in no way a database person or SQL master but I know enough to be dangerous and can hack things together to get what I'm looking for. It's how I came up with the monstrosity of a query that I have below. I was hoping to get some help on where my report has gone screwy to cause these inconsistencies.

Any help on this will be much appreciated.

Thanks,
Matt

Code:
Select Top 1000000 a.icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  a.Description,
  tblState.Statename As State,
  a.AssetType As [Asset Type],
  a.IPAddress As [IP Address],
  tblAssets.Mac As [MAC Address],
  a.Manufacturer,
  a.Model,
  a.Serialnumber As [Serial #],
  a.Lastseen As [Last Seen],
  a.Lasttried As [Last Tried],
  u.Username As [Assigned To],
  a.AssetName As [Asset Location],
  tblAssetCustom.Custom1 As [IT Asset #],
  tblAssetCustom.Custom2 As [YHI Asset #]
From (Select Top 1000000 tblADusers.Username,
        tsysAssetRelationTypes.Name As Relation,
        tblAssets.AssetID As AssetID,
        tblAssets.AssetName
      From tblADusers
        Inner Join tblAssetUserRelations On tblADusers.Username =
          tblAssetUserRelations.Username And tblADusers.Userdomain =
          tblAssetUserRelations.Userdomain
        Inner Join tblAssets On
          tblAssets.AssetID = tblAssetUserRelations.AssetID
        Inner Join tsysAssetRelationTypes On
          tsysAssetRelationTypes.RelationTypeID = tblAssetUserRelations.Type
      Where tsysAssetRelationTypes.Name In ('used by', 'controlled by')
      Order By tblAssets.Userdomain,
        tblAssets.Username,
        tblAssets.AssetName) As u
  Right Join (Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
        tblAssets.AssetName As Asset,
        tblAssets.AssetID As AssetID,
        tsysAssetTypes.AssetTypename As AssetType,
        tblAssets.IPAddress,
        tblAssetCustom.Manufacturer,
        tblAssetCustom.Model,
        tblAssetCustom.Serialnumber,
        tblAssets.Lastseen,
        tblAssets.Lasttried,
        tsysAssetRelationTypes.Name As Relation,
        tblAssets1.AssetID As AssetID1,
        tblAssets1.AssetName,
        tblAssets1.Description
      From tblAssets
        Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
        Inner Join tblAssetRelations On tblAssetRelations.ChildAssetID =
          tblAssets.AssetID
        Inner Join tsysAssetRelationTypes On
          tsysAssetRelationTypes.RelationTypeID = tblAssetRelations.Type
        Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
          tblAssetRelations.ParentAssetID
        Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
      Where tsysAssetRelationTypes.Name = 'is located in'
      Order By tblAssets1.AssetName,
        tblAssets.Domain,
        Asset) As a On u.AssetID = a.AssetID
  Inner Join tblAssetCustom On tblAssetCustom.CustID = a.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblAssets On tblAssets.AssetID = tblAssetCustom.AssetID
Order By [Assigned To]
mwrobo09
#1mwrobo09 Member Posts: 52  
posted: 11/14/2019 2:06:11 PM(UTC)
Change the first line in the query from Select Top to Select Distinct

This has worked for me in the past.
Matt Fuhrman
#2Matt Fuhrman Member Original PosterPosts: 3  
posted: 11/14/2019 3:55:25 PM(UTC)
Thanks mwrobo09! That solved my duplication issue. Had to add Distinct in front of Top and it worked.

Now I just need to figure out why 7 assets don't show up in my custom report. I swear I've looked at everything about them and they look exactly the same as ones that are showing up in my custom report.
Matt Fuhrman
#3Matt Fuhrman Member Original PosterPosts: 3  
posted: 11/22/2019 9:06:57 PM(UTC)
So I was able to make a few minor changes to clean up the duplication issue I was having and remove a field I didn't need and add another one that I wanted. I'm still having the same issue of 7 Windows assets not showing up on this report for some reason. Brick wall

As I said before I'm not a SQL master, can someone explain to me what this query is doing and what would cause an asset to not be seen by it?

I'm just trying to figure out if something is ticked and shouldn't be or something needs to be ticked and isn't for these 7 assets. Thanks again for any help on this.

Here is my new code:

Code:
Select Distinct Top 1000000 a.icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblState.Statename As State,
  a.AssetType As [Asset Type],
  a.IPAddress As [IP Address],
  tblAssets.Mac As [MAC Address],
  a.Manufacturer,
  a.Model,
  a.Serialnumber As [Serial #],
  a.Lastseen As [Last Seen],
  a.Lasttried As [Last Tried],
  u.Username As [Assigned To],
  a.AssetName As [Asset Location],
  tblAssetCustom.Custom1 As [IT Asset #],
  tblAssetCustom.Custom2 As [YHI Asset #],
  tblAssetGroups.AssetGroup As [Group]
From (Select Top 1000000 tblADusers.Username,
        tsysAssetRelationTypes.Name As Relation,
        tblAssets.AssetID As AssetID,
        tblAssets.AssetName
      From tblADusers
        Inner Join tblAssetUserRelations On tblADusers.Username =
          tblAssetUserRelations.Username And tblADusers.Userdomain =
          tblAssetUserRelations.Userdomain
        Inner Join tblAssets On
          tblAssets.AssetID = tblAssetUserRelations.AssetID
        Inner Join tsysAssetRelationTypes On
          tsysAssetRelationTypes.RelationTypeID = tblAssetUserRelations.Type
      Where tsysAssetRelationTypes.Name In ('used by')
      Order By tblAssets.Userdomain,
        tblAssets.Username,
        tblAssets.AssetName) As u
  Right Join (Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
        tblAssets.AssetName As Asset,
        tblAssets.AssetID As AssetID,
        tsysAssetTypes.AssetTypename As AssetType,
        tblAssets.IPAddress,
        tblAssetCustom.Manufacturer,
        tblAssetCustom.Model,
        tblAssetCustom.Serialnumber,
        tblAssets.Lastseen,
        tblAssets.Lasttried,
        tsysAssetRelationTypes.Name As Relation,
        tblAssets1.AssetID As AssetID1,
        tblAssets1.AssetName,
        tblAssets1.Description
      From tblAssets
        Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
          tblAssets.Assettype
        Inner Join tblAssetRelations On tblAssetRelations.ChildAssetID =
          tblAssets.AssetID
        Inner Join tsysAssetRelationTypes On
          tsysAssetRelationTypes.RelationTypeID = tblAssetRelations.Type
        Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
          tblAssetRelations.ParentAssetID
        Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
      Where tsysAssetRelationTypes.Name = 'is located in'
      Order By tblAssets1.AssetName,
        tblAssets.Domain,
        Asset) As a On u.AssetID = a.AssetID
  Inner Join tblAssetCustom On tblAssetCustom.CustID = a.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblAssets On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
Where (tblAssetGroups.AssetGroup Like '%Location' Or
    tblAssetGroups.AssetGroup Like '%VDI' Or tblAssetGroups.AssetGroup
    Like '%Production' Or tblAssetGroups.AssetGroup Like '%Inventory')
Order By [Assigned To]

Active Discussions

Lansweeper Software Missing Report
by  RC62N   Go to last post Go to first unread
Last post: 12/6/2019 6:09:28 PM(UTC)
Lansweeper Assets without Asset Location
by  JLangthaler  
Go to last post Go to first unread
Last post: 12/5/2019 12:44:19 PM(UTC)
Lansweeper Installed Memory report
by  lansend   Go to last post Go to first unread
Last post: 12/2/2019 8:15:53 PM(UTC)
Lansweeper Custom OID Report
by  bramassendorp  
Go to last post Go to first unread
Last post: 12/2/2019 4:42:48 PM(UTC)
Lansweeper Report thats showing Windows machines when AV is not like =
by  RC62N   Go to last post Go to first unread
Last post: 11/28/2019 5:56:51 PM(UTC)
Lansweeper dhcp addresses available
by  Cesco93   Go to last post Go to first unread
Last post: 11/28/2019 2:56:02 PM(UTC)
Lansweeper All Assets Report on all IP-addresses and MAC-addresses
by  Tommy75  
Go to last post Go to first unread
Last post: 11/27/2019 11:54:44 AM(UTC)