Splunk Search

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

DEAD_BEEF
Builder

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

  1. Source IP
  2. Count
  3. Destination IP
  4. Destination Port
  5. Unique URLs
  6. URLs

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

  1. Count
  2. Source IP
  3. Destination IP
  4. Destination Port
  5. Unique URLs
  6. 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"
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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)

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

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)

martin_mueller
SplunkTrust
SplunkTrust

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.

DEAD_BEEF
Builder

Ok, now I understand! Thanks for the clarification.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

alt text

DEAD_BEEF
Builder

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.

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...