Splunk Search

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

goncalosilva123
Engager

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,

Labels (2)
0 Karma
1 Solution

tscroggins
Influencer

@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

View solution in original post

0 Karma

tscroggins
Influencer

@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

0 Karma

tscroggins
Influencer

@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

0 Karma

goncalosilva123
Engager

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-192021-01-20 Dif_19_20
Item 12931-2 (31-29) (Green)
Item 23725-12 (25-37) (Green)
Item 4
10
13  3 (13-10) (Red)
Item 38 5-3 (5-8) (Green)
Tags (4)
0 Karma

goncalosilva123
Engager

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.

Tags (1)
0 Karma

tscroggins
Influencer

@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
0 Karma

to4kawa
Ultra Champion
| 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
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...