I have a search where the transaction status of a policy was set to FAIL.
It was processed manually and now it has changed to SUCCESS.
I get the failed policies by the below search.
index="prod" sourcetype="PRD:syslog" PROXYNAME="UAPI" URI="/vehicle/DeviceFullFillment" (TransactionStatus=Fail)| fields PolicyNumber by TransactionStatus
There are couple of policies which both have TransactionStatus=FAIL and TransactionStatus=SUCCESS.
How do I use "AND" operator or any other way to list all the policies which has both the status (FAIL and SUCCESS)
Hi athorat,
based on your comments I did update the answer, so try this:
index="prod" sourcetype="PRD:syslog" PROXYNAME="UAPI" URI="/vehicle/DeviceFullFillment" TransactionStatus=fail OR TransactionStatus=success
| stats count(TransactionStatus) AS myCount by PolicyNumber, TransactionStatus
| where myCount>="2"
| table PolicyNumber TransactionStatus
This will search all PolicyNumber
which have either TransactionStatus=fail
or TransactionStatus=success
and count them by PolicyNumber
, the where
claus will get back all PolicyNumber
which have a count of more or equal of 2
and the shows the result as table
.
Hope this matches your requirements ...
btw values are case in-sensitive that's why you can search for TransactionStatus=Fail
or TransactionStatus=FAIL
or TransactionStatus=fAil
and all will return the same events.
cheers, MuS
Hi @MuS
We are using Join to filter the data as the query further got enhanced.
index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment" TransactionStatus=FAIL | join type=left CorrelationId [search index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment" TransactionStatus !=FAIL] | search TransactionStatus=FAIL TransactionStatus=FAIL | append [search index="dpa" sourcetype="DP:PROD:SYSLOG" PROXYNAME="UBIAPI" URI="/vehicle/DeviceFullFillment" TransactionStatus !=FAIL]
just to add, this will also work:
index="prod" AND sourcetype="PRD:syslog" AND PROXYNAME="UAPI" AND URI="/vehicle/DeviceFullFillment" AND TransactionStatus=fail AND TransactionStatus=success | fields PolicyNumber by TransactionStatus
Thanks for the reply @Mus
Well that some how does not return any results.
If I use PolicyNumber=ABC prefix TransactionStatus=* before the pipe it shows both the status
Anything which you can think of?
Does your events really contain both fields like TransactionStatus=fail
and TransactionStatus=success
in the same one single event?
@MuS, no those are two different events.
TransactionStatus=FAIL|TransactionDesc=AAA_RUNTIME_ERROR|EventCreationDate=2015-07-27T18:26:51Z|Latency=2.119|RiskState=AZ|PolicyNumber=AZSS203728899|ClubCode=002|RespData=ERROR|ErrorVINs=1G1JC6SH8F4157755|ErrorCode=UNEXPECTED_EXCEPTION|ErrorDesc=Octo did not retrurn voucher number and vin. - PolicyNumber:AZSS2
Second Event
TransactionStatus=SUCCESS|TransactionDesc=UBI Device Enrollment Response is sent to Reply Queue for all the requested vehicles|EventCreationDate=2015-07-27T18:26:53Z|Latency=6.782|RiskState=AZ|PolicyNumber:AZSS2
Okay in this case you need to use the OR
in the search instead of AND
like this:
index="prod" sourcetype="PRD:syslog" PROXYNAME="UAPI" URI="/vehicle/DeviceFullFillment" TransactionStatus=fail OR TransactionStatus=success | fields PolicyNumber by TransactionStatus
Well as I was mentioning earlier we have 4 out of 121 policy numbers who has transaction status failed and success. If I use OR it returns all of the 121 events
Got it now - I think 🙂 See my updated answer ...