Help with creating script to report on multiple banned words

[Ian_Harrison]

Hi

I have a list of about 20 banned words that setup in a web filter and work fine.  What I want to do is to create a report on all those banned words to see who searched for them.  I have the following dataset that allows me to search for one banned word/s.  Is there a way to get it to read a list of words say inside a for loop?

Thanks for any help

Ian

select from_itime(itime) as timestamp, keyword, srcip, coalesce( nullifna(`user`), ipstr(`srcip`) ) as user_src, count(*) as requests from $log where $filter and lower(keyword) like '%example words%' group by keyword, timestamp, srcip, user_src order by timestamp

[hzhao_FTNT]

Hi Ian,

It is not recommend that user define their own functions in our DB, please put them in one query. You query looks good, but if you want to see count(*), you do not need timestamp. The query should be like:

select keyword, srcip, coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, count(*) as requests from $log where $filter and lower(keyword) in ('keyword1', 'keyword2'......) group by keyword, srcip, user_src order by requests desc

If you need wildcard, use: lower(keyword) similar to '%(keyword1|keyword2|...|keyword20)%'

regards,

hz