topic Re: How can I calculate data in sub groups? in Splunk Search

How can I calculate data in sub groups?

juliaester03 — Wed, 05 Jun 2019 16:18:45 GMT

Hello all,

I have a question regarding a calculation for the stock.

My table has three coloums: ISIN, price and timestamp. Every event gives a new price for an arbitrary ISIN. The task is to find out which ISIN has a price change over a given threshold (e.g. 5%) in the last two events for this ISIN.

I want to build an alert if the price change of an ISIN exceeds the threshold in two sequenced events which belong to each ISIN.

I have tried the following command:

sourcetype="stock_data.csv" | top 2 _time by ISIN | stats count by ISIN

But I have no idea how to calculate the two newest prices for one ISIN and to compare it with a given threshold.

Many thanks in advance!

Re: How can I calculate data in sub groups?

niketn — Wed, 05 Jun 2019 17:19:42 GMT

@juliaester03 "last two events for ISIN" and "top two events for ISIN" may imply different things. I am assuming it is the first that you need.

Using | dedup 2 ISIN you can retain latest two events for each ISIN. Using streamstats with window=1 current=f, on applying last() aggregation gives us the previous value of ISIN which can be used for calculating the percent change.

sourcetype="stock_data.csv"
| fields _time ISIN price
| dedup 2 ISIN
| reverse
| streamstats last(price) as lastPrice by ISIN window=1 current=f
| eval perc=round(((price-lastPrice)/price)*100,2)
| search perc>5

Following is a run anywhere example based on the detailed provided in the question

| makeresults
| eval data="time=\"2019/06/05 01:00:00\",ISIN=\"A\",price=50;time=\"2019/06/05 02:00:00\",ISIN=\"A\",price=60;time=\"2019/06/05 03:00:00\",ISIN=\"A\",price=100;time=\"2019/06/05 01:00:00\",ISIN=\"B\",price=50;time=\"2019/06/05 02:00:00\",ISIN=\"B\",price=51"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| eval _time=strptime(time,"%Y/%m/%d %H:%M:%S")
| sort - _time
| fields _time ISIN price
| dedup 2 ISIN
| reverse
| streamstats last(price) as lastPrice by ISIN window=1 current=f
| eval perc=round(((price-lastPrice)/price)*100,2)
| search perc>5

Re: How can I calculate data in sub groups?

Vijeta — Wed, 05 Jun 2019 17:23:36 GMT

@juliaester03 Try something similar -

sourcetype="stock_data.csv" | sort 0 - _time|streamstats count as id by ISIN | where id <=2 | eval diff=( price - threshold )/threshold * 100| where diff > 5

Re: How can I calculate data in sub groups?

juliaester03 — Fri, 07 Jun 2019 16:57:19 GMT

Dear @niketnilay ,

many thanks for your fast and very detailed answer!!! The first try worked out really well. I will do some testing now. Maybe I come back to you with some more questions. 🙂

Re: How can I calculate data in sub groups?

juliaester03 — Fri, 07 Jun 2019 16:59:47 GMT

Dear @Vijeta ,

many thanks for answering me so fast!!

Re: How can I calculate data in sub groups?

evania — Mon, 17 Jun 2019 22:40:23 GMT

Hi @juliaester03 ,

Did you have a chance to check out some answers? If it worked, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help you.

Thanks for posting!