topic Re: How to filter a record among multiple events in Splunk Search

How to filter a record among multiple events

febbi — Mon, 01 Feb 2021 06:58:01 GMT

I have a table like in splunk this:

appname	value	time
app1	10	2020-12-30
app1	12	2020-12-31
app2	23	2020-12-30
app2	20	2020-12-31

I want to filter the records that the value is increasing while the time. In this case, we can only find

(app1, 12 2020-12-31)

How could I write the splunk sql to implement this?

Re: How to filter a record among multiple events

scelikok — Mon, 01 Feb 2021 07:37:24 GMT

Hi @febbi,

Please try below;

| sort time | delta value as value_diff | where value_diff > 0

Re: How to filter a record among multiple events

tread_splunk — Mon, 01 Feb 2021 07:41:06 GMT

How about this? Lines 1-5 are to create some test data. The rest is the solution.

Re: How to filter a record among multiple events

tread_splunk — Mon, 01 Feb 2021 08:23:26 GMT

I've used

| streamstats sum(count) as prevCount by app window=1 current=f global=f

...instead of delta because I've assumed you want / need the "by app" clause.

I've also assumed you only want to know when the most recent event (per app) has a larger value than the previous event

Re: How to filter a record among multiple events

febbi — Tue, 02 Feb 2021 10:22:02 GMT

Thanks for your answer, but it still doesn't work for me.

Are there ant ways to convert the table to

appname	row1	row2
app1	10	12
app2	23	30

Then I can compare the "row1 < row2".

Thanks again

Re: How to filter a record among multiple events

febbi — Tue, 02 Feb 2021 10:27:39 GMT

Thanks for your answer, but it still doesn't work for me.

Are there ant ways to convert the table to

appname	row1	row2
app1	10	12
app2	23	30

Then I can compare the "row1 < row2".

Thanks again

Re: How to filter a record among multiple events

tread_splunk — Tue, 02 Feb 2021 12:26:15 GMT

Will each app (app1, app2 etc) only ever have 2 values?

Re: How to filter a record among multiple events

tread_splunk — Tue, 02 Feb 2021 12:43:28 GMT

Assuming you only have 2 rows per app, and you already have the data as per your table, then you just need the last 3 lines in the above. Everything above that is to generate some test data in the same format as your table.

Re: How to filter a record among multiple events

febbi — Tue, 02 Feb 2021 13:22:30 GMT

Each app may have several values, we need to convert the values to different columns according to the same app.

Re: How to filter a record among multiple events

tread_splunk — Tue, 02 Feb 2021 13:27:39 GMT

No problem. The solution I posted works for multiple rows per app.

Just add...

| streamstats count by app | eval count="Row ".count | xyseries app count value

Definitely worth getting to grips with xyseries and untable commands.

Re: How to filter a record among multiple events

febbi — Tue, 02 Feb 2021 13:39:00 GMT

It works for me!, Thanks very much!