How to combine foreach and fieldformat?

[HeinzWaescher]

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...

[jeffland · Accepted solution]

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>>')]

[HeinzWaescher]

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.

[jeffland]

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.

[HeinzWaescher]

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

[jeffland]

With the above search? That shouldn't happen.

[HeinzWaescher]

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

[jeffland]

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

[HeinzWaescher]

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?

[jeffland]

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

[HeinzWaescher]

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:

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

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

[jeffland]

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

[HeinzWaescher]

I will try to join

[HeinzWaescher]

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