
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-01-2020 12:26 PM
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
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
Labels:
- Labels:
-
Report Center
4 REPLIES 4

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-05-2020 10:49 PM
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-06-2020 01:04 PM
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-06-2020 04:45 PM
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-07-2020 11:34 AM
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
