Splunk Search

can't join search data and inputlookup table data

iKate
Builder

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      
Tags (2)
1 Solution

iKate
Builder

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.

View solution in original post

0 Karma

iKate
Builder

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.

0 Karma

lguinn2
Legend

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

0 Karma

lguinn2
Legend

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.

lguinn2
Legend

happy to help!

0 Karma

iKate
Builder

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!

iKate
Builder

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

0 Karma

Ayn
Legend

Might you be confusing join with append here?

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 ...