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?
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
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
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.
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.
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.
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
Thank you so much! This works well for me!