topic Wierd results with rename in Splunk Search

Wierd results with rename

kmattern — Fri, 24 Jun 2011 18:45:42 GMT

This is really strange. It appears that I can either rename _time or format _time but not both. Here are the searches and partial results. Why does this happen?

sourcetype="solaris3-web-access" /XXYYZZ/ status="200" | timechart count(status) as Logins | convert timeformat="%H:%M:%S" ctime(Hours) as timestr

Results:

_time ....................................Logins

6/23/11 12:00:00.000 AM..........82

6/23/11 12:30:00.000 AM..........91

6/23/11 1:00:00.000 AM...........3

6/23/11 1:30:00.000 AM...........84

6/23/11 2:00:00.000 AM...........23

Now include the rename

sourcetype="solaris3-web-access" /uhpo/ status="200" | convert timeformat="%H:%M:%S" ctime(_time) as timestr | timechart count(status) as Logins | rename _time as Hours

And get this result:

Hours.....................Logins

1 1308805200..........82

2 1308807000..........91

3 1308808800..........3

4 1308810600..........84

5 1308812400..........23

What I want is the human readable time and the header to say Hours.

Re: Wierd results with rename

sideview — Fri, 24 Jun 2011 20:11:57 GMT

Assuming you dont actually have a field called 'Hours' here, I think your convert clause is having no effect.

The Splunk UI will automatically convert _time, which is always a number of seconds since 1/1/1970, into a localized string. So that's why the _time values in the first example look like "6/23/11 12:30:00.000 AM", and it doesnt have anything to do with your convert clause.

In the second example, by renaming _time you break this behavior so that now the values appear in their native form, namely as the number of seconds since 1/1/1970. Again the convert clause is not doing anything here either.

I think what you want is more like:

sourcetype="solaris3-web-access" /XXYYZZ/ status="200" | timechart count(status) as Logins | convert timeformat="%H:%M:%S" ctime(_time) as Hours | table Hours, Logins`

Re: Wierd results with rename

twinspop — Fri, 24 Jun 2011 20:13:24 GMT

I think the field name _time tells Splunk to make it human readable, so renaming it removes that built-in functionality. This might work for you:

sourcetype="solaris3-web-access" /uhpo/ status="200" | 
convert timeformat="%H:%M:%S" ctime(_time) as timestr| 
timechart first(timestr) as hours,count as Logins

Jon

Re: Wierd results with rename

woodcock — Sat, 02 May 2015 20:34:30 GMT

Try fieldformat:



... | fieldformat Hours=strftime(_time, "%H:%M:%S")

http://docs.splunk.com/Documentation/Splunk/6.2.2/SearchReference/Fieldformat

Re: Wierd results with rename

chimell — Sat, 02 May 2015 21:15:09 GMT

Hi kmattern
Try this search code

sourcetype="solaris3-web-access" /uhpo/ status="200" |timechart count(status) as Logins|appendcols[search sourcetype="solaris3-web-access" /uhpo/ status="200" | convert timeformat="%H:%M:%S" ctime(_time) as timestr|eval n=substr(timestr, 1, 8) ]|rename n as Hours| table Hours Logins