Splunk Search

show multiple rows per one user

bcusick
Communicator

Hi,

I have data that gives these fields: user and error code.

I am trying to count the amount of certain errors PER user, so it would look like...

USER----------ERROR-----COUNT

user1

--------------error1-----5

--------------error2-----3

user2

--------------error1-----7

--------------error3-----9

0 Karma
1 Solution

kristian_kolb
Ultra Champion

You can do something like this to solve the problem of the double count by/group by

your_search | stats c by user, error | stats list(error) AS ERROR list(c) AS COUNT by USER

The result would look like;

USER      ERROR     COUNT
-------------------------
user1     error1    7
          error3    3
          error6    1
-------------------------
user2     error4    2
          error3    9

/K

View solution in original post

kristian_kolb
Ultra Champion

You can do something like this to solve the problem of the double count by/group by

your_search | stats c by user, error | stats list(error) AS ERROR list(c) AS COUNT by USER

The result would look like;

USER      ERROR     COUNT
-------------------------
user1     error1    7
          error3    3
          error6    1
-------------------------
user2     error4    2
          error3    9

/K

somesoni2
SplunkTrust
SplunkTrust

If the lookup is on User, just add a lookup command after the stats.

....| stats...by USER | lookup yourlookupfile.csv lookupField as USER OUTPUT .

This will just add the fields to existing output.

0 Karma

bcusick
Communicator

This worked so far...however I know I am going to have to add information from a lookup table. How can I produce multiple fields from a lookup table (on user) instead of just the stats this is giving?

0 Karma

gauldridge
Path Finder

The "c" is just shorthand for "count". Similarly, dc() is shorthand for distinct_count(). So, "| stats c by mdm_user,error_code" and "| stats count by mdm_user,error_code" would produce the same results except that the count columns would be named "c" and "count" respectively. If you pipe the results of this first stats command into something else, you just need to make sure you use either "c" or "count" depending on what you used in the stats table.

0 Karma

bcusick
Communicator

what is the "c" in "stats c by user, error"...do I replace this with "count"?

My fields are offically mdm_user, error_code, and I want the count of each error_code value per user. I have a search that pulls the list of errors per user in one row so far by using | transaction mdm_user |

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...