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!

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