Splunk Search

Why am I unable to format chart field?

MarcG
Explorer

I'm attempting to chart a maximum duration by server and event_type, and I'd like to display the duration in HH:MM:SS format rather than a number of seconds. However, fieldformat doesn't seem to be applying the change to the assigned duration field. Is there a way to do this?

Here's the command:

 

 

index=s3batchtest eventcode Open | extract pairdelim="," kvdelim="=" | eval bDate=strptime(beginDate,"%Y-%m-%d %H:%M:%S") | eval lDate=strptime(lastDate,"%Y-%m-%d %H:%M:%S") | eval eventAge=lDate - bDate | chart max(eventAge) AS eventDuration by server eventCode limit=0 | fieldformat eventDuration=toString(eventDuration, "duration")

 

 

 

Labels (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

I should have spotted this from your original illustration:  eventDuration doesn't exist as a field name in chart command; in other words, "AS eventDuration" may as well be omitted.

 

| chart max(eventAge) AS eventDuration by server eventCode limit=0

 

 is equivalent to

 

| chart max(eventAge) over server by eventCode limit=0

 

The output contains a field named "server", and multiple fields each named after one distinct eventCode. (You can see these headers in statistics table.)  Assuming that you don't have pure integer number as server name, you can do something like

 

| chart max(eventDuration) over server by eventCode
| foreach *
    [eval <<FIELD>> = if(isint(<<FIELD>>), tostring(<<FIELD>>, "duration"), <<FIELD>>)]

 

Unlike fieldformat, after eval, you won't be able to use those values as numerals until you convert them back.  But fieldformat doesn't work in foreach.

View solution in original post

Tags (1)

yuanliu
SplunkTrust
SplunkTrust

Shouldn't it be

| fieldformat eventDuration=strftime(eventDuration, "%H:%M:%S")

 instead? toString would have no way to know that you want it in HH:MM:SS.

Tags (1)
0 Karma

MarcG
Explorer

toString(<value>, "duration") should format the time as HH:MM:SS, as described here - https://docs.splunk.com/Documentation/Splunk/7.2.1/SearchReference/ConversionFunctions

0 Karma

yuanliu
SplunkTrust
SplunkTrust

You are correct. (I didn't quite grasp "duration" as a directive.)  In that case, you'll need to examine content of bDate and lDate.  Is it possible that one of them is null?  Also examine values of beginDate and lastDate.  Is one of them null or multivalued?

Otherwise, it should function like the this emulation.

| makeresults
| eval beginDate = "2023-04-05 14:32:30", lastDate = "2023-04-10 06:20:11"
``` the above emulates index=s3batchtest eventcode Open | extract pairdelim="," kvdelim="=" ```
| eval bDate=strptime(beginDate,"%Y-%m-%d %H:%M:%S")
| eval lDate=strptime(lastDate,"%Y-%m-%d %H:%M:%S")
| eval eventAge=lDate - bDate
| chart max(eventAge) AS eventDuration
| fieldformat eventDuration=toString(eventDuration, "duration")

 

0 Karma

MarcG
Explorer

While none of the dates in the actual log entries are null, it is the case that not all events occur on all servers, so there are some chart results that evaluate as null. Interestingly, if I follow the chart block with

fillnull value=0

then the empty chart entries are replaced with 0 as expected, but if I use

fillnull value=0 eventDuration

then they aren't, which leads me to believe that the chart results aren't actually being assigned to the eventDuration field, which would explain why my fieldformat command isn't working.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

I should have spotted this from your original illustration:  eventDuration doesn't exist as a field name in chart command; in other words, "AS eventDuration" may as well be omitted.

 

| chart max(eventAge) AS eventDuration by server eventCode limit=0

 

 is equivalent to

 

| chart max(eventAge) over server by eventCode limit=0

 

The output contains a field named "server", and multiple fields each named after one distinct eventCode. (You can see these headers in statistics table.)  Assuming that you don't have pure integer number as server name, you can do something like

 

| chart max(eventDuration) over server by eventCode
| foreach *
    [eval <<FIELD>> = if(isint(<<FIELD>>), tostring(<<FIELD>>, "duration"), <<FIELD>>)]

 

Unlike fieldformat, after eval, you won't be able to use those values as numerals until you convert them back.  But fieldformat doesn't work in foreach.

Tags (1)

MarcG
Explorer

That's also interesting, since the spec for the chart command does say that you can assign the results of the aggregate to a field value using the AS command. However, I did try your suggestion, and after a few tweaks (refining the eval of eventAge to produce an integer instead of a float), the report is now providing the results I wanted. Thank you very much for your help!

0 Karma

yuanliu
SplunkTrust
SplunkTrust

There are uses of AS field name in chart command, just not with both over and by.

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...