I have log file with this strings

2022-08-13 19:15:17.170 INFO 550034 --- [ scheduling-3] org.hibernate.SQL_SLOW : SlowQuery: 11387 milliseconds. SQL:

I need grep command to count slowQuery for last hour with time more than 10000 ms

I've try

grep "SQL_SLOW" app.log | wc -l

but i can't add two conditions:

1. time (first 19 symbols) must be between current time minus one hour
2. time for query must be more than 10000 ms (in example it 11387 ms)

CodePudding user response：

grep is the wrong tool for the job; trying to get the date and time conditions to match via RE is just wrong.

How about awk?

echo '2022-08-13 19:15:17.170 INFO 550034 --- [ scheduling-3] org.hibernate.SQL_SLOW : SlowQuery: 11387 milliseconds. SQL:' \
| awk -v now=$(date " %s") '
/SlowQuery:/ {
        tmp = "\" " $1 " " $2 "\""
        cmd = "date -d " tmp "  %s"
        cmd | getline ts
        t = gensub(/.*SlowQuery: ([0-9] ) milliseconds.*/, "\\1", "g", $0)
        if (now - ts < 3600 && t > 10000) {
                print $0
        }
}

Brief explanation: first we capture the current time in seconds since epoch.

Then we convert the timestamp for each line of the log containing /SlowQuery:/ to seconds since epoch tmp="\x22 "$1" "$2"\x22";cmd="date -d " tmp " %s";cmd|getline ts and store it in the variable ts.

We extract the time taken t=gensub(/.*SlowQuery: ([0-9] ) milliseconds.*/, "\\1", "g") and store it in t.

The last step is to check if less than an hour has passed and the query was slower than 10000 ms if(ts-now<3600 && t>10000){print $0}. If both is true, print the line.

CodePudding user response：

Someone helped me find this solution. But it have some admissions. Logs will not be exactly for the last hour, but for the current incomplete hour and for the previous hour (that is, depending on the current time, it will be from 1 to 2 hours)

grep "SQL_SLOW" "app.log" \
| grep "^$(date -d '1 hour ago' ' %Y-%m-%d %H')" \
| grep -P "SlowQuery: \d{5,} milliseconds" \
| wc -l

But solution with awk looks like better