topic Re: How do you re-arrange columns from a "stats count" function? in Splunk Search

How do you re-arrange columns from a "stats count" function?

DEAD_BEEF — Thu, 16 Oct 2014 20:13:46 GMT

My existing query produces a table that has the following columns in this order:

Source IP
Count
Destination IP
Destination Port
Unique URLs
URLs

I'd like to rearrange the table so that it is:

Count
Source IP
Destination IP
Destination Port
Unique URLs
URLs

The trouble I'm having is that I can't find any documentation about how to use "stats count ______". I've found stats count by and stats count as but having trouble using them to how I would like and not finding any explanation on how to best use them, or why you would use one over the other.

The first column that I want is the total count, so how do I say "stats count by count?"

Existing query:

index=palo dst_port!=80 dst_port!=443 method=POST | regex url="^(\d{1,3}\.){3}\d{1,3}.+" | stats count AS "Total Hits", values(dst_ip), values(dst_port), dc(url), values(url) by src_ip | sort -"Total Hits"

EDIT: Solution:

index=palo dst_port!=80 dst_port!=443 method=POST | regex url="^(\d{1,3}\.){3}\d{1,3}.+" | stats count AS "Total Hits", values(dst_ip) AS "Dest. IP's", values(dst_port) AS "Dest. Port", dc(url) AS "Unique URLs", values(url) AS "URLs" by src_ip | rename src_ip AS "Source IP" | table "Total Hits", "Source IP", "Dest. IP's", "Dest. Port", "Unique URLs", "URLs", | sort -"Total Hits"

Re: How do you re-arrange columns from a "stats count" function?

martin_mueller — Thu, 16 Oct 2014 20:16:43 GMT

You can use table to reorder columns:

| stats count | eval dst_ip = "foo" | eval dst_port = "bar" | eval url = "baz" | eval src_ip = "whee"
| stats count AS "Total Hits", values(dst_ip), values(dst_port), dc(url), values(url) by src_ip
| table "Total Hits" src_ip values(dst_ip) values(dst_port) dc(url) values(url)

Re: How do you re-arrange columns from a "stats count" function?

DEAD_BEEF — Thu, 16 Oct 2014 20:27:07 GMT

Thanks for the quick response. Would you expand on a few things? I'm confused why you run stats count by itself, then do a bunch of evals, then do stats count again? I wasn't sure if you were just building an example or if this was required syntax.

Re: How do you re-arrange columns from a "stats count" function?

martin_mueller — Thu, 16 Oct 2014 20:34:26 GMT

The first line builds an example event. The second like is from your query. The third line is my solution, use table to reorder columns.

Re: How do you re-arrange columns from a "stats count" function?

DEAD_BEEF — Thu, 16 Oct 2014 20:35:48 GMT

Ok, now I understand! Thanks for the clarification.

Re: How do you re-arrange columns from a "stats count" function?

martin_mueller — Thu, 16 Oct 2014 20:48:35 GMT