Splunk Search

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

dbcase
Motivator

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?

Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

dbcase
Motivator

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!
0 Karma

dbcase
Motivator

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!
0 Karma

dbcase
Motivator

yep looks like this

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

0 Karma

dbcase
Motivator

ugh the formatting leaves a lot to be desired

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...