Mulitple asset locations, IP addresses or AD containers

[Clagnuts]

Hi I'm trying to create a report based on multiple locations, IP addresses or AD OU's.

An example would be 'out of warranty' assets in London, Berlin and Moscow only

or

All assets without an anti virus installed with IP addresses like 192.168.0.% and 192.168.10.%


The code I have so far works if I use one location, IP address or OU but not multiples.

Any help greatly appreciated.

Select Top 1000000 tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tsysOS.Image As icon,
  tblAssetCustom.Location,
  tblAssets.IPAddress
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblAssetCustom.Warrantydate < GetDate() And tblAssets.IPAddress
  Like '192.168.0.%' And tblComputersystem.Domainrole < 2 And
  tblAssetCustom.State = 1) Or
  (tblAssets.IPAddress Like '192.168.10.%') Or
  (tblAssets.IPAddress Like '192.168.20.%')
Order By [Warranty Expiration] Desc

[Daniel_B · Accepted solution]

You need to correct the brackets for this filter. :

Where tblAssetCustom.Warrantydate < GetDate() And
tblComputersystem.Domainrole < 2 And
tblAssetCustom.State = 1 And
(tblAssets.IPAddress Like '192.168.0.%' Or
tblAssets.IPAddress Like '192.168.10.%' Or
tblAssets.IPAddress Like '192.168.20.%')

If you would like to filter on the name of an IP location, you could modify the report like this:

Select Top 1000000 tblAssetCustom.AssetID,
  tblAssets.AssetUnique,
  tsysOS.OSname,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tsysOS.Image As icon,
  tblAssetCustom.Location,
  tblAssets.IPAddress
From tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblComputersystem On
    tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysIPLocations On tblAssets.IPNumeric Between
    tsysIPLocations.StartIP And tsysIPLocations.EndIP
Where tblAssetCustom.Warrantydate < GetDate() And
  tblComputersystem.Domainrole < 2 And
  tblAssetCustom.State = 1 And
  tsysIPLocations.IPLocation In
     ('Berlin','London','Moscow')
Order By [Warranty Expiration] Desc

This will work with IP locations defined under Configuration\Asset Groups, section IP Address Range locations