RSA Admin

Combining LIKE and a Watchlist value in a Report's SQL clause

Discussion created by RSA Admin Employee on Aug 28, 2008
Latest reply on Jul 8, 2009 by RSA Admin

I have a series of watchlists that correspond to the five top level domain registrars...  ARIN, RIPE, APNIC, LACNIC, and AFRINIC.  Each watchlist consist of the first octect of all the class A (/8) IP ranges managed and/or controlled by that given registrar.  The entries are in the following format...  Using Afrinic as an example.....






Now... I've been trying to build an Ad-Hoc report that pulls off the Intrusion Detection Table where the "SourceAddress" field starts with an IP address listed in one of the provided watchlists.    It would be something to the effect of:


DeviceAddress IN (select paddr from device_list where dtype=9) AND SourceAddress LIKE '${Watchlist}%'


The goal is to have the output bubble up into an eye-candy chart that show percentages of IDS hits from each registrar and how that's changed over time... shifting from one to the other, etc.


I've tried numerous variations of what I know (which admitedly is very limited).     Anyone have any suggestions?  Am I going about it all wrong?   I was able to get it to work just fine if I hard code all the like statements into the report itself... but I would rather make use of a watchlist instead.


thanks in advance,