topic Re: Use eval (or other) to make automatic calculation in xyseries in Splunk Search

Use eval (or other) to make automatic calculation in xyseries

goncalosilva123 — Mon, 22 Feb 2021 21:02:31 GMT

Hi,

I have an automatic process that daily writes some information in a CSV file [1]. Then I have a dashboard that picks up some data and uses xyseries so that I can see the evolution by day. [2]

Now I want to calculate the difference between everyday, but the problem is that I don't have "field" names so that I can use in an Eval, so I can I solve this? [3]

Please see examples below.

################################################

[1] Information Saved every day [ | is the separator of each column]

[Item_Name] | [Date_1] | [Total]
2021-01-18
Item 1 | |2021-01-18 | 32
Item 2 | |2021-01-18 | 50
Item 3 | |2021-01-18 | 10
Item 4 | |2021-01-18 | 15

2021-01-19
Item 1 | |2021-01-18 | 29
Item 2 | |2021-01-18 | 37
Item 3 | |2021-01-18 | 8
Item 4 | |2021-01-18 | 10

2021-01-20
Item 1 | |2021-01-20 | 31
Item 2 | |2021-01-20 | 25
Item 3 | |2021-01-20 | 5
Item 4 | |2021-01-20 | 13

################################################
| inputlookup blabla
| xyseries Item_Name Date_1 Total

[2] Applying xyseries I get the following

2021-01-18 2021-01-19 2021-01-20

Item 1 32 29 31
Item 2 50 37 25
Item 3 10 8 5
Item 4 15 10 13

################################################

[3] What I would like to have

2021-01-18 2021-01-19 2021-01-20 Dif_18_19 Dif_19_20

Item 1 32 29 31 -3 (29-32) 2 (31-29)
Item 2 50 37 25 -13 (37-50) -12 (25-37)
Item 3 10 8 5 -2 (8-10) -3 (5-8)
Item 4 15 10 13 -5 (10-15) 3 (13-10)

################################################

Thanks in advance.

Best Regards,

Re: Use eval (or other) to make automatic calculation in xyseries

to4kawa — Tue, 23 Feb 2021 01:34:27 GMT

| makeresults | eval _raw="item,date,count Item 1 ,2021-01-18 ,32 Item 2 ,2021-01-18 ,50 Item 3 ,2021-01-18 ,10 Item 4 ,2021-01-18 ,15 Item 1 ,2021-01-19 ,29 Item 2 ,2021-01-19 ,37 Item 3 ,2021-01-19 ,8 Item 4 ,2021-01-19 ,10 Item 1 ,2021-01-20 ,31 Item 2 ,2021-01-20 ,25 Item 3 ,2021-01-20 ,5 Item 4 ,2021-01-20 ,13" | multikv forceheader=1 | eval date=trim(date) | table item date count ``` this is sample data. from here, the logic ``` | streamstats current=f last(count) as delta_value by item | eval diff_field="Diff_".mvindex(split(date,"-"),-1)."-".strftime(relative_time(strptime(date,"%F"),"-1d"),"%d") | eval diff_value=count - delta_value | eval tmp1=date.":".count, tmp2=diff_field.":".diff_value | eval tmp=mvappend(tmp1,tmp2) | table item tmp | mvexpand tmp | rex field=tmp "(?<date>.*):(?<count>.+)" | xyseries item date count

Re: Use eval (or other) to make automatic calculation in xyseries

tscroggins — Tue, 23 Feb 2021 04:27:24 GMT

@goncalosilva123

Here's an alternative with similar performance:

| inputlookup a_CSV_file.csv
| streamstats current=f last(Date_1) as last_Date_1 last(Total) as last_Total by Item_Name
| eval Diff_Date="Diff_".substr(last_Date_1, -2)."_".substr(Date_1, -2)
| appendpipe [ | stats values(eval((Total - last_Total)." (".Total."-".last_Total.")")) as Diff by Diff_Date Item_Name ]
| eval Date_1=coalesce(Date_1, Diff_Date), Total=coalesce(Total, Diff)
| xyseries Item_Name Date_1 Total

Re: Use eval (or other) to make automatic calculation in xyseries

goncalosilva123 — Wed, 03 Mar 2021 08:54:49 GMT

Hi again,

this as done the work...and just another question, if I have several dates, how can I show only the last two and the difference?

ex:

2021-01-19 2021-01-20 Dif_19_20

Item 1 29 31 -2 (31-29)
Item 2 37 25 -12 (25-37)
Item 3 8 5 -3 (5-8)
Item 4 10 13 3 (13-10)

Thanks in advance and sorry for the delay in answer.

Re: Use eval (or other) to make automatic calculation in xyseries

tscroggins — Wed, 03 Mar 2021 12:21:08 GMT

@goncalosilva123

Insert a tail command between streamstats and eval:

| tail 2

If your input CSV is very large, you can additionally limit the results before streamstats with an inputlookup where predicate, a separate where command, or even | tail 3.

| inputlookup

| tail 3

| streamstats

| tail 2

| eval

Re: Use eval (or other) to make automatic calculation in xyseries

goncalosilva123 — Mon, 08 Mar 2021 16:29:18 GMT

Thanks for all!

Finally, how can I change order based on the most recent date and number (2021-01-20, descending) and also change the cell color based on the value?
Ex:
-> If the cell value is bigger than 5, puts cell color red, If cell color is between 0 and 5, puts orange, if is less than 0, puts green.
-> 2021-01-20
31
25
13
5

#################################################################
The goal!

	2021-01-19	2021-01-20	Dif_19_20
Item 1	29	31	-2 (31-29) (Green)
Item 2	37	25	-12 (25-37) (Green)
Item 4	10	13	3 (13-10) (Red)
Item 3	8	5	-3 (5-8) (Green)

Re: Use eval (or other) to make automatic calculation in xyseries

tscroggins — Sat, 13 Mar 2021 19:17:31 GMT

@goncalosilva123

Use a method appropriate to your source data to filter the base search prior to streamstats:

| inputlookup a_CSV_file.csv | eval _time=strptime(Date_1, "%F") | eventstats max(_time) as max_time | where _time>relative_time(max_time, "-2d") | streamstats current=f count last(Date_1) as last_Date_1 last(Total) as last_Total by Item_Name | eval Diff_Date="Diff_".substr(last_Date_1, -2)."_".substr(Date_1, -2) | appendpipe [ | stats values(eval((Total - last_Total)." (".Total."-".last_Total.")")) as Diff by Diff_Date Item_Name ] | eval Date_1=coalesce(Date_1, Diff_Date), Total=coalesce(Total, Diff) | xyseries Item_Name Date_1 Total