How to fetch the before event of the search field

[Abilan1]

Hi ,

I am looking for two different search on the single log file and am using below command to search.

index=Test host=XXX "ABNUM" | map search="search source=$source$ "took""

I wanted to take the seconds which is highlighted on the below field only for the table ABNUM (which is on next event). The problem with the above search I've multiple table which is also having this second deatils. So It is giving me the output of that one as well. I would like to restrict this search only to the before event not on the entire source file.

Log File:

Sun Mar 27 13:07:28.654666 doQueryDiagnostics: The following SQL query took 4 seconds which is equal to or greater than QueryExecutionTimeThreshold (4 seconds)

Sun Mar 27 13:07:28.654975 SELECT * FROM ABNUM WHERE ( RPAN8 = 68537110.000000 )

[woodcock · Accepted solution]

Try this:

 index=Test host=XXX "ABNUM" | map search="search source=$source$ | streamstats current=f last(_raw) AS next_line | search \" took \" next_line=\"*ABNUM*\"" | dedup _raw next_line | rex "query took (?<querySeconds>\d+).*\((?<QueryExecutionTimeThreshold>\d+) seconds\)"

[Abilan1]

Thank you.. I've to use "query seconds" in my dashboard right? and is there any threshold value we have here?

[woodcock]

I assume so but it is your data! I can only tell you how to manipulate it and access it; only you know what it means. The threshold, which appears to be a constant (4), is in the field named QueryExecutionTimeThreshold

[Abilan1]

Hi ,

am able to see the querySeconds field in search fields, when I try to put the dashboard it is giving me no results found.

[woodcock]

This is an entirely different question. Click "Accept" to close this Q&A and ask a new question.

[Abilan1]

Thank you so much!

[woodcock]

Like this:

index=Test host=XXX "ABNUM" | map search="search source=$source$ | streamstats current=f last(_raw) AS next_line | search \" took \""

This should show all of your took events and each one should contain a field next_line that is the ABNUM event. From there, you can tack on something like | rex field=next_line blah to pull the SQL or whatever out.

[Abilan1]

Hi,

Thanks, I've one more problem here, actually there are other tables also in the log like EmpID, etc..so the above query is fetching the "took" from those entries as well..sample below.

Log:

Sun Mar 27 13:07:28.654666 doQueryDiagnostics: The following SQL query took 4 seconds which is equal to or greater than QueryExecutionTimeThreshold (4 seconds)

Sun Mar 27 13:07:28.654975 SELECT * FROM ABNUM WHERE ( RPAN8 = 68537110.000000 )

Sun Mar 27 13:17:08.654666 doQueryDiagnostics: The following SQL query took 15 seconds which is equal to or greater than QueryExecutionTimeThreshold (4 seconds)

Sun Mar 27 13:17:08.654975 SELECT * FROM EMPID Where ABC=1

[woodcock]

OK, then use this:

index=Test host=XXX "ABNUM" | map search="search source=$source$ | streamstats current=f last(_raw) AS next_line | search \" took \" next_line=\"*ABNUM*\""

[Abilan1]

Hi ,

am not able to see your answers, can you please post it again..Thanks!