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
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
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 |
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
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 |
Please ignore the above query. It works perfect
If the query worked, you can mark it as a solution. It'll will help someone who refers similar thing in future. Cheers!!