topic Re: How do you format numbers produced by 'count over'? in Splunk Search

How do you format numbers produced by 'count over'?

dpadams — Mon, 28 Sep 2020 10:14:52 GMT

I've got a collection of Web log data where we like to see the URLs counted by host:

sourcetype="access_common" | chart count over uri_path by host

We've got a couple of different server types handling different sorts of URLs, so the table we get out of this query is a helpful way to see the URLs-by-machine very easily. You get a table with the uri_path (URL) listed in the left-hand column and then one column for each of our servers. So:

uri_path Server_1 Server_2
/action/do_this 897 12345
/action/do_that 23456 18001

The "Server_1" and "Server_2" names are extracted from the host field for the event and the counts are calculated by Splunk.

Can anyone tell me how to format the resulting numbers? Ideally, I'd like to use commas in large numbers and put a - or an empty string for zeros. I've been eperimenting with fieldFormat, eval, and tostring and, in this case, had no luck. I think part of the problem is that I don't know how to address the generated columns. They're not fields, they're values extracted by fields. If I try applying formatting to "Server_1" and so on (not an ideal solution) it doesn't work.

Is there a way to apply numeric formatting to an extracted/calculated series like this?

Thanks for any suggestions or pointers.

Re: How do you format numbers produced by 'count over'?

lguinn2 — Tue, 27 Dec 2011 11:50:25 GMT

The only way that I have found to do this is

sourcetype="access*" | stats count by host uri_path | fieldformat count=tostring(count,"commas")

but you will lose the nice row vs. column formatting that the chart command gives you. Of course, if you have more than a few hosts, you might like the stats output better for looking at the data points. The chart command output will be easier to see as a graphic.

Like you, I could not figure out how to address the generated columns in the chart command output.

Re: How do you format numbers produced by 'count over'?

gkanapathy — Tue, 27 Dec 2011 15:47:09 GMT

you can convert stats-type output to charts-type row/column headers with the | xyseries search command, applied after the re-formatting.

Re: How do you format numbers produced by 'count over'?

gkanapathy — Tue, 27 Dec 2011 15:49:05 GMT

specifically, in this case: ... | xyseries host uri_path count to do it.

Re: How do you format numbers produced by 'count over'?

gkanapathy — Tue, 27 Dec 2011 15:51:20 GMT

Try this:

sourcetype="access_common" | stats count by host uri_path | fieldformat count=tostring(count,"commas") | xyseries host uri_path count

Or, if you're happy with the stats-style row/column layout, you can drop the xyseries command.

Re: How do you format numbers produced by 'count over'?

lguinn2 — Tue, 27 Dec 2011 20:32:28 GMT

I like the xyseries command, but if you run it, you will see that you get an extra column lablel "count" with no values. What's up with that?

Re: How do you format numbers produced by 'count over'?

dpadams — Tue, 27 Dec 2011 22:15:36 GMT

Thanks for the answers and comments. I reversed the host and uri_path arguments to keep the URLs on the left. The output looks better, but I'm not quite there yet:

There are still no commas.
As Iguinn noted, there's now a spare column with no data labeled count.
The zero values are now blank which is better than a blizzard of zeros. I'd like to try out putting in a dash as a placeholder. If that's not possible, I'll stick with the blanks.

Thanks for any additional help and suggestions - it's very much appreciated.

Re: How do you format numbers produced by 'count over'?

dpadams — Mon, 28 Sep 2020 10:14:55 GMT

I've just stubmled across ctable, which may even be better than xyseries:

sourcetype="access_common"| ctable uri_path host | sort TOTAL DESC

I end up with a table like I was getting from my original 'chart' search with the addition of a grand total column.

Here again, I can't figure out what to pipe the results to or through to format the numbers in the series columns. This pipe does format the grand dotal column:

| fieldFormat TOTAL=tostring(TOTAL,"commas")

If I address a data column by the name Splunk gives it, the data is all set to empty.