dbinspect query help

[mschlapfer]

I'm trying to write a dbinspect query to calculate the # of days of data that is stored in our hot/warm storage partition and our cold storage partition, for each index. So for example trying to get results like this:

Index, Hot/Warm Days, Cold Days
_internal, 10, 80
os, 12, 78
etc.

OR

Index, Hot/Warm Earliest Time, Cold Earliest Time
_internal, %Y %m %d, %H:%M:%S, %Y %m %d, %H:%M:%S
os, %Y %m %d, %H:%M:%S, %Y %m %d, %H:%M:%S
etc.

Can anyone help me figure out how to do this with dbinspect?

thanks,
Marcel

[mschlapfer]

Thanks @Bselberg! This is great.

[Bselberg]

This query is going to be super slow going about it from a DB inspect way.
Make sure to set the time for this greater than your cold storage typical data age.
It's a 3 step join to make the table look nice. This would be something to put as a report.

| dbinspect index=* timeformat=%s 
| search state=hot 
| stats max(modTime) as recentHot min(modTime) as oldestHot count as hotbuckets max(endEpoch) as hotearliestEvent min(startEpoch) as hotoldestEvent by index 
| join index 
    [| dbinspect index=* timeformat=%s 
    | search state=warm 
    | stats max(modTime) as recentwarm min(modTime) as oldestwarm count as warmbuckets max(endEpoch) as warmearliestEvent min(startEpoch) as warmoldestEvent by index ] 
| join index 
    [| dbinspect index=* timeformat=%s 
    | search state=cold 
    | stats max(modTime) as recentcold min(modTime) as oldestcold count as coldbuckets max(endEpoch) as coldearliestEvent min(startEpoch) as coldoldestEvent by index ] 
| eval maxAgeHot=tostring((recentHot-oldestHot), "duration") 
| eval maxAgeWarm=tostring((recentwarm-oldestwarm), "duration") 
| eval maxIndexedEventAgeHot=tostring((hotearliestEvent-hotoldestEvent), "duration") 
| eval maxIndexedEventAgeWarm=tostring((warmearliestEvent-warmoldestEvent), "duration") 
| convert ctime(hotoldestEvent) 
| convert ctime(hotearliestEvent) 
| convert ctime(warmearliestEvent) 
| convert ctime(warmoldestEvent) 
| convert ctime(oldestcold) 
| convert ctime(recentcold) 
| convert ctime(recentwarm) 
| convert ctime(oldestwarm) 
| convert ctime(recentHot) 
| convert ctime(oldestHot) 
| sort -index 
| table index,hotbuckets,warmbuckets,coldbuckets,maxIndexedEventAgeHot,maxIndexedEventAgeWarm, maxAgeHot,maxAgeWarm,recentHot ,oldestHot ,oldestwarm ,recentwarm recentcold ,oldestcold,hotoldestEvent,hotearliestEvent,warmearliestEvent,warmoldestEvent