topic Re: How to combine foreach and fieldformat? in Splunk Search

How to combine foreach and fieldformat?

HeinzWaescher — Thu, 19 Nov 2015 09:10:21 GMT

Hi,

I've a timechart table for revenue grouped by product.

_time | productA | product B | product C

I would like to display all revenue values as 1,000 € instead of 1000.
Now I could do it like this, but I would have to know all possible products.

....
| fieldformat productA=tostring(productA, "commas")." €"

Is there a way to achieve this with the foreach command? I was not able to figure it out.

Best
Heinz

Update:
This formats all revenue values, but here the time field is empty:

| foreach * [fieldformat  <<FIELD>>=tostring('<<FIELD>>', "commas")." €"]

A possible workaround seems to be:

| eval time=strftime(_time,"%Y-%m-%d")
| addcoltotals labelfield=time label="TOTALS"
| addtotals fieldname="TOTALS"
| foreach * [fieldformat  <<FIELD>>=tostring('<<FIELD>>', "commas")." €"]
| foreach time [fieldformat  <<FIELD>>=tostring('<<FIELD>>')]

But it does not format the values every time I run the search. Sometimes it works, sometimes not...

Re: How to combine foreach and fieldformat?

jeffland — Tue, 29 Sep 2020 07:56:05 GMT

I can't exactly reproduce your problem, when I run this search:

index=_internal | timechart count by group | foreach * [fieldformat  <<FIELD>>=tostring('<<FIELD>>', "commas")." €"]

I get an intact _time field and all other fields are displayed as desired. However, when I use a different name for my _time values such as your time, then the foreach will also be applied to that field. Is there a reason you use time instead of _time?

If you have to, you could prevent this by either using a letter that all your products have in common that is different from t, i.e. if your product columns are actually called product_abc and product_def you could use foreach product_* and skip fieldformatting your field time by that. You could also use an if inside your subsearch to check if the current field name "time" and not change it if it is, such as this:

| foreach * [fieldformat  <<FIELD>>=if(<<MATCHSTR>>!=time, tostring('<<FIELD>>', "commas")." €", '<<FIELD>>')]

Re: How to combine foreach and fieldformat?

HeinzWaescher — Thu, 19 Nov 2015 13:16:41 GMT

The reason why I use time here, is because of the addcoltotals. I want to label the values as "TOTALS", but the _time fields does not accept the string.

The if-approach works fine so far, I haven't experienced wrong formats yet. Thanks!

Another question appeared, when I tried to send this dashboard as PDF. It seems that the PDF-Delivery doesn't like the format. When I preview this as PDF it looks fine, but the delivery per mail only shows one empty row in the PDF.

Re: How to combine foreach and fieldformat?

HeinzWaescher — Thu, 19 Nov 2015 13:26:43 GMT

I checked this without "€" and with "$" instead of it. Both times this works, so Splunk seems to have problem with the € sign here.

Re: How to combine foreach and fieldformat?

jeffland — Thu, 19 Nov 2015 13:34:22 GMT

I just tried, and I was able to add a "TOTALS" string to my _time field:

index=_internal | timechart count by group | table _time per_source_thruput pipeline queue | addcoltotals labelfield=_time label="TOTALS" | addtotals fieldname="TOTALS" | foreach * [fieldformat  <<FIELD>>=tostring('<<FIELD>>', "commas")." €"]

Anyway, glad we found a solution for you. Unfortunately, I can't really help you with the PDF delivery errors, maybe you should to open a separate question for that.

Re: How to combine foreach and fieldformat?

HeinzWaescher — Thu, 19 Nov 2015 13:38:33 GMT

Hm, in my search it looks like this:

_time

2015-11-16 00:00:00

2015-11-17 00:00:00

2015-11-18 00:00:00

0NaN-NaN-NaN NaN:NaN:NaN

Re: How to combine foreach and fieldformat?

jeffland — Thu, 19 Nov 2015 13:44:30 GMT

With the above search? That shouldn't happen.

Re: How to combine foreach and fieldformat?

HeinzWaescher — Thu, 19 Nov 2015 13:45:32 GMT

Yes, just copy & paste
We are currently still on 6.2.2, perhaps this is a new feature? 😉

Re: How to combine foreach and fieldformat?

jeffland — Thu, 19 Nov 2015 13:59:09 GMT

"It's not a bug, it's a feature" 🙂 Well this is strange. It works for me on 6.3.0 and on 6.2.3.

Re: How to combine foreach and fieldformat?

HeinzWaescher — Thu, 19 Nov 2015 14:00:52 GMT

I will check it with the next update.
Can you tell my why we are using eval instead of fieldformat in the foreach command here?

Re: How to combine foreach and fieldformat?

jeffland — Thu, 19 Nov 2015 15:20:51 GMT

Which search exactly? I don't see any eval.

Re: How to combine foreach and fieldformat?

HeinzWaescher — Tue, 29 Sep 2020 07:56:13 GMT

I think I've mixed something up. I must have changed all "fieldformat" with eval automatically, because the fieldformat doesn't work after foreach on my side.

When I use your example:

This does not change the values. But with eval instead fieldformat it does.

Re: How to combine foreach and fieldformat?

jeffland — Fri, 20 Nov 2015 10:21:40 GMT

Huh? That's strange... Time to take that to the IRC channel if you ask me 🙂

Re: How to combine foreach and fieldformat?

HeinzWaescher — Fri, 20 Nov 2015 10:29:34 GMT

I will try to join