Could you help me make a query which returns results on devices that fail and succeed?

[dbcase]

Hi,

I have this query that counts the type of failure for a given device, which works just fine.

index=wholesale_app DynamicChoice Properties.index=3 buildTarget=blah product=*|stats count by Properties.args{}| appendpipe [stats count | where count=0]

The request is if the device fails and retries (could be several times) but ultimately succeeds then don't count it as a failure.

The data looks like this for a failure

{   [-] 
     Properties:    {   [-] 
         args:  [   [-] 
         CONNECTION_FAILURE 
        ]   
         category:   Event  
         index:  3  
    }   
     analyticType:   DynamicChoice  
     buildTarget:    cox    
     clientSessionId:    DZLPTNZ-XQGUW  
     product:    Converge   
}

and if it succeeds it would look like this

{   [-] 
     Properties:    {   [-] 
         args:  [   [-] 
         CONNECTED  
        ]   
         category:   Event  
         index:  2  
    }   
     analyticType:   DynamicChoice  
     buildTarget:    cox    
     clientSessionId:    DZLPTNZ-XQGUW  
     product:    Converge   
}

The client session ID is the common field. Properties.index=3 contains all the failures while Properties.index=2 contains all the successes. How would I go about doing this?

[DalJeanis]

This construction...

 | stats count | where count=0 

will never return anything. stats can only count what is there, so if nothing is there, it cannot be counted.

Try something like

 index=wholesale_app DynamicChoice (Properties.index=3 OR Properties.index=2) buildTarget=blah product=*
| stats count by Properties.args{} Properties.index

I suspect you may also need to include an spath command to interpret the json before the stats command.

[dbcase]

Hi, This query works but doesn't seem to correlate a session where it failed to connect multiple times but then succeeds

[renjith_nair]

@dbcase,

Try this if you just want failure count

index=wholesale_app DynamicChoice buildTarget=cox product=*
|stats latest(Properties.index) as StatusIndex,latest(Properties.args{}) as TypeOfFailures by clientSessionId
|where StatusIndex=3|stats count by TypeOfFailures

And try this for both success and failures

index=wholesale_app DynamicChoice buildTarget=cox product=*
|stats latest(Properties.index) as StatusIndex,latest(Properties.args{}) as TypeOfFailures by clientSessionId
|stats count(eval(if(StatusIndex==3,clientSessionId,null()))) as Failures,
       count(eval(if(StatusIndex==2,clientSessionId,null()))) as Success by TypeOfFailures

[dbcase]

Hmmmm the success and failure query above does output a stat table but has numerics in the type of failures column and success/failures always is zero

[renjith_nair]

@dbcase,
are you getting result after

 index=wholesale_app DynamicChoice buildTarget=cox product=*
 |stats latest(Properties.index) as StatusIndex,latest(Properties.args{}) as TypeOfFailures by clientSessionId

[dbcase]

yep looks like this

clientSessionId StatusIndex TypeOfFailures
DZLPUTF-ALWMOVJ 15 1726
DZLQZKA-WSFXAP 15 9385
DZLTKFI-BQWWEN 13 3152
DZLUBKP-ALZFENT 2 STREAMING

[dbcase]

ugh the formatting leaves a lot to be desired

[renjith_nair]

@dbcase, thanks for that. From the output, the last record has a type of failure as "streaming" and gives me a 1 under success since the StatusIndex is 2. So it works for the last record.

TypeOfFailures  Failures    Success
1726    0   0
3152    0   0
9385    0   0
STREAMING   0   1

Now for others (3 out of 4), the type of failures are coming as integers. What do you have in Properties.Args? Is that field extracted from JSON and has only the string values?