topic Re: How to insert rows with eval calculated fields in a time series table? in Splunk Search

How to insert rows with eval calculated fields in a time series table?

Stevelim — Tue, 23 Feb 2016 13:03:38 GMT

I have a set of time series data that looks like this:

Date  Type  Data
==================
12  A    1
12  B    2
12  C    3
13  A    1
13  B    2
13  C    3

I need to insert, for example, 2 calculated fields based on a formula like D = A + B - C, E = A + B + C and insert back the date. The results should look like the following:

Date  Type  Data
==================
12  A    1
12  B    2
12  C    3
12  D    1+2-3
12  E    1+2+3
13  A    4
13  B    5
13  C    6
13  D    4+5-6
12  E    4+5+6

Is Splunk able to achieve this?

Re: How to insert rows with eval calculated fields in a time series table?

javiergn — Tue, 23 Feb 2016 13:48:05 GMT

Hi,

I have replicated your sample with a csv file and then wrote the following query that hopefully will answer your question:

| inputcsv mycsv.csv
| appendpipe [stats list(Data) as Data by Date | eval Type = "D, E"]
| eval Type = split(Type, ",")
| mvexpand Type
| eval Data = case(
    match(Type,"D"), tonumber(mvindex(Data,0)) + tonumber(mvindex(Data,1)) - tonumber(mvindex(Data,2)),
    match(Type,"E"), tonumber(mvindex(Data,0)) + tonumber(mvindex(Data,1)) + tonumber(mvindex(Data,2)),
    1 == 1, Data
)
| sort Date

Let me know if that helps.
There are probably other ways to do this I'm sure. I'm thinking about join, maybe eventstats, etc.

Thanks,
J

Re: How to insert rows with eval calculated fields in a time series table?

Stevelim — Tue, 23 Feb 2016 15:09:25 GMT

Thank you so much! This works well for me!

Re: How to insert rows with eval calculated fields in a time series table?

Stevelim — Tue, 23 Feb 2016 15:29:57 GMT

Am I right to say that this command will only work if the data order is consistent? What if the order of the data? i.e. the data Type may be dynamic as more data types are added to the search.

Re: How to insert rows with eval calculated fields in a time series table?

javiergn — Tue, 23 Feb 2016 15:35:36 GMT

You don't need to order your data because the appendpipe with the stats function is already grouping by Date and will take this into account.

If data Type is dynamic then you might be better off using a join query, but there's a limit in the number of rows it can process. There are other options I'm sure but I would need to think a bit more about it.

Re: How to insert rows with eval calculated fields in a time series table?

javiergn — Tue, 23 Feb 2016 17:00:37 GMT

UPDATE: Fixed an issue on my query above.

Instead of

stats values(Data)

It should say:

stats list(Data)

Otherwise you'll be in trouble in there are common values as values will only return 1 entry per unique value.

Re: How to insert rows with eval calculated fields in a time series table?

Stevelim — Wed, 24 Feb 2016 03:48:32 GMT

UPDATE: Fix for dynamic

Base search | 

 | eval temp =  case(                        
 match(Type ,"A"), Data * -1,                   <== Suppose you want -A,-B,-C
 match(Type ,"B"), Data * -1, 
 match(Type ,"C"), Data * -1, 
 match(Type ,"D"), 0,                                   <= Remove unwanted data if any
 1==1, Data
 ) 

| appendpipe [stats sum(temp) as Data by Date | eval Type  = "Others"]  | eval Type  = split(Type , ",") <== sums up temp and put it back to the correct Keys

 | eval temp2 =  case(                        <== Repeat for another variable
 match(Type ,"A"), Data * 1,                    
 match(Type ,"B"), Data * 1, 
 match(Type ,"C"), Data * 1, 
 match(Type ,"D"), 0,                                  
 1==1, Data
 ) 

| appendpipe [stats sum(temp2) as Data by Date | eval Type  = "Others"]  | eval Type  = split(Type , ",") <== sums up temp and put it back to the correct Keys