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

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

dbcase — Wed, 26 Sep 2018 22:42:34 GMT

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?

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

renjith_nair — Thu, 27 Sep 2018 03:15:43 GMT

@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

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

DalJeanis — Thu, 27 Sep 2018 03:50:19 GMT

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.

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

dbcase — Thu, 27 Sep 2018 15:34:59 GMT

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

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

dbcase — Thu, 27 Sep 2018 15:38:04 GMT

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

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

renjith_nair — Fri, 28 Sep 2018 12:40:07 GMT

@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

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

dbcase — Fri, 28 Sep 2018 15:27:41 GMT

yep looks like this

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

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

dbcase — Fri, 28 Sep 2018 15:28:05 GMT

ugh the formatting leaves a lot to be desired

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

renjith_nair — Sat, 29 Sep 2018 02:49:08 GMT

@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?