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.
- On the Notifications page, select Add Report at the bottom of the page.
An Add Custom Report window should be displayed.
- Enter the name of the report: Flagged Words.
- Enter the description of the report: Report of Searches with Flagged Words.
- Enter the following text for the report:
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"
log_raw, users, urls, hosts
log_raw.timestamp > NOW() - INTERVAL '1 day'
AND log_raw.userid = users.userid
AND log_raw.id = urls.urlid
AND log_raw.status = 6
urls.url ~* '\y(?:p|q|search(_query)?|text)=bomb\y'
OR urls.url ~* '\y(?:p|q|search(_query)?|text)=nude(.|%20)selfie\y'
log_raw.timestamp::timestamp with time zone AT TIME ZONE 'CST6CDT'
- Change the highlighted timezone based upon your location: EST5EDT, CST6CDT, MST7MDT, PST8PDT, etc.
- 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 (+).
- Click the Add Report button in the edit window to save the report.
- To dispatch this report:
- Select the appropriate e-mail address in the E-mail Addresses section of the page, and
- Select the Daily option for this report in the Report Notifications section of the page.
Daily reports are dispatched Monday through Friday at 6 PM. Weekly reports are dispatched Monday at 12:01 AM.
This report is specified as a daily report. If you would like to utilize this report as a weekly report make the following changes:
- In the text of the report, change INTERVAL '1 day' to INTERVAL '1 week', and
- Select the Weekly option for this report in the Report Notifications section of the page.