Splunk Search

help with using table and stats to produce query output

ebailey
Communicator

I need to take the output of a query and create a table for two fields and then sum the output of one field. The two fields are already extracted and work fine outside of this issue.

For example

eventtype=test-prd Failed_Reason="201" hoursago=4 | stats count by Failed_User | table Failed_User,count

works perfectly

I get a list of Failed_User with a count of how many times the field occurred in the query.

The problem is when I try to add the second field to the table

eventtype=test-prd Failed_Reason="201" hoursago=4 | stats count by Failed_User | table Failed_User,count,IP_ADDR

I get the same table as above but with no data under the column for IP_ADDR

How do I carry the data from the extracted field to fill in the column under IP_ADDR?

Every event with Failed_User also contains IP_ADDR

Thanks

Ed

Tags (3)
1 Solution

dwaddle
SplunkTrust
SplunkTrust

The stats command produces a statistical summarization of data. The reason your IP_ADDR field doesn't appear in your table command is because stats summarized your primary search into a smaller result set containing only a count for each value of Failed_User. What you might do is use the values() stats function to build a list of IP_ADDR for each value of Failed_User

eventtype=test-prd Failed_Reason="201" hoursago=4 
| stats count, values(IP_ADDR) as IP_ADDR by Failed_User 
| table Failed_User,count,IP_ADDR

Note - I am not sure the table command provides anything useful in this example.

View solution in original post

sawgata12345
Path Finder

eventtype=test-prd Failed_Reason="201" hoursago=4
| stats count, IP_ADDR by Failed_User
| table Failed_User,count,IP_ADD

even without values(IP_ADDR) works as above

Similar kind of output from access.log
source="access.log" host="cisco-virtual-machine" index="testaccess1" sourcetype="access_combined_wcookie" status=404|stats count by status,clientip | table count,status,clientip

0 Karma

dwaddle
SplunkTrust
SplunkTrust

The stats command produces a statistical summarization of data. The reason your IP_ADDR field doesn't appear in your table command is because stats summarized your primary search into a smaller result set containing only a count for each value of Failed_User. What you might do is use the values() stats function to build a list of IP_ADDR for each value of Failed_User

eventtype=test-prd Failed_Reason="201" hoursago=4 
| stats count, values(IP_ADDR) as IP_ADDR by Failed_User 
| table Failed_User,count,IP_ADDR

Note - I am not sure the table command provides anything useful in this example.

dwaddle
SplunkTrust
SplunkTrust

Awesome. Could you please click the checkbox to the left to mark the answer as correct? Thanks.

0 Karma

mendesjo
Path Finder

great explanation, I get it.. but I tried that values() suggestion with no luck.

0 Karma

ebailey
Communicator

perfect thanks for explaining how stats works. Your explanation is the best I have read.

0 Karma
Get Updates on the Splunk Community!

Discover Powerful New Features in Splunk Cloud Platform: Enhanced Analytics, ...

Hey Splunky people! We are excited to share the latest updates in Splunk Cloud Platform 9.3.2408. In this ...

Splunk Classroom Chronicles: Training Tales and Testimonials

Welcome to the "Splunk Classroom Chronicles" series, created to help curious, career-minded learners get ...

Access Tokens Page - New & Improved

Splunk Observability Cloud recently launched an improved design for the access tokens page for better ...