Grouping and counting based on different values

[neerajs_81]

HI Team, i am caught in a maze of how to use stats function to get the data in expected format i want. 

Sample data.  We have alerts based on their different status values.  Alert and  status are field names.

Alert	values(status)	Total_Count
001_Phishing_Alert	In progress Resolved On-Hold	5
002_Malware_alert	In-progress Resolved	6
003_DLP_Alert	In-Progress	4

Desired / Expected output:  Want to split in based on each individual status value

Alert	Count	In-Progress	Resolved	On-Hold
001_Phishing_Alert	5	3	1	1
002_Malware_Alert	6	3	3	0
003_DLP_alert	4	4	0	0
Total	15	8	4	1

I am trying using 

|..base search
| stats count by Alert, status
....
OR 
|..base search..
| stats count, values(status) by Alert

 nothing is working out to show the desired output.  Can someone pls assist? 

[neerajs_81]

Quick followup question, is there a way to include another field ( as in column ) as part of the final output?

For example, if i have something like below where there is another field "Priority" calculated using eval, how to include it in the final output?   As of now,  using the below query,  Priority doesn't show any data and thats expected because Priority is not part of our chart command.  I tried all different combos to add Priority in the chart command but couldn't figure out how to.

| eval Priorty = case(Alert like "001","P1",Alert like "002","P2")
| chart count by Alert status
| addtotals col=t fieldname=Count label=Total labelfield=Alert
| table rule_name Count status Priority

[neerajs_81]

Never mind, i figured out. Just moved the |eval statement to the end after the table command, it worked.

[ITWhisperer · Accepted solution]

| chart count by Alert status
| addtotals col=t fieldname=Count label=Total labelfield=Alert

[neerajs_81]

Awesome. So often forget the "chart" command to use for such scenarios.   Thank you