→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
RTI_IT
Champion Sweeper
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
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
@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

View solution in original post

4 REPLIES 4
RTI_IT
Champion Sweeper
Thank you!! The report works perfectly now!
MakeBug
Champion Sweeper
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
Lansweeper Alumni
@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
Champion Sweeper
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