Splunk Search

Wierd results with rename

kmattern
Builder

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.

Tags (2)
0 Karma

chimell
Motivator

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, 😎 ]|rename n as Hours| table Hours Logins 
0 Karma

woodcock
Esteemed Legend

Try fieldformat:


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

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

0 Karma

twinspop
Influencer

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

0 Karma

sideview
SplunkTrust
SplunkTrust

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`

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...