topic Re: Grouping and counting based on different values in Splunk Search

Grouping and counting based on different values

neerajs_81 — Mon, 01 Jul 2024 14:25:00 GMT

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?

Re: Grouping and counting based on different values

ITWhisperer — Mon, 01 Jul 2024 14:49:45 GMT

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

Re: Grouping and counting based on different values

neerajs_81 — Tue, 02 Jul 2024 06:40:58 GMT

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

Re: Grouping and counting based on different values

neerajs_81 — Tue, 02 Jul 2024 07:45:01 GMT

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

Re: Grouping and counting based on different values

neerajs_81 — Tue, 02 Jul 2024 08:06:12 GMT

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