inner join/append with two Where commands.

[sbattista09]

I want to join two search's for an alert, I want to alert when the "difference " is above 30 AND the "Total_GB_Used "
is above 350, not sure how I would append or do an inner join as you can probably see. hoping someone would be able to give an example?

index=_internal earliest=-60m@m source=license_usage.log type="Usage" 
| eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) 
| eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) 
| eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) 
| bin _time span=1h | stats sum(b) as b by _time, pool, s, h, idx 
| search pool="Splunk Production" 
| timechart span=60m sum(b) AS volumeB by st fixedrange=false 
| bin _time span=1h 
| foreach  [eval <>=round('<>'/1024/1024/1024, 3)] 
| Rename NULL as count |tail 2| reverse 
| autoregress count 
| eval pct_increase=100 * (count - count_p1)/count 
| Rename "count_p1" as "Previous hour Count" 
| rename pct_increase as difference 
| join difference [search index=_internal earliest=-0d@d source=*license_usage.log type=Usage
         | timechart span=1d eval(round(sum(b)/1024/1024/1024,2)) AS Total_GB_Used] 
| Where difference > 30 
| Where Total_GB_Used > 350

[lguinn2 · Accepted solution]

I updated your search to remove some inefficiencies and redundancies:

 index=_internal earliest=-60m@m source=license_usage.log type="Usage" pool="Splunk Production" 
 | eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) 
 | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) 
 | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) 
 | bin _time span=1h | stats sum(b) as b by _time, pool, s, h, idx 
 | timechart span=60m sum(b)  by st fixedrange=false 
 | foreach  [eval <>=round('<>'/1024/1024/1024, 3)] 
 | rename NULL as count |tail 2| reverse 
 | autoregress count 
 | eval pct_increase=100 * (count - count_p1)/count 
 | rename "count_p1" as "Previous hour Count" 
 | rename pct_increase as difference 
 | join difference [search index=_internal earliest=-0d@d source=*license_usage.log type=Usage
          | timechart span=1d eval(round(sum(b)/1024/1024/1024,2)) AS Total_GB_Used] 
 | where difference > 30 and Total_GB_Used > 350

However, some serious flaws remain, and some things don't make sense to me:

  | bin _time span=1h | stats sum(b) as b by _time, pool, s, h, idx 
  | timechart span=60m sum(b)  by st fixedrange=false 

After the stats command, you will have only 6 fields: _time, pool, s, h, idx, b
So, the by st in the timechart command makes no sense as there is no field named st.
In fact, the entire timechart command makes no sense here - from the beginning of the search, you have been concerned with all of these other fields, but the timechart command (if it works) will obliterate them.

Between the first timechart command and the join, I can't comment because I am lost, but -
You can't join on a field that only exists in one of the searches. The subsearch produces no difference field, so the join will not work.
Plus, in the main search you are calculating on an hourly basis, and in the subsearch, it is daily.
Finally, you don't need two where commands, just combine the two expressions.

Suggestions:

• "Build" your search: start with just the search and run it. If that works, add the next command and run it. Repeat until something looks fishy. This will help you figure out what is going on...
• What output are you trying to achieve? What are you trying to compare? Your problem description is very abstract. The more concrete you can make it, the more likely that the Splunk community can help.

[sbattista09]

ended up rebuilding and then no longer needing. thanks for input.

index=_internal source=*license_usage.log type="Usage" | eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) | stats sum(b) as b by _time, pool, s, h, idx | search pool="SplunkProd" | timechart span=1h sum(b) AS volume  | eval "volume"=round (volume/1024/1024/1024, 2)| reverse | autoregress volume | eval pct_diff=1.00*(volume-volume_p1) | Where pct_diff > 10.0