‎03-23-2022 04:40 PM
Select Top 1000000 tblassets.AssetID As AssetID1,
tblassets.AssetName As AssetName1,
tsysassettypes.AssetTypename As AssetTypename1,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress As IPAddress1,
tblassets.Lastseen As Lastseen1,
tblassets.Lasttried As Lasttried1,
tblAntivirus.AntivirusId,
tblAntivirus.DisplayName,
tblAntivirus.AssetID,
tblAntivirus.ProductState,
tblAntivirus.LastChanged,
tblAntivirus.onAccessScanningEnabled,
tblAntivirus.productUpToDate
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tblAntivirus On tblassets.AssetID = tblAntivirus.AssetID
Where tblAntivirus.DisplayName Like '%Windows Defender%' And
tblAntivirus.DisplayName Not Like '%Sophos%' And
tblAntivirus.DisplayName Not Like '%Sentinel%' And tblassetcustom.State = 1
Select Top (1000000) tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan]
From tblAssets
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tsysIPLocations.StartIP <=
tblAssets.IPNumeric And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID Inner Join tsysantivirus On
tblSoftwareUni.softwareName Like tsysantivirus.Software) And
tblAssets.AssetID Not In (Select tblAntivirus.AssetID
From tblAntivirus) And tblComputersystem.Domainrole <= 1 And
tblAssetCustom.State = 1
Order By tblAssets.AssetName
‎03-25-2022 03:56 PM
Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
Case
When AV.AssetID Is Not Null Then 'Yes'
Else 'No'
End As AV,
AV.DisplayName
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Left Join (Select tblAntivirus.AssetID,
tblAntivirus.DisplayName
From lansweeperdb.dbo.tblAntivirus
Where tblAntivirus.DisplayName = 'Windows Defender') AV On
tblassets.AssetID = AV.AssetID
Where tsysassettypes.AssetTypename = 'Windows' And tblassetcustom.State = 1
Order By AV
‎03-25-2022 10:43 PM
Andy.S wrote:
Hi, Give this a try , I wrote it very quickly so may need a tweak or two ?Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
Case
When AV.AssetID Is Not Null Then 'Yes'
Else 'No'
End As AV,
AV.DisplayName
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Left Join (Select tblAntivirus.AssetID,
tblAntivirus.DisplayName
From lansweeperdb.dbo.tblAntivirus
Where tblAntivirus.DisplayName = 'Windows Defender') AV On
tblassets.AssetID = AV.AssetID
Where tsysassettypes.AssetTypename = 'Windows' And tblassetcustom.State = 1
Order By AV
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now