Cipafilter Support:
Support@Cipafilter.com
309 517 2022 option 2
Mon - Fri 7 AM - 6 PM CT
Custom Reports - Flagged Words
Posted by Jim Giseburt, Last modified by Jim Giseburt on 12 April 2017 09:33 AM

Custom Reports are created on the Notifications page and can be automatically dispatched on either a daily or weekly basis.

Problem:  We would like an automatic report run daily listing searches with flagged words.

Solution:

  1. On the Notifications page, select Add Report at the bottom of the page.

    An Add Custom Report window should be displayed.

    Add custom report window

  2. Enter the name of the report:  Flagged Words.

  3. Enter the description of the report:  Report of Searches with Flagged Words.

  4. Enter the following text for the report:

    SELECT DISTINCT
        users.username AS "Username",
        log_raw.ip AS "IP",
        log_raw.timestamp::timestamp with time zone AT TIME ZONE 'CST6CDT' AS "Time",
        substr(urls.url, 0, 30) AS "URL",
        regexp_matches(urls.url, '\y(?:p|q|search(?:_query)?|text)=([^&]+)') AS "Query"
    FROM
        log_raw, users, urls, hosts
    WHERE
        log_raw.timestamp > NOW() - INTERVAL '1 day'
        AND log_raw.userid = users.userid
        AND log_raw.id = urls.urlid
        AND log_raw.status = 6
        AND (
            urls.url ~* '\y(?:p|q|search(_query)?|text)=bomb\y'
            OR urls.url ~* '\y(?:p|q|search(_query)?|text)=nude(.|%20)selfie\y'
    )
    ORDER BY
        log_raw.timestamp::timestamp with time zone AT TIME ZONE 'CST6CDT'
    ;

    Flagged words report window

    Notes:

    1. Change the highlighted timezone based upon your location:  EST5EDT, CST6CDT, MST7MDT, PST8PDT, etc.

    2. Change the highlighted flagged words as desired.  For a single flagged word, use the syntax as specified in the bomb example.  For multiple words, separate the words with  (.|%20) as in the nude selfie example.  The characters (.|%20) are necessary to specify that the words are either separated by a space (%20) or another character (.).  Depending upon the search engine, individual words are separated by either spaces or another character like a plus sign (+).

  5. Click the Add Report button in the edit window to save the report.

  6. To dispatch this report:

    1. Select the appropriate e-mail address in the E-mail Addresses section of the page, and 

      E-mail address section


    2. Select the Daily option for this report in the Report Notifications section of the page.

      Report notifications section

Daily reports are dispatched Monday through Friday at 6 PM.  Weekly reports are dispatched Monday at 12:01 AM.

Note:

This report is specified as a daily report.  If you would like to utilize this report as a weekly report make the following changes:

  1. In the text of the report, change INTERVAL '1 day' to INTERVAL '1 week', and

  2. Select the Weekly option for this report in the Report Notifications section of the page.


Attachments 
 
 addcustomreportwindow.png (12.19 KB)
 flaggedwordsreportwindow.png (22.50 KB)
 e-mailaddressessection.png (14.36 KB)
 reportnotificationssection.png (54.60 KB)
(0 vote(s))
Helpful
Not helpful

Comments (0)
©Cipafilter 2017. All Rights Reserved.