Notification

Icon
Error

Duplicate Servers without AV - I would like a report that will tell me my servers without AV and not have duplicates

Posted: Monday, July 27, 2020 4:56:20 PM(UTC)
ddanks

ddanks

Member Original PosterPosts: 2
0
Like
Hello, I am using the built in lansweeper report for servers without anti-virus. However when I do that I am getting some duplicate machines. Any chance someone with more knowledge would be able to help me?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
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 tblState.Statename = 'Active' And
tblDomainroles.Domainrolename In ('Stand-alone server', 'Member server',
'Primary domain controller', 'Backup domain controller')
Order By tblAssets.AssetName


Thank you!
RC62N
#1RC62N Member Posts: 455  
posted: 7/28/2020 3:11:03 PM(UTC)
Your query looks like it should work, and in fact it does on my inventory. Consider making a copy of the query and dropping joined tables one-by-one until you stop seeing duplicates to try to identify which is causing the dupes to be produced, then focus on that table to figure out why.

Active Discussions

Installer Windows 10 Upgrade
by  CyberCitizen  
Go to last post Go to first unread
Last post: 7/8/2020 1:07:47 AM(UTC)
Installer Installer - Microsoft Office
by  Florian_Eigsi   Go to last post Go to first unread
Last post: 6/29/2020 3:33:20 PM(UTC)
Installer Firefox ESR 68.8.0 MSI Installer
by  PLSJohnJohn  
Go to last post Go to first unread
Last post: 5/11/2020 4:33:50 PM(UTC)
Installer Update Chrome browser with GoogleUpdate.exe ?
by  Inna Ptushkina   Go to last post Go to first unread
Last post: 5/4/2020 8:39:29 PM(UTC)
Installer Bios Update for Dell all in one
by  Florian_Eigsi  
Go to last post Go to first unread
Last post: 4/2/2020 11:36:43 AM(UTC)
Installer Uninstall - Adobe Acrobat 9x
by  palemmo   Go to last post Go to first unread
Last post: 3/25/2020 7:25:05 PM(UTC)
Installer OneDrive Silent Install
by  Steven.C  
Go to last post Go to first unread
Last post: 3/13/2020 2:55:19 PM(UTC)