Let's say there's saved lookup table that looks like this:
month number1 number2
2012.05 10 40
2012.06 20 50
2012.07 30 60
We need to join it with another search data and use join command:
| inputlookup my_table
| join type=outer [search source="my_source_1"
| eval num1=70
| eval month="2012.08"
| stats first(num1) as number1 by month ]
| join type=outer [search source="my_source_2"
| eval num2=80
| month="2012.08"
| stats first(num2) as number2 by month ]
| eval summ=number1+number2
As a result we get:
month number1 number2 summ
2012.05 10 40 50
2012.06 20 50 70
2012.07 30 60 90
So just values from lookup table were evaluated.
What's wrong here and how can I get the desired table with results of all four months?
P.S. with append
instead of join
one gets this:
month number1 number2 summ
2012.05 10 40 50
2012.06 20 50 70
2012.07 30 60 90
2012.08 70
2012.08 80
Even using summary indexing I didn't get the result. But then I finally realised what was the case: in the structure above splunk couldn't sum results the way I wanted him to do. Using addtotals solved the problem. Current working variant looks like:
index=summary source=my_source_1 OR source=my_source_2 | stats first(num1) as number1, first(num2) as number2 by month
| addtotals row=t number1 number2
And Total
that it returned was the desired summ
.
Even using summary indexing I didn't get the result. But then I finally realised what was the case: in the structure above splunk couldn't sum results the way I wanted him to do. Using addtotals solved the problem. Current working variant looks like:
index=summary source=my_source_1 OR source=my_source_2 | stats first(num1) as number1, first(num2) as number2 by month
| addtotals row=t number1 number2
And Total
that it returned was the desired summ
.
You could also use stats a second time, as in
index=summary source=my_source_1 OR source=my_source_2
| stats first(num1) as number1, first(num2) as number2 by month
| stats sum(number1) as total1 sum(number2) as total2
but then you would only get the totals
If your lookup table is saving data from prior months, I'd recommend using summary indexing instead. I think you could avoid this entire problem. But this will work:
| inputlookup my_table
| append type=outer [search source="my_source_1"
| eval num1=70
| eval month="2012.08"
| stats first(num1) as number1 by month ]
| append type=outer [search source="my_source_2"
| eval num2=80
| month="2012.08"
| stats first(num2) as number2 by month ]
| stats sum(number1) as Number1 sum(number2) as Number2 by month
| eval summ=Number1+Number2
This looks at each entry as something to be summed into the final table. It's a variant on Ayn's suggestion.
happy to help!
Though summary indexing wasn't the medicine in this case, it's hard to overestimate the value of your answer! It pushed me to learn and try all the mighty of summary indexing. Now I'm widely using it and make a refactoring of the older searches. Thank you so much!
Nope. But I've also tried with append and got this:
month number1 number2 summ
2012.05 10 40 50
2012.06 20 50 70
2012.07 30 60 90
2012.08 70
2012.08 80
Might you be confusing join
with append
here?