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?
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.
Hi, This query works but doesn't seem to correlate a session where it failed to connect multiple times but then succeeds
@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
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
@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
yep looks like this
clientSessionId StatusIndex TypeOfFailures
DZLPUTF-ALWMOVJ 15 1726
DZLQZKA-WSFXAP 15 9385
DZLTKFI-BQWWEN 13 3152
DZLUBKP-ALZFENT 2 STREAMING
ugh the formatting leaves a lot to be desired
@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?