Force Report To Only Include Results In IP Range?

[RTI_IT]

Hello,

I have a report that shows computers that are missing the Firewall Single Sign On agent and I was wondering if there was a way to make it so that it only shows computers who are in the DHCP pool?

Our pool is 192.168.90.1 - 192.168.92.254

Current Report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tsysOS.Image As icon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like 'WatchGuard Authentication Client 11.9.4')
And tblAssetCustom.State = 1
Order By tblAssets.AssetName

Thanks!
Ed

[RTI_IT]

Thank you!! The report works perfectly now!

[MakeBug]

Thank you too...I tried to use this table at first. But I couldn't figure out, how to link any device-info from tblAssets or tblAssetsCustom to this one (I didn't know that it is possible to create direct links from any table...that would have solved a lot of my problems during the setup of our inventory ).

[Daniel_B · Accepted solution]

@MakeBug, thank you, this is a good way. However, we recommend using the system table tsysIPlocations for this purpose, as outlined here.

Select Top 1000000 tblAssets.AssetID,
 tblAssets.AssetUnique,
 tblAssets.Domain,
 tsysOS.OSname,
 tblAssets.Description,
 tsysOS.Image As icon
From tblAssets
 Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
 Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
 Inner Join tsysIPlocations On tblAssets.IPNumeric Between
 tsysIPlocations.StartIP and tsysIPlocations.EndIP
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
 From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
 tblSoftware.softID
 Where
 tblSoftwareUni.softwareName Like 'WatchGuard Authentication Client 11.9.4')
 And tblAssetCustom.State = 1 And tsysIPlocations.IPLocation like
 '%[IP Location Name]%'
Order By tblAssets.AssetName

In case it is not possible to create a separate IP location for these servers (i.e. because they are already in another IP location with a larger range), you are able to filter directly on tblAssets.IPNumeric:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Description,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where
    tblSoftwareUni.softwareName Like 'WatchGuard Authentication Client 11.9.4')
  And tblAssetCustom.State = 1 And tblAssets.IPNumeric Between 192168090001 And
  192168092254
Order By tblAssets.AssetName

[MakeBug]

First create an IP-Location for this range. Then you can just add a filter into the report for this IP-Location.

The report should look somehow like this:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblAssets.Description,
tsysOS.Image As icon
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join web40repIPLocationlist On tblAssets.AssetID =
web40repIPLocationlist.AssetID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where
tblSoftwareUni.softwareName Like 'WatchGuard Authentication Client 11.9.4')
And tblAssetCustom.State = 1 And web40repIPLocationlist.IPLocation =
' [IP Location Name] '
Order By tblAssets.AssetName