Splunk Search

Formatting Money in Histogram

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

SplunkTrust
SplunkTrust

@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

SplunkTrust
SplunkTrust

@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

Explorer

Thank you. This is perfect.

0 Karma