Add Antivirus Software

[wayneRex]

Hello

Ive created a report which shows All Domain Controllers. I would like to add a column which shows what Antivirus each controller has.

I have tried to copy this report, but i can't integrate it into my code. It has a subquery called unioned in it.

https://www.lansweeper.com/forum/yaf_postst10247_Antivirus-Report-For-systems.aspx#post38755

Can anyone help me add Antivirus?

Thank you

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname As OS,
tblAssets.Domain,
tsysIPLocations.IPLocation,
tblAssets.NrProcessors As [No. of Processors],
tblProcessor.Name,
tblAssets.Memory,
Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric))
As [Total size (GB)],
Sum(Cast(Cast(tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric)) As [Free in GB],
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As UptimeSinceLastReboot,
tblAssets.Lastseen
From tblComputersystem
Inner Join tblAssets On tblComputersystem.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblProcessor On tblAssets.AssetID = tblProcessor.AssetID,
tsysantivirus
Where tblDomainroles.Domainrolename In ('Backup Domain Controller',
'Primary Domain Controller') And tblAssetCustom.State = 1
Group By tsysOS.Image,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS.OSname,
tblAssets.Domain,
tsysIPLocations.IPLocation,
tblAssets.NrProcessors,
tblProcessor.Name,
tblAssets.Memory,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes',
tblAssets.Lastseen
Order By tblAssets.AssetName

[pryan67]

Very odd...I've been playing with this and as soon as I add the anti-virus, it shows no results...but if I get rid of the "where", it shows all workstations (but no servers) Strange. I'll keep playing though

[wayneRex]

pryan67 wrote:
Very odd...I've been playing with this and as soon as I add the anti-virus, it shows no results...but if I get rid of the "where", it shows all workstations (but no servers) Strange. I'll keep playing though

Great, thank you for taking a look

[pryan67]

wayneRex wrote:

pryan67 wrote:
Very odd...I've been playing with this and as soon as I add the anti-virus, it shows no results...but if I get rid of the "where", it shows all workstations (but no servers) Strange. I'll keep playing though

Great, thank you for taking a look

Give this a shot

Select Top 1000000 unioned.assetid,
tblAssets.AssetName,
tsysOS.Image As icon,
unioned.software,
unioned.version,
unioned.Enabled,
unioned.Uptodate,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblDomainroles.Domainrolename
From ((Select a.assetid As assetid,
a.software As software,
a.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) a)
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)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Where tblDomainroles.Domainrolename In ('Backup Domain Controller',
'Primary Domain Controller') And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[wayneRex]

pryan67 wrote:

wayneRex wrote:

pryan67 wrote:
Very odd...I've been playing with this and as soon as I add the anti-virus, it shows no results...but if I get rid of the "where", it shows all workstations (but no servers) Strange. I'll keep playing though

Great, thank you for taking a look

Give this a shot

Select Top 1000000 unioned.assetid,
tblAssets.AssetName,
tsysOS.Image As icon,
unioned.software,
unioned.version,
unioned.Enabled,
unioned.Uptodate,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblDomainroles.Domainrolename
From ((Select a.assetid As assetid,
a.software As software,
a.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) a)
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)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Where tblDomainroles.Domainrolename In ('Backup Domain Controller',
'Primary Domain Controller') And tblAssetCustom.State = 1
Order By tblAssets.AssetName

Looks good to me, thank you so much