https://community.splunk.com/t5/Splunk-Search/dbinspect-query-help/m-p/449806#M127358 <P>This query is going to be super slow going about it from a DB inspect way. <BR /> Make sure to set the time for this greater than your cold storage typical data age. <BR /> It's a 3 step join to make the table look nice. This would be something to put as a report. </P> <PRE><CODE>| 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 </CODE></PRE> Wed, 01 May 2019 15:46:32 GMT Bselberg 2019-05-01T15:46:32Z https://community.splunk.com/t5/Splunk-Search/dbinspect-query-help/m-p/449805#M127357 <P>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:</P> <P>Index, Hot/Warm Days, Cold Days<BR /> _internal, 10, 80<BR /> os, 12, 78<BR /> etc.</P> <P>OR</P> <P>Index, Hot/Warm Earliest Time, Cold Earliest Time<BR /> _internal, %Y %m %d, %H:%M:%S, %Y %m %d, %H:%M:%S<BR /> os, %Y %m %d, %H:%M:%S, %Y %m %d, %H:%M:%S<BR /> etc.</P> <P>Can anyone help me figure out how to do this with dbinspect?</P> <P>thanks,<BR /> Marcel</P> Tue, 30 Oct 2018 15:41:24 GMT https://community.splunk.com/t5/Splunk-Search/dbinspect-query-help/m-p/449805#M127357 mschlapfer 2018-10-30T15:41:24Z https://community.splunk.com/t5/Splunk-Search/dbinspect-query-help/m-p/449806#M127358 <P>This query is going to be super slow going about it from a DB inspect way. <BR /> Make sure to set the time for this greater than your cold storage typical data age. <BR /> It's a 3 step join to make the table look nice. This would be something to put as a report. </P> <PRE><CODE>| 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 </CODE></PRE> Wed, 01 May 2019 15:46:32 GMT https://community.splunk.com/t5/Splunk-Search/dbinspect-query-help/m-p/449806#M127358 Bselberg 2019-05-01T15:46:32Z https://community.splunk.com/t5/Splunk-Search/dbinspect-query-help/m-p/449807#M127359 <P>Thanks @Bselberg! This is great.</P> Mon, 13 May 2019 14:59:36 GMT https://community.splunk.com/t5/Splunk-Search/dbinspect-query-help/m-p/449807#M127359 mschlapfer 2019-05-13T14:59:36Z