Splunk Search

Appendcols Search Doesn't Work if No Event in Main Search?

aferone
Builder

I've seen some other posts reference this, but I can't seem to get any of the solutions to work.

Here is the search:

| dbinspect index=foo state=hot
| stats sum(sizeOnDiskMB) as HotSize
| appendcols [ | dbinspect index=foo state=warm | stats sum(sizeOnDiskMB) as WarmSize]
| eval HotWarm = HotSize + WarmSize
| eval HotWarmTotal = HotWarm / 1024
| gauge HotWarmTotal 0 1.5 3 4.5 6 7.5

I am monitoring the size of our Hot/Warm buckets since we have them on a separate partition than cold. dbinspect doesn't allow to select hot AND warm buckets, so I use the above. When there is data in both hot and warm, this works fine.

However, for indexes with NO hot buckets, the entire search comes back empty, even though there are warm buckets with data.

How do I make sure the search comes back with warm bucket data even if there is no results for hot data?

Thanks!

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

@aferone -

Then just run this and see what you get ...

 | dbinspect index=foo 
 | eval HotSize = case(state="hot",sizeOnDiskMB) 
 | eval WarmSize = case(state="warm",sizeOnDiskMB)

If you get anything, then add this...

 | stats sum(HotSize) as HotSize, sum(WarmSize) as WarmSize
 | eval HotWarm = HotSize + WarmSize

Hmmm. I would expect sum() to give 0 if all records were null in one side, but if you get one value and one null, then redo this way and run the whole thing again...

   | dbinspect index=foo 
   | eval HotSize = if(state="hot",sizeOnDiskMB,0) 
   | eval WarmSize = if(state="warm",sizeOnDiskMB,0)

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

@aferone -

Then just run this and see what you get ...

 | dbinspect index=foo 
 | eval HotSize = case(state="hot",sizeOnDiskMB) 
 | eval WarmSize = case(state="warm",sizeOnDiskMB)

If you get anything, then add this...

 | stats sum(HotSize) as HotSize, sum(WarmSize) as WarmSize
 | eval HotWarm = HotSize + WarmSize

Hmmm. I would expect sum() to give 0 if all records were null in one side, but if you get one value and one null, then redo this way and run the whole thing again...

   | dbinspect index=foo 
   | eval HotSize = if(state="hot",sizeOnDiskMB,0) 
   | eval WarmSize = if(state="warm",sizeOnDiskMB,0)

aferone
Builder

You did it! The last query works! Thank you SO MUCH!

If you re-post as an answer, I will accept it! Thanks again!

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

I assume that there must be some use case where appendcols is the right tool, but I have never yet seen it.

Also, whenever you are going twice to the same index for different kinds of things, you can generally do the join with an eval structure instead with better performance.

Try this.

| dbinspect index=foo 
| stats sum(eval(case(state="hot",sizeOnDiskMB))) as HotSize 
        sum(eval(case(state="warm",sizeOnDiskMB))) as WarmSize
| eval HotWarm = HotSize + WarmSize
| eval HotWarmTotal = HotWarm / 1024
| gauge HotWarmTotal 0 1.5 3 4.5 6 7.5

Another way to write the same thing, if this style is more comfortable for you...

| dbinspect index=foo 
| eval HotSize = case(state="hot",sizeOnDiskMB)
| eval WarmSize = case(state="warm",sizeOnDiskMB)
| stats sum(HotSize) as HotSize, sum(WarmSize) as WarmSize
| eval HotWarm = HotSize + WarmSize
| eval HotWarmTotal = HotWarm / 1024
| gauge HotWarmTotal 0 1.5 3 4.5 6 7.5

aferone
Builder

Hmmmm. I am still getting no results.

0 Karma
Get Updates on the Splunk Community!

This Week's Community Digest - Splunk Community Happenings [9.26.22]

Get the latest news and updates from the Splunk Community here! Upcoming User Group Events! 👏 Check ...

BSides Splunk 2022 - The Call for Papers is now Open!

TLDR; Main Site: https://bsidessplunk.com CFP Site: https://bsidessplunk.com/cfp CFP Opens: December 15th, ...

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...