How to edit my search to measure appendcols results by minutes or greater?

[bfong]

Hi all,

Looking to measure cache hit rate percentage of a source/sources, listing time, source, cache hit, total hits, and cache hit rate. All fields return the information I need, except for the time field, which returns results in seconds. Using time picker also returns the same results. Is there a way to provide broader data cuts than seconds? Thanks!

index="[myIndex]" source="[mySource]" status!=null earliest=-15m@h latest=now | stats count as total by source, time | appendcols [search index="[myIndex]" source="[mySource]" cache=HIT earliest=-15m@m latest=now | stats count as hit by source, time ]| eval perc=round((hit/total)*100,2) | fields time,source,hit,total,perc

[lguinn2 · Accepted solution]

You could do this much more efficiently with this search, and solve your problem as well:

index="[myIndex]" source="[mySource]" (status!=null OR cache=HIT) earliest=-15m@m latest=now 
| bin _time span=5m
| stats count(eval(cache="HIT")) as hit count(eval(status!="null")) as total by _time source
| eval perc=round((hit/total)*100,2) 

FYI, you are not testing whether or not the status is null - you are testing whether status!="null"
While this may effectively be the same thing, you should probably be testing status=*
which explicitly means "only return events with a value in the status field"
And in that case, an even better version of the search would be

index="[myIndex]" source="[mySource]" (status=* OR cache=HIT) earliest=-15m@m latest=now 
| bin _time span=5m
| stats count(eval(cache="HIT")) as hit count(eval(isnotnull(status))) as total by _time source
| eval perc=round((hit/total)*100,2)