topic Re: Unable to format chart field. in Splunk Search

Why am I unable to format chart field?

MarcG — Mon, 08 May 2023 10:01:23 GMT

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

Re: Unable to format chart field.

yuanliu — Sat, 06 May 2023 05:33:27 GMT

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.

Re: Unable to format chart field.

MarcG — Mon, 08 May 2023 13:14:44 GMT

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

Re: Unable to format chart field.

yuanliu — Tue, 09 May 2023 05:30:56 GMT

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

Re: Unable to format chart field.

MarcG — Tue, 09 May 2023 15:11:01 GMT

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.

Re: Unable to format chart field.

yuanliu — Wed, 10 May 2023 09:00:50 GMT

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.

Re: Unable to format chart field.

MarcG — Wed, 10 May 2023 15:12:21 GMT

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!

Re: Unable to format chart field.

yuanliu — Thu, 11 May 2023 07:30:36 GMT

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