Splunk Search

inner join/append with two Where commands.

sbattista09
Contributor

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

lguinn2
Legend

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.

View solution in original post

lguinn2
Legend

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
Contributor

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
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...