Splunk Search

How to combine foreach and fieldformat?

HeinzWaescher
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

jeffland
SplunkTrust
SplunkTrust

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

jeffland
SplunkTrust
SplunkTrust

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

jeffland
SplunkTrust
SplunkTrust

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

HeinzWaescher
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

jeffland
SplunkTrust
SplunkTrust

With the above search? That shouldn't happen.

0 Karma

HeinzWaescher
Motivator

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

0 Karma

jeffland
SplunkTrust
SplunkTrust

"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

HeinzWaescher
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

jeffland
SplunkTrust
SplunkTrust

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

0 Karma

HeinzWaescher
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

jeffland
SplunkTrust
SplunkTrust

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

0 Karma

HeinzWaescher
Motivator

I will try to join

0 Karma

HeinzWaescher
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...