cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
brodiemac2
Champion Sweeper
Need help modifying the lsagent not seen in 7 days report to show only active devices. Any help is always appreciated.
1 ACCEPTED SOLUTION
brandon_jones
Champion Sweeper III
This should work.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As [Asset Name],
tblLsAgentGroup.Name As [LsAgent Group],
tblAssets.LastLsAgent As [Last LsAgent Scan],
tblAssets.LsAgentVersion,
tblLsAgentAssetState.statename As Status,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress As [Last known IP],
tsysOS.OSname As OS,
tblAssets.SP As [Service Pack],
tblAssets.Description
From tblAssets
Inner Join tblLsAgentAsset On tblLsAgentAsset.AssetID = tblAssets.AssetID
Inner Join tblLsAgentAssetState On tblLsAgentAssetState.id =
tblLsAgentAsset.Status
Inner Join tblLsAgentGroup On tblLsAgentGroup.LsAgentGroupID =
tblLsAgentAsset.LsAgentGroupID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join lansweeperdb.dbo.tblAssetCustom On tblAssets.AssetID =
tblAssetCustom.AssetID
Where tblAssets.LastLsAgent < GetDate() - 7 And tblAssetCustom.State = 1
Order By [Last LsAgent Scan] Desc,
[Asset Name]

View solution in original post

2 REPLIES 2
brodiemac2
Champion Sweeper
Perfect, thank you!
brandon_jones
Champion Sweeper III
This should work.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As [Asset Name],
tblLsAgentGroup.Name As [LsAgent Group],
tblAssets.LastLsAgent As [Last LsAgent Scan],
tblAssets.LsAgentVersion,
tblLsAgentAssetState.statename As Status,
tblAssets.Domain,
tblAssets.Username,
tblAssets.IPAddress As [Last known IP],
tsysOS.OSname As OS,
tblAssets.SP As [Service Pack],
tblAssets.Description
From tblAssets
Inner Join tblLsAgentAsset On tblLsAgentAsset.AssetID = tblAssets.AssetID
Inner Join tblLsAgentAssetState On tblLsAgentAssetState.id =
tblLsAgentAsset.Status
Inner Join tblLsAgentGroup On tblLsAgentGroup.LsAgentGroupID =
tblLsAgentAsset.LsAgentGroupID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join lansweeperdb.dbo.tblAssetCustom On tblAssets.AssetID =
tblAssetCustom.AssetID
Where tblAssets.LastLsAgent < GetDate() - 7 And tblAssetCustom.State = 1
Order By [Last LsAgent Scan] Desc,
[Asset Name]