Splunk Search

Formatting Money in Histogram

splunkqy
Explorer

We log money for amounts between $0.01 and $1,000,000,000.00. We are trying to format the histogram labels to show commas and remove trailing zeroes though. This is giving us a problem.

alt text

Our query is:

index="<index-name>"
| search <filter-condition>
| where cost > 0
| bin cost span=1log10
| eval Amount="$".cost
| chart count as "Number of Transactions" by Amount

This causes a problem with the amount formatting on the x-axis though.

We tried this query too:

index="<index-name>"
| search <filter-condition>
| where cost > 0
| bin cost span=1log10
| eval Amount="$".tostring(cost, "commas")
| chart count as "Number of Transactions" by Amount

but nothing shows up in the visualization.

How can we remove the trailing zeroes in the decimal part and add commas to thousands parts?

0 Karma
1 Solution

niketn
Legend

@splunkqy try the following:

 index="<index-name>"
| search <filter-condition>
| where cost > 0 
| eval Amount=case(
    cost>=0.0 AND cost<1.0 ,"$0.0-$1.0",
    cost>=1 AND cost<10,"$1-$10",
    cost>=10 AND cost<100,"$10-$100",
    cost>=100 AND cost<1000,"$100-$1000",
    cost>=1000 AND cost<10000,"$1000-$10,000",
    cost>=10000 AND cost<100000,"$10,000-$100,000",
    cost>=100000 AND cost<1000000,"$100,000-$10,00,000",
    cost>=1000000 AND cost<10000000,"$10,00,000-$100,00,000",
    cost>=10000000 AND cost<100000000,"$100,00,000-$10,00,00,000",
    cost>=100000000 AND cost<1000000000,"$10,00,00,000-$100,00,00,000"
    ) 
| bin cost span=1log10 
| stats count as "Number of Transactions" by cost Amount 
| fields - cost
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@splunkqy try the following:

 index="<index-name>"
| search <filter-condition>
| where cost > 0 
| eval Amount=case(
    cost>=0.0 AND cost<1.0 ,"$0.0-$1.0",
    cost>=1 AND cost<10,"$1-$10",
    cost>=10 AND cost<100,"$10-$100",
    cost>=100 AND cost<1000,"$100-$1000",
    cost>=1000 AND cost<10000,"$1000-$10,000",
    cost>=10000 AND cost<100000,"$10,000-$100,000",
    cost>=100000 AND cost<1000000,"$100,000-$10,00,000",
    cost>=1000000 AND cost<10000000,"$10,00,000-$100,00,000",
    cost>=10000000 AND cost<100000000,"$100,00,000-$10,00,00,000",
    cost>=100000000 AND cost<1000000000,"$10,00,00,000-$100,00,00,000"
    ) 
| bin cost span=1log10 
| stats count as "Number of Transactions" by cost Amount 
| fields - cost
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

splunkqy
Explorer

Thank you. This is perfect.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...