Archive

How to fetch the before event of the search field

Abilan1
Path Finder

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 )

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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\)"

View solution in original post

woodcock
Esteemed Legend

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\)"

View solution in original post

Abilan1
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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

0 Karma

Abilan1
Path Finder

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.

0 Karma

woodcock
Esteemed Legend

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

0 Karma

Abilan1
Path Finder

Thank you so much!

0 Karma

woodcock
Esteemed Legend

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.

0 Karma

Abilan1
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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*\""
0 Karma

Abilan1
Path Finder

Hi ,

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

0 Karma