How to determine age of events in frozen buckets?

[teedilo]

How can you determine the ages of events in frozen buckets? My first thought was to use dbinspect, but that only looks at hot, warm and cold buckets. I don't want to thaw the frozen buckets just to get info about them.

The first two components of bucket names are apparently the newest and oldest event times in "UTC epoch time (in seconds)". I've seen various posts in Splunk Answers about converting epoch times to human readable time, but in this case I don't really have data to search. I just want to convert the epoch times in the bucket names to human readable times.

I'm sure I could come up with an Excel formula that would do this easily enough, but I figured I would ask here in case someone already had something handy in this regard.

Thanks.

(I figured this would be a commonly asked question but I could find no evidence of this.)

[teedilo]

wmedeiros - I don't know whether you eventually figured this out, but there's a problem with the formula that you entered in cell C1. Instead of this:

=A1+A2/(24*60*60)

... it should be this:

=A1+B1/(24*60*60)

... and here's an improved version that includes formatting the result into a date format:

=TEXT(A1+B1/(24*60*60),"m/d/yyyy h:mm:ss")

Alternatively, you can use "Format Cells" to format the value in column C into a date, which is apparently what I had done when I had posted my original answer. Otherwise the value just comes out as a number.

nawazns5038 - Regarding your questions, I see that you asked them in a new topic here. That's good because I don't think they really pertain to this topic. Bear in mind that the techniques described in the answers to your questions will not help you determine the age of events in frozen buckets, however, since frozen buckets are no longer "seen" by Splunk, but you could use the techniques I described here to determine the ages of events in frozen buckets.

[wmedeiros]

Can you help me? I don't know what is wrong.

[teedilo · Accepted solution]

I just answered my own question. It really was simple to do this in Excel. For the benefit of anyone else who wants to do this in Excel:

1) Put this in cell A1:

1/1/1970

2) In cell B1, put the newest_time or oldest_time from a bucket name. For example, for the bucket name "db_1305515855_1272833281_22854", newest_time is 1305515855, and oldest time is 1272833281.

3) In cell C1, put this formula:

=A1+B1/(24*60*60)

4) If you want to convert this to your local time, then just add or subtract your local time's offset from UTC as necessary. I am in CST, and we are currently behind UTC by 6 hours, so in cell D1 I would put this:

=C1-TIME(6,0,0)

My desired result (for newest or oldest event time in the bucket) is now in cell D1.

5) Propagate the formulas down as desired. For example, it would be easy enough to get an entire list of buckets in a frozen location, extract the newest and oldest event times from the file names, list them in Excel, and propagate these formulas to get the human readable newest and oldest event times for all of them.

[nawazns5038]

how can we get the oldest index time of an index ?

Does retention policy depend on indextime or _time ?