→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !
‎11-12-2019 11:13 PM
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]
‎11-22-2019 09:06 PM
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]
‎11-14-2019 03:55 PM
‎11-14-2019 02:06 PM
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now