CPUG: The Check Point User Group

Resources for the Check Point Community, by the Check Point Community.


First, I hope you're all well and staying safe.
Second, I want to give a "heads up" that you should see more activity here shortly, and maybe a few cosmetic changes.
I'll post more details to the "Announcements" forum soon, so be on the lookout. -E

 

Results 1 to 3 of 3

Thread: log file filter on command line

  1. #1
    Join Date
    2012-08-06
    Posts
    63
    Rep Power
    9

    Default log file filter on command line

    Hi there,

    when I'm doing audits I apply certain filters (query) in SmartView Tracker to the logging data to preprocess false positives.

    Now I'm trying to automate this, i.e. mail me the filtered log data daily. How can I accomplish this? Can I somehow export SmartView Tracker queries to be used on the command line (fw log ...)? Seems not...

    So far, the only thing that I accomplished is to export (an unfiltered) CSV version of the logs via crontab and ftp.

    Any ideas?

    best regards,
    Marki

  2. #2
    Join Date
    2008-07-15
    Posts
    6
    Rep Power
    0

    Default Re: log file filter on command line

    Also interested in this.

  3. #3
    Join Date
    2012-08-06
    Posts
    63
    Rep Power
    9

    Default Re: log file filter on command line

    Meanwhile I'm exporting to an SQL database and do the filtering there.

    How I do this:

    First you export the logs to CSV format as I said an get them onto your DB machine.

    Here's how I import the stuff, it's quite tricky because you have to deal with the problem of changing columns randomly.

    Code:
    #!/bin/bash
    
    set -o errexit
    set -o nounset
    
    TMPF=/tmp/$(basename $0).$$.$RANDOM.tmp
    LOG=/tmp/$(basename $0).$$.log
    rm -f $TMPF $LOG
    
    function tdate {
            date | tee --append $LOG
    }
    function techo {
            echo "$*" | tee --append $LOG
    }
    
    function clean_up {
            rm -f $TMPF
            find /tmp/ -iname "$(basename $0).??.log" -mtime +10 -delete
            find /tmp/ -iname "$(basename $0).??.*.tmp" -mtime +2 -delete
            exit
    }
    trap clean_up SIGHUP SIGINT SIGTERM EXIT
    
    function error_exit {
            echo "ERROR_EXIT $1" 1>&2
            rm -f $TMPF
            exit 1
    }
    
    FILE="$(ls -1rt /home/fwlog/*csv.gz 2>/dev/null | tail -1)"
    DB=fwlog
    TABLE=fwlog
    
    MYSQL="mysql -u fwlog -pxxx $DB"
    
    [ -z "$FILE" -o ! -f "$FILE" ] && error_exit "FILE >$FILE< does not exist"
    
    gunzip -c $FILE > $TMPF || error_exit "error gunzipping file >$FILE<"
    
    COLS="$(head -1 $TMPF | tr ' ;' '_,'  | tr -d '/:')"
    
    tdate
    SQL_INSERT="USE $DB; LOAD DATA INFILE '$TMPF' INTO TABLE $TABLE FIELDS TERMINATED BY ';' IGNORE 1 LINES ($COLS);" ## SET date=...
    techo "- Insert"
    set +o errexit
    RES=$(echo "$SQL_INSERT" | $MYSQL 2>&1)
    set -o errexit
    techo "SQL_INSERT RES >$RES<"
    #ERROR 1054 (42S22) at line 1: Unknown column 'sync' in 'field list'
    while echo "$RES" | grep --quiet "Unknown column"; do
            COL=$(echo "$RES" | cut --delimiter=\' --fields=2)
            COLS=$(echo 'USE fwlog; SELECT GROUP_CONCAT(column_name) from information_schema.columns where table_name="fwlog"' | $MYSQL | tail -1)
    
            tdate
            techo "-- Alter >$COL<"
            RES=$(echo "USE fwlog; \
    DROP TABLE IF EXISTS fwlog_new;
    CREATE TABLE fwlog_new LIKE fwlog; \
    ALTER TABLE fwlog_new ADD COLUMN $COL varchar(255) DEFAULT NULL; \
    INSERT INTO fwlog_new ($COLS) SELECT * FROM fwlog; \
    RENAME TABLE fwlog TO fwlog_old, fwlog_new TO fwlog; \
    DROP TABLE fwlog_old;" | $MYSQL)
            RET=$?
    
    #echo RET $RET
    #exit 0
    
            #SQL_ALTER="ALTER TABLE $TABLE ADD $COL VARCHAR(255) DEFAULT NULL"
            #echo "$SQL_ALTER" | $MYSQL
            #RET=$?
            if [ $RET -eq 0 ]; then
                    # try again
                    tdate
                    techo "- Insert 2"
                    set +o errexit
                    RES=$(echo "$SQL_INSERT" | $MYSQL 2>&1) # || error_exit "Mysql load data infile"
                    set -o errexit
                    techo "SQL_INSERT2 RES >$RES<"
            else
                    error_exit "Mysql alter table col >$col< RES >$RES<"
            fi
    done
    
    tdate
    techo "- Cleanup"
    echo "USE $DB; \
     DELETE FROM $TABLE WHERE DATEDIFF(DATE(NOW()), STR_TO_DATE(date, '%e%b%Y')) > 6;" | $MYSQL || error_exit "Mysql delete"
    
    tdate
    techo "- Optimize"
    echo "USE $DB; \
     OPTIMIZE TABLE $TABLE;" | $MYSQL || error_exit "Mysql optimize"
    
    tdate
    mv $FILE $FILE.xx
    find $(dirname $FILE) -name '*csv.gz.xx' -mtime +5 -delete
    Once in the database, I have another table with exceptions I want to filter and using SQL queries la "SELECT FROM fwlog ... AND NOT EXISTS SELECT bla FROM fwlog_exceptions ...." I get what I want.

    Have fun.
    Last edited by jeronimo; 2012-11-29 at 05:11.

Similar Threads

  1. Nokia IPSO Command Line
    By Youngy in forum Check Point IP Appliances and IPSO (Formerly Sold By Nokia)
    Replies: 33
    Last Post: 2010-08-17, 11:10
  2. How to get to command line interface?
    By kwarden in forum Check Point UTM-1 Appliances
    Replies: 9
    Last Post: 2009-09-21, 17:37
  3. Command Line
    By brierw in forum Clustering (Security Gateway HA and ClusterXL)
    Replies: 7
    Last Post: 2008-12-01, 06:52
  4. Command Line mode problems
    By ravic in forum SecureClient/SecuRemote
    Replies: 10
    Last Post: 2008-06-12, 07:17
  5. Command Line Reference for IPSO
    By Barry J. Stiefel in forum Check Point IP Appliances and IPSO (Formerly Sold By Nokia)
    Replies: 0
    Last Post: 2005-08-14, 13:57

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •