Stats for multiple fields in the same table?

[Nidd]

I have logs like below:

{ [-]
   TransactionName: "my TransactionName"
   type1Error: NA
   eventTime: 2023-02-28 11:16:52.961
   type2Error: NA
   type3Error: NA
}

{ [-]
   TransactionName: "my TransactionName"
   type1Error: NA
   eventTime: 2023-02-28 11:16:52.961
   type2Error: Missing Field
   type3Error: NA
}

I have framed a below query:

index=my_idx | stats count by type1Error, type2Error, type3Error

Which gives me result like:

---------------------------------------------------------
type1Error		type2Error		type3Error		count
---------------------------------------------------------
NA				NA				NA				1
NA				NA				Missing Field	1
---------------------------------------------------------

But then, it would be better if I can bring it for success and failures separately. Like:

Create 2 new queries for errors with NA and not NA:

NA:

----------------------------------------------------
Success count
----------------------------------------------------
type1Error 2
type2Error 2
type3Error 1
----------------------------------------------------

Not NA:

----------------------------------------------------
Failure count
----------------------------------------------------
type1Error 0
type2Error 0
type3Error 1
----------------------------------------------------

How can we achieve this? Not getting a clear picture on how to frame a query for this. Tried using chart, but no luck !!

[ITWhisperer]

| eval count=mvrange(0, count)
| mvexpand count
| untable count error status
| eval status=if(status="NA","Success","Failure")
| chart count by error status

[bowesmana]

I always knew there'd be a simple way - must play with untable... 😁

[bowesmana]

You could do something like this

| foreach type*Error [ eval <<FIELD>>=if('<<FIELD>>'="NA", 0, 1) ]
| stats count sum(*) as *
| eval type="Failures"
| appendpipe [
  | foreach type*Error [ eval <<FIELD>>=count-<<FIELD>> ]
  | eval type="Success"
]
| fields - count
| transpose 0 header_field=type column_name=Type

which is generic and does not care how many types of error you have - in this case 3. It will put both success/fail in same table.

[bowesmana]

Or if you just want a single query for failures

... search ...
| stats sum(eval(if(type1Error!="NA",1,0))) as type1Error sum(eval(if(type2Error!="NA",1,0))) as type2Error sum(eval(if(type3Error!="NA",1,0))) as type3Error
| transpose 0 column_name=Type
| rename "row 1" as Failures

and change the stats for successes. Note that this means you always need to know the field names.

You can always use the previous query for both if you're going to use this in a dashboard by making it a base search and then just removing the field you don't want in the post processing search.