How to get count of success and failure?

[Jasmine]

i have json input 

Please find the Query  below:

...

...

| stats values(*) as * by Id| eval Status=if(match(Error,"^[a-zA-Z0-9_]"),"Failure","Success")| stats Count by Dept,Status

i can print as below in dashboard

Dept	Status	Count
Accounts	Success	4
Accounts	Failure	7
Mechanical	Success	4
Mechanical	Failure	4

i want to print as below:

Dept	Success	Failure	total
Accounts	5	1	6
Mechanical	6	2	8

Please help here

[GaetanVP]

Hello @Jasmine,

Could you please share the JSON you have in inputs please ? Would be helpful to do some tests and give you an answer !

Thanks
GaetanVP

[Jasmine]

2023-07-13 07:53:15,155 - __main__ - INFO - "{\"Id\": \"123456JKL\", \"Dept\": \"Accounts\", \"Time1\": \"3.04\"}"
2023-07-13 07:53:15,155 - __main__ - INFO - "{\"Id\": \"123456JKL\", \"Dept\": \"Mechanical\", \"Time2\": \"4.05\"}"
2023-07-13 07:53:15,155 - __main__ - INFO - "{\"Id\": \"123456JKL\", \"Dept\": \"Mechanical\",\"Error\": \"ErrorFound\"}"

Here i am  extracting each field and showing in table. if error found, adding Status as Success and Failure as below

| stats values(*) as * by UniqueId| eval Status=if(match(Error,"^[a-zA-Z0-9_]"),"Failure","Success")| stats Count by PhaseName,Status

with the above code 

Dept	Status	Count
Accounts	Success	4
Accounts	Failure	7
Mechanical	Success	4
Mechanical	Failure	4

But i want like this:

Dept	Success	Failure	total
Accounts	5	1	6
Mechanical	6	2	8

[Thulasinathan_M]

Hi,

If your trying to match word 'Error' and it's followed by your Dept then below query would do the trick.

| rex field=Logs "Dept\W+(?<Dept>\w+)\W+(?<Status>\w+)"
| eval Status=if(match("Error", Status),"Failure","Success") 
| stats count(eval(Status="Success")) as Success, count(eval(Status="Failure")) as Failure by Dept
| eval Total = coalesce(Success, 0) + coalesce(Failure, 0)
| table Dept, Success, Failure, Total

[Jasmine]

i am getting as below:

Dept	Success	Failure	total
Accounts	5	0	5
Accounts	0	1	1
Mechanical	0	6	6
Mechanical	2	0	2

i want as 

Dept	Success	Failure	total
Accounts	5	1	6
Mechanical	6	2	8

[Jasmine]

Please ignore the above query. It works perfect

[Thulasinathan_M]

If the query worked, you can mark it as a solution. It'll will help someone who refers similar thing in future. Cheers!!