I've got a collection of Web log data where we like to see the URLs counted by host:
sourcetype="accesscommon" | chart count over uripath 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:
uripath Server1 Server2
/action/dothis 897 12345
/action/do_that 23456 18001
The "Server1" and "Server2" 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.
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.
you can convert
stats-type output to
charts-type row/column headers with the
| xyseries search command, applied after the re-formatting.
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
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?
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.
I've just stubmled across ctable, which may even be better than xyseries:
sourcetype="accesscommon"| ctable uripath 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.