Splunk Search

How to combine foreach and fieldformat?

Motivator

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

0 Karma
1 Solution

Champion

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

View solution in original post

Champion

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

View solution in original post

Motivator

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.

0 Karma

Champion

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.

0 Karma

Motivator

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

0 Karma

Champion

With the above search? That shouldn't happen.

0 Karma

Motivator

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

0 Karma

Champion

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

0 Karma

Motivator

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?

0 Karma

Champion

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

0 Karma

Motivator

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.

0 Karma

Champion

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

0 Karma

Motivator

I will try to join

0 Karma

Motivator

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

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!