Count of distinct events by multiple values?

[Cuyose]

This seems easy but for some reason I guess I don't know how to ask the question.

I want a table that looks like this: Where the reason rows are error messages, and the column is another value "location" and I want to get the count of.
reason1, countofA, countofB, countofC, etc.
reason2, countofA, countofB, countofC, etc.

My current stats count by reason, location pipes out a table like below, which I do not want.
reason1,A,countofA
reason1,B,countofB
reason2,A,countofA
etc

[Cuyose · Accepted solution]

I got it, xyseries
| stats count(error) AS numOf by location error
|xyseries error,location,numOf

[Flynt]

Excellent! I see your data was not as I thought. I've upvoted your self answer!

[Cuyose]

Thanks! I knew I had used that before, just got lost in the shuffle, had a brain fart!

[fdi01]

try like this:

...| stats count(*) as countof* by reason | 

[Flynt]

Unfortunately this will just give the full count of the MVfield and not the values IN the MVfield.

[Flynt]

You might be able to get by with using mvexpand and chart

Here's a quick example using dummy values-

 |stats count|eval test="A,B,A"|eval reason="reason1"|makemv test delim=","|mvexpand test|chart count(test) by reason test

[Cuyose]

I need to explain better. If i want to list the count of each http status code by location, the commas are just to delineate columns. each column is a location and the intersection of the error code row and the location column is the count of those error for that location.

error code,location1,location2,location3,location4,location{n}
400,23,45,67,89,
403,etc...
404
500