Splunk Search

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

Stevelim
Communicator

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?

1 Solution

javiergn
SplunkTrust
SplunkTrust

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

View solution in original post

javiergn
SplunkTrust
SplunkTrust

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
Communicator

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.

0 Karma

javiergn
SplunkTrust
SplunkTrust

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.

0 Karma

javiergn
SplunkTrust
SplunkTrust

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.

0 Karma

Stevelim
Communicator

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

Stevelim
Communicator

Thank you so much! This works well for me!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...