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

[Stevelim]

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?

[javiergn · Accepted solution]

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

[Stevelim]

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.

[javiergn]

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.

[javiergn]

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.

[Stevelim]

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

[Stevelim]

Thank you so much! This works well for me!