Custom All Assets Report Help

[mfuhrman]

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

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]

[mfuhrman]

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.

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:

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]

[mfuhrman]

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.

[mwrobo09]

Change the first line in the query from Select Top to Select Distinct

This has worked for me in the past.