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

[aferone]

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!

[DalJeanis · Accepted solution]

@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]

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

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

[DalJeanis]

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]

Hmmmm. I am still getting no results.