‎03-16-2018 12:15 PM
‎03-22-2018 04:54 PM
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetType,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
tsysAssetTypes.AssetTypename = 'Location'
And tblAssetCustom.State = 1
Select Top 1000000If the asset location is correctly reported, build on that. If the asset location doesn't show up, maybe you're not recording it in the field you think you are.
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
al.AssetName As AssetLocation
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetRelations As ar On ar.ChildAssetID = tblAssets.AssetID
Left Join tblAssets As al On ar.ParentAssetID = al.AssetID And al.Assettype = 66
Where
tblAssetCustom.State = 1
‎03-21-2018 04:10 PM
‎03-19-2018 04:49 PM
Select Top 1000000
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Location,
tsysOS.OSname,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
--tblAssetCustom.Location As Location1,
al.AssetName AS AssetLocation
From
tblAssets
Left Outer Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
LEFT JOIN tsysOS On tblAssets.OScode = tsysOS.OScode
LEFT JOIN tblAssetRelations AS ar ON ar.ChildAssetID=tblAssets.AssetID
LEFT JOIN tblAssets AS al ON ar.ParentAssetID=al.AssetID AND al.Assettype=66
Where
tsysOS.OSname Like '%Not scanned%'
And tblOperatingsystem.AssetID Is Null
And tblAssetCustom.State = 1
And tblAssets.Assettype = -1
Order By
tblAssets.AssetName,
Cast(tblAssets.Lastseen - tblAssets.Firstseen As bigint) Desc
‎03-21-2018 08:13 AM
RC62N wrote:
Depending on your needs, an INNER JOIN may be all that's required against tsysOS, but I made it a LEFT JOIN just to be sure nothing got filtered out that way.Select Top 1000000
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssetCustom.Location,
tsysOS.OSname,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
--tblAssetCustom.Location As Location1,
al.AssetName AS AssetLocation
From
tblAssets
Left Outer Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
LEFT JOIN tsysOS On tblAssets.OScode = tsysOS.OScode
LEFT JOIN tblAssetRelations AS ar ON ar.ChildAssetID=tblAssets.AssetID
LEFT JOIN tblAssets AS al ON ar.ParentAssetID=al.AssetID AND al.Assettype=66
Where
tsysOS.OSname Like '%Not scanned%'
And tblOperatingsystem.AssetID Is Null
And tblAssetCustom.State = 1
And tblAssets.Assettype = -1
Order By
tblAssets.AssetName,
Cast(tblAssets.Lastseen - tblAssets.Firstseen As bigint) Desc
As a test on my side, I removed the first two conditions from the WHERE clause to get results from my inventory. The asset location was part of the results, as expected.
For sorting, if all you're wanting is the number of days between first and last seen, you might consider using DateDiff(d, tblAssets.Firstseen, tblAssets.Lastseen).
‎03-19-2018 03:24 AM
‎03-16-2018 07:29 PM
LEFT JOIN tblAssetRelations AS ar ON ar.ChildAssetID=tblAssets.AssetID
LEFT JOIN tblAssets AS al ON ar.ParentAssetID=al.AssetID AND al.Assettype=66
al.AssetName AS AssetLocation
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now