Splunk Search

Subsearch doesn't return anything

MidnightRun
Explorer

I'm trying to write a search that will return a table where all average values of the field price grouped by Ids are lower then 1 month ago. This is my attempt:

 

index="myindex" sourcetype="mysourcetype" earliest=-1mon@mon latest=@mon | stats avg(Price) as avg by ID | where avg > [search index="myindex" sourcetype="mysourcetype" earliest=@d | stats avg(Price) as new_avg by ID | return $new_avg] | table *

This, however, always returns 0 results even though there are events in these time periods. I even tried subtituting the subsearched with a fixed number and that produces a table. Does anyone now why this isn't working and maybe how to fix it? 

Labels (5)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

There are a couple of ways (and perhaps more) to do that.

index="myindex" sourcetype="mysourcetype" earliest=-1mon@mon latest=@mon 
```Get the old average price```
| stats avg(Price) as avg by ID
```Get the new average price ```
| append [ search index="myindex" sourcetype="mysourcetype" earliest=@d 
  | stats avg(Price) as new_avg by ID ]
```Regroup the results```
| stats values(*) as * by ID
| where avg > new_avg
| table *

Or, if Price is an indexed field:

| tstats avg(Price) as avg where index="myindex" sourcetype="mysourcetype" earliest=-1mon@mon latest=@mon by ID
| tstats append=t avg(Price) as new_avg where index="myindex" sourcetype="mysourcetype" earliest=@d by ID
| where avg > new_avg 
| table *

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

PickleRick
SplunkTrust
SplunkTrust

Firstly, try rephrasing your search to not use subsearch at all. (Eventstats with eval, perhaps?) Subsearches are good sometimes (that's why they are there) but have limits and can be tricky.

But if you really want a subsearch, yours prepares an aggregate by ID but returns only the first value. Are you sure that's what you want?

0 Karma

MidnightRun
Explorer

I would like to use a subsearch yes, and returning the first value isn't what I want, any Idea how to fix it? 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Well, it ain't that easy. Subsearch returns either a "table" of results or values only but as a whole "result". You can't easily compare single field value to a set of values. (your "| where " condition). Why do you want to use the subsearch?

0 Karma

MidnightRun
Explorer

I don't see any other way of doing it but with subsearch. I need to compare average values from one timerange to average values of another time frame. 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

There are a couple of ways (and perhaps more) to do that.

index="myindex" sourcetype="mysourcetype" earliest=-1mon@mon latest=@mon 
```Get the old average price```
| stats avg(Price) as avg by ID
```Get the new average price ```
| append [ search index="myindex" sourcetype="mysourcetype" earliest=@d 
  | stats avg(Price) as new_avg by ID ]
```Regroup the results```
| stats values(*) as * by ID
| where avg > new_avg
| table *

Or, if Price is an indexed field:

| tstats avg(Price) as avg where index="myindex" sourcetype="mysourcetype" earliest=-1mon@mon latest=@mon by ID
| tstats append=t avg(Price) as new_avg where index="myindex" sourcetype="mysourcetype" earliest=@d by ID
| where avg > new_avg 
| table *

 

---
If this reply helps you, Karma would be appreciated.

MidnightRun
Explorer

This works, thank you very much. 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.

---
If this reply helps you, Karma would be appreciated.
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 ...