topic Re: How to fetch the before event of the search field in Splunk Search

How to fetch the before event of the search field

Abilan1 — Tue, 29 Mar 2016 03:46:28 GMT

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 )

Re: How to fetch the before event of the search field

woodcock — Tue, 29 Mar 2016 07:58:44 GMT

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.

Re: How to fetch the before event of the search field

Abilan1 — Tue, 29 Mar 2016 08:20:20 GMT

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

Re: How to fetch the before event of the search field

woodcock — Tue, 29 Mar 2016 08:30:14 GMT

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

Re: How to fetch the before event of the search field

Abilan1 — Wed, 30 Mar 2016 15:28:59 GMT

Hi ,

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

Re: How to fetch the before event of the search field

woodcock — Wed, 30 Mar 2016 15:34:21 GMT

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

Re: How to fetch the before event of the search field

Abilan1 — Wed, 30 Mar 2016 15:47:30 GMT

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

Re: How to fetch the before event of the search field

woodcock — Wed, 30 Mar 2016 15:51:39 GMT

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

Re: How to fetch the before event of the search field

Abilan1 — Wed, 30 Mar 2016 16:07:33 GMT

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.

Re: How to fetch the before event of the search field

woodcock — Wed, 30 Mar 2016 16:36:14 GMT

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

Re: How to fetch the before event of the search field

Abilan1 — Wed, 30 Mar 2016 18:46:42 GMT

Thank you so much!