→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎11-20-2014 04:13 PM
Select Top 1000000 AntiVirusUpToDate.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
AntiVirusUpToDate.Software,
AntiVirusUpToDate.Version,
AntiVirusUpToDate.Enabled,
AntiVirusUpToDate.Uptodate,
AntiVirusUpToDate.RetrievedFrom
From ((Select SoftwareComparison.AssetId As AssetID,
SoftwareComparison.software As Software,
SoftwareComparison.version As Version,
'software comparison' As RetrievedFrom, '' AS Enabled, '' AS UpToDate
From (Select tblSoftware.AssetID As AssetID,
tblSoftwareUni.softwareName As Software,
tblSoftware.softwareVersion As Version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) As SoftwareComparison)
Union
(Select tblAntivirus.AssetID As AssetID,
tblAntivirus.DisplayName As Software,
Null As Version,
'WMI' As RetrievedFrom, CASE WHEN tblAntivirus.onAccessScanningEnabled = 1 THEN 'Yes' ELSE 'No' END AS Enabled,
CASE WHEN tblAntivirus.productUpToDate = 1 THEN 'Yes' ELSE 'No' END AS UpToDate
From tblAntivirus)) As AntiVirusUpToDate
Inner Join tblAssetCustom On AntiVirusUpToDate.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = AntiVirusUpToDate.AssetId
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Where tblState.Statename = 'Active' And tblDomainroles.Domainrolename In
('Stand-alone server', 'Member server', 'Primary domain controller',
'Backup domain controller')
Order By tblAssets.Domain,
tblAssets.AssetName
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now