show multiple rows per one user

[bcusick]

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

[kristian_kolb · Accepted solution]

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]

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.

[bcusick]

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?

[gauldridge]

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.

[bcusick]

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 |