topic Re: How do I list data rows starting at a certain point if a field contains a particular value in Splunk Dev

How do I list data rows starting at a certain point if a field contains a particular value

Gawker — Mon, 14 May 2018 18:55:06 GMT

I've a need to search through some data for a field that has a particular value. If the value is found, then I want to list out that line and the 50 lines that precedes it.

For example:

01) Search sourcetype=abc for field1="alert".

02) Field1="alert" is first found at 2018-05-10 18:10:01

03) List out entry for 2018-05-10 18:10:01 and the 50 data entries prior/earlier then 18:10:01. The 50 data entries may not be related to or contain field1="alert". They are needed for review of activity leading to the "alert".

04) Field1="alert" is next found at 2018-04-27 13:57:55

05) List out entry for 2018-05-10 13:10:00 and the 50 data entries prior/earlier then 13:57:55. The 50 data entries may not be related to or contain field1="alert". They are needed for review of activity leading to the "alert".
...

I've tried several things unsuccessfully. I could really use some guidance.

Thank you

Re: How do I list data rows starting at a certain point if a field contains a particular value

somesoni2 — Mon, 14 May 2018 19:04:53 GMT

What version of Splunk are you using?

Re: How do I list data rows starting at a certain point if a field contains a particular value

somesoni2 — Mon, 14 May 2018 19:39:26 GMT

If you're using Splunk 6.4 or above, give this a try

Your current search querying sourcetype=abc and gets all events
| streamstats count reset_before="Field1=\"alert\""
| where count<=50

Re: How do I list data rows starting at a certain point if a field contains a particular value

Gawker — Mon, 14 May 2018 19:52:52 GMT

Cloud based SaaS instance: 7.0.0.1 aef63c0828ae

Re: How do I list data rows starting at a certain point if a field contains a particular value

somesoni2 — Mon, 14 May 2018 20:02:33 GMT

Ok.. give the above query a try.

Re: How do I list data rows starting at a certain point if a field contains a particular value

Gawker — Mon, 14 May 2018 20:23:15 GMT

For clarification, I updated my question.

The 50 rows after the field1="alert" that I am interested in are not entries with "alert".

What I am looking for would be something akin to a head/tail combination of the data based on the actual time of the entry which contains field1="alert". I want to capture the "alert" row and 50 data rows following it, regardless of the contents of the 50 rows.

Hope that clarifies things.

Re: How do I list data rows starting at a certain point if a field contains a particular value

somesoni2 — Mon, 14 May 2018 20:42:27 GMT

So if your current search is selecting all data, regardless of where it has field1="alert" or not, all those will be selected by line 1. The events returned should be in reverse chronological order of _time (latest events first). The line 2 should add a field count on each events with 1,2,3.. untill it's find field1="alert". It'll reset the count to start from 1 again once it found field1="alert" and so on. The where clause should only select events with field="alert" and all subsequent rows which have count (or serial number) less than 51.
Do you see different output from the query I suggested?

Re: How do I list data rows starting at a certain point if a field contains a particular value

Gawker — Tue, 29 Sep 2020 19:35:03 GMT

I took a small data source where verification of my search criteria and results would be easy.

(01) Here's the data from a specific time picker range that was convenient to use as there were only 12 events.

"12 events (5/15/18 12:00:00.000 AM to 5/16/18 12:00:00.000 AM)"

grep "2018-05-15 15:32:" ../log/server.log

xxxxxxxx75

xxxxxxxx78

grep exception ../log/server.log

xxxxxxxx66

tail -f ../log/server.log

xxxxxxxx66

cd /usr/local/jboss/server/default/deploy
2018-05-15 11:34:16

xxxxxxxx99

nslookup eudc2.eu.ei3.local

xxxxxxxx94

nslookup xxxxx.xxxxxx.xxxxxx.xx

xxxxxxxx44

cat inputs.conf

xxxxxxxx43

ls

xxxxxxxx41

cd /opt/splunkforwarder/etc/system/local/
2018-05-15 09:32:12

(02) I set up this query, using the date/time from the first event (see above) as my key for streamstats:

source="/root/.bash_history" | rex "(grep \"(?.)\" ..\/log\/server.log)" | streamstats count reset_after="Search_It=\"2018-05-15 15:32:*\"" | where count<=3

(03) The results from the aforementioned query and the time range picker yielded what was expected, the "key" entry and three rows:

grep "2018-05-15 15:32:" ../log/server.log

xxxxxxxx75

xxxxxxxx78

grep exception ../log/server.log

xxxxxxxx66

tail -f ../log/server.log

(04) I set the time range picker to "All", which then yielded an extra row in addition to the 4 expected as in (03):

2018-05-17 16:49:01

Any idea as to why the extra row shows up?

Thank you.

Re: How do I list data rows starting at a certain point if a field contains a particular value

Richfez — Tue, 29 Sep 2020 19:39:12 GMT

If field1 exists always but is only sometimes error, then ...

Hmm. There's several ways to do this (some outlined by the inestimable somesoni2 already). Let's try this one:

sourcetype=abc
| eval is_error = if(field1=="error", "error", null())
| streamstats window=50 last(is_error) as trigger_error
| transaction maxevents=50 trigger_error

That should create one "event" out of the error line, plus the preceeding 50 events.

NOTES:
Change window=50 and maxevents=50 to 5 each for testing - it might make it easier to see/test. They should match each other, but otherwise the number is up to you.
Also, for debugging it might be useful to run that same search only replace the | transaction... line with | table _time, field1, is_error, trigger_error (and maybe include another field or two if it makes sense). If you do that, you'll see better how it works.

It works by
a) searching all the data - you can't throw out the non-error ones at the front or else how would you include them later?

b) creating a new field "is_error" (change that name if it conflicts with an existing field!) that only exists when field1 equals "error". This way if field1 is NOT "error" then there's no new field "is_error" on that event.

c) now the magic - streamstats in this case is watching a window or 50 events. For each event, it copies the most recent "is_error" to the all 50 events in its window as the field "trigger_error". This means that when "is_error" doesn't exist, nothing gets copied, but when it does, the preceeding 50 events also get a copy of it.

d) last we just group them together to make it easy to alert on it.

Line d) may require a little fiddling depending on exactly how you are going to use this.

As reference, here's a very similar thing done with 100 items before the "alerting event". It's not quite the same scenario, but it is close enough that it may help to read through that answer too.

Happy Splunking!
-Rich