Skip to main content
Ian_Harrison
Ian_Harrison
New Member
February 23, 2016
Question

Help with creating script to report on multiple banned words

  • February 23, 2016
  • 1 reply
  • 2772 views

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

    1 reply

    hzhao_FTNT
    Staff
    hzhao_FTNT
    Staff
    February 23, 2016

    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

    Terms & ConditionsAccessibility statement