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
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
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
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.
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?
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.
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 |