→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mfuhrman
Engaged Sweeper
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]
3 REPLIES 3
mfuhrman
Engaged Sweeper
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
Engaged Sweeper
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
Champion Sweeper
Change the first line in the query from Select Top to Select Distinct

This has worked for me in the past.