Hi Splunk Community! I'm trying to get the context of an error. Here is a snippet of the logs: 2021-03-21 11:36:43,045 [thread-1] blablabla orderid 12345
2021-03-21 11:36:43,045 [thread-2] blablabla orderid 23456
2021-03-21 11:36:43,045 [thread-3] blablabla orderid 34567
2021-03-21 11:36:43,046 [thread-1] blablabla
...
2021-03-21 11:36:43,047 [thread-1] WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - SQL Error: 1366, SQLState: HY000
2021-03-21 11:36:43,048 [thread-1] ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - Incorrect string value: '\xE2\x80\xAFfro...' for column 'request' at row 1
2021-03-21 11:36:43,050 [thread-1] ERROR [class-1] - org.hibernate.exception.GenericJDBCException: could not execute statement
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
<multi-line stack trace>
... The "context" I'm trying to get is: >> For orderid 12345, error "SQL Error: 1366, SQLState: HY000" while trying to write '\xE2\x80\xAFfro...' for column 'request' in "class-1". As you can see, the order and the error messages are all on different lines. I know, it's not ideal, but that's what I have to deal with right now. Is there a way to get this summary? My idea is: find the error get the thread name find logs with the same thread name in the past few seconds to get the orderid find logs with the same thread name in the next few seconds to get the character(s), the column name and the class name Something to keep in mind on our setup: the "_time" is the indexed time, not the real log time. Because of that, sometimes there are logs from the previous day having the same "_time" as logs from the current day. So, I've used extracted fields to get the date/time from the log entry. I have been able to extract fields for the date/time (rm_datetime), the thread name (rm_threadname), the log message (rm_logmessage) I've tried using: transaction to get the next line grouped by the rm_threadname, but transaction can only go in 1 direction, i.e. either up or down. In this case, I'm looking for "SQL Error: 1366". So, I have to walk up and down to get the full context. index=main | transaction rm_threadname startswith="[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - Incorrect string value:" maxevents=5 | rex field=_raw "(?<FirstFewLines>(.*[\n]){2})" | table FirstFewLines map based on the rm_threadname of the previous search, but I'm failing to get log entries around the rm_datetime of the error (a few seconds before and a few seconds after) index=main "SQL Error: 1366"
| eval errordateserial=strptime(rm_datetime, "%Y-%m-%d %H:%M:%S,%Q"), fromdateserial=strptime(rm_datetime, "%Y-%m-%d %H:%M:%S,%Q") - 2, todateserial=strptime(rm_datetime, "%Y-%m-%d %H:%M:%S,%Q") + 1
| table rm_datetime, rm_threadname, rm_logmessage, errordateserial, fromdateserial, todateserial
| map [ search index=main rm_threadname=$rm_threadname$
| eval datetime=strptime(rm_datetime, "%Y-%m-%d %H:%M:%S,%Q")
| eval datetime >= $fromdateserial$ | eval datetime <= $todateserial$
| eval errordateserial=$errordateserial$, fromdateserial=$fromdateserial$, todateserial=$todateserial$
] subsearches using the rm_threadname, but I'm failing to get log entries around the rm_datetime of the error (a few seconds before and a few seconds after) index=main [ search index=main "SQL Error: 1366" | fields rm_datetime, rm_threadname | format ] What am I missing? Thanks in advance.
... View more