Why am I unable to format chart field?

[MarcG]

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")

[yuanliu]

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.

[MarcG]

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

[yuanliu]

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")

[MarcG]

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.

[yuanliu · Accepted solution]

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.

[MarcG]

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!

[yuanliu]

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