topic Re: display rows between 2 searches in Splunk Search

display rows between 2 searches

splunkpaterd2 — Wed, 31 Mar 2021 07:42:33 GMT

Good morning,

suppose I have the following entries in my file :

BEGIN

abc

def

END;

BEGIN

xyz

END;

***

I want to search for the sentence BEGIN and the sentence with END;

As a result I want to have the search entries BEGIN and END including the rows between.

Regards

Dik Pater

Re: display rows between 2 searches

ITWhisperer — Wed, 31 Mar 2021 07:47:20 GMT

On the face of it, it seems like the transaction command is the one you should be looking at

Re: display rows between 2 searches

splunkpaterd2 — Wed, 31 Mar 2021 08:00:40 GMT

I did not succeed, so if you have the solution for me please post it.

Re: display rows between 2 searches

ITWhisperer — Wed, 31 Mar 2021 08:04:28 GMT

Can you post some more realistic anonymised data?

Re: display rows between 2 searches

splunkpaterd2 — Thu, 01 Apr 2021 10:55:39 GMT

2021-03-17T11:08:26,399 INFO [00000018] :dikpater@nowhere - 3 PROC SQL NOEXEC;
2021-03-17T11:08:26,399 INFO [00000018] :dikpater@nowhere - 4 SELECT t1.ID,
2021-03-17T11:08:26,399 INFO [00000018] :dikpater@nowhere - 5 t1.KOLOM1,
2021-03-17T11:08:26,399 INFO [00000018] :dikpater@nowhere - 6 t1.KOLOM2,
2021-03-17T11:08:26,399 INFO [00000018] :dikpater@nowhere - 7 FROM mytable t1;
2021-03-17T11:08:26,399 INFO [00000018] :dikpater@nowhere - 8 RUN;

So if I search for PROC SQL until RUN;

thats what I need to get back.

TIA

Dik Pater

Re: display rows between 2 searches

ITWhisperer — Thu, 01 Apr 2021 13:12:25 GMT

| makeresults | eval _raw="2021-03-17T11:08:26,399 INFO [00000018] :dikpater@nowhere - 3 PROC SQL NOEXEC; 2021-03-17T11:08:26,400 INFO [00000018] :dikpater@nowhere - 4 SELECT t1.ID, 2021-03-17T11:08:26,401 INFO [00000018] :dikpater@nowhere - 5 t1.KOLOM1, 2021-03-17T11:08:26,402 INFO [00000018] :dikpater@nowhere - 6 t1.KOLOM2, 2021-03-17T11:08:26,403 INFO [00000018] :dikpater@nowhere - 7 FROM mytable t1; 2021-03-17T11:08:26,404 INFO [00000018] :dikpater@nowhere - 8 RUN;" | multikv noheader=t | fields - Column_1 | eval _time=strptime(_raw,"%Y-%m-%dT%H:%M:%S,%Q") | sort - _time | transaction startswith="PROC SQL" endswith="RUN" mvraw=t

The first part sets up dummy data in line with your example

The second part may or may not be needed if you already have _time extracted as an epoch time

The transaction command needs event sorted in descending _time order

I used mvraw=t which may or may not be required depending on how you want to proceed

Re: display rows between 2 searches

splunkpaterd2 — Wed, 07 Apr 2021 06:04:53 GMT

Thanks for your excellent answer .

My collegues are happy with this and can now continue to answer some auditing questions.

Regards,

Dik Pater

The Netherlands