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

[RSAAdmin]

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.....

41.

154.

196.

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,

ryan

[RSAAdmin]

I've been looking for something similar -- I want to exclude the large IP ranges of our scanners for metrics reports...it would be really handy if you could use regex watchlists for reporting, too.

If you aren't dead-set on using watchlists, though, you could define each range in your ipaddr.tab file and assign them each to a different department, and populate the data this way...doing it that way populates some of the default dashboards a little more interestingly, too.

On that note, if you take the time to populate your ipaddr.tab file with all this data, share that part of your ipaddr.tab here -- I'm sure that there are plenty of other people who would be interested in this data for reporting!

[RSAAdmin]

I too am looking for this sort of functionality. I have a list of over 10 devices comitted to monitoring, and maybe a dzen more that are use for management services. Obviously, both sets looks suspicious to the dozen or so IDSs. I don't want to have to keep tuning the IDSs every time a department brings on a new monitoring server, or a new management tool. I would like to be able to just add the IP to a watch list and do my filtering in Envision. I also don't want to start impacting my device count just so I can add them to the "device_list" table to able to "filter" them in SQL statements.

Any ideas?

[RSAAdmin]

Actually ... RSA, If you guys are splitting the data in the listvalues column using a sproc, it would be cool if you let us

know what that sproc is. If you're doing it in Java w/ a regex or something ... well, maybe you could provide us with a sproc that we can use in our sub-select statements (I would make my own, but I have a fear of voiding the warantee). OR ... well, you could always re-engineer the way the the watchlist is stored as some people have suggested in these forums. A pair of tables w/ some clever joining would probably make it easy enough for you guys, and certainly would make my day, and a few other people's I'm sure.