Skip to content

Instantly share code, notes, and snippets.

@petrosmm

petrosmm/logparser.sql

Last active December 20, 2017 14:56
Show Gist options
  • Save petrosmm/4bf7bf9f2995ec42bd8432e985e60f2f to your computer and use it in GitHub Desktop.
Save petrosmm/4bf7bf9f2995ec42bd8432e985e60f2f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. M Peters revised this gist Dec 20, 2017. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions logparser.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -3,6 +3,5 @@ SELECT EXTRACT_SUFFIX(EXTRACT_PATH(LogFilename), 0, '\W3SVC') as Site, TO_LOCALT
    FROM '[LOGFILEPATH]'
    WHERE
    TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 'dd') = TO_STRING(SYSTEM_DATE(), 'dd') AND
    [sc-status] NOT BETWEEN 200 AND 399 AND
    cs(User-Agent) NOT LIKE '%Bing%'
    [sc-status] NOT BETWEEN 200 AND 399
    ORDER BY datetime desc
  2. M Peters created this gist Dec 19, 2017.
    8 changes: 8 additions & 0 deletions logparser.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,8 @@
    SELECT EXTRACT_SUFFIX(EXTRACT_PATH(LogFilename), 0, '\W3SVC') as Site, TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS [datetime], [LogFilename], [LogRow], [c-ip], [cs-username], [s-sitename], [s-computername], [s-ip], [s-port], [cs-method], [cs-uri-stem], [cs-uri-query], [sc-status], [sc-substatus], [sc-win32-status], [sc-bytes], [cs-bytes], [time-taken], [cs-version], [cs-host], [cs(User-Agent)], [cs(Cookie)], [cs(Referer)], [s-event], [s-process-type], [s-user-time], [s-kernel-time], [s-page-faults], [s-total-procs], [s-active-procs], [s-stopped-procs]
    /* remove columns: [date], [time], */
    FROM '[LOGFILEPATH]'
    WHERE
    TO_STRING(TO_LOCALTIME(TO_TIMESTAMP(date, time)), 'dd') = TO_STRING(SYSTEM_DATE(), 'dd') AND
    [sc-status] NOT BETWEEN 200 AND 399 AND
    cs(User-Agent) NOT LIKE '%Bing%'
    ORDER BY datetime desc