topic How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS? in Splunk Search

How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

athorat — Tue, 28 Jul 2015 00:35:56 GMT

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)

Re: How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

MuS — Tue, 28 Jul 2015 01:19:49 GMT

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

Re: How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

MuS — Tue, 28 Jul 2015 01:21:05 GMT

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

Re: How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

athorat — Tue, 28 Jul 2015 02:20:50 GMT

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?

Re: How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

MuS — Tue, 28 Jul 2015 03:07:10 GMT

Does your events really contain both fields like TransactionStatus=fail and TransactionStatus=success in the same one single event?

Re: How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

athorat — Tue, 28 Jul 2015 03:11:57 GMT

@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

Re: How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

MuS — Tue, 28 Jul 2015 03:13:46 GMT

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

Re: How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

athorat — Tue, 28 Jul 2015 04:27:01 GMT

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

Re: How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

MuS — Tue, 28 Jul 2015 08:45:58 GMT

Got it now - I think 🙂 See my updated answer ...

Re: How do I use the "AND" operator or any other way to list all values of a field that have both statuses FAIL and SUCCESS?

athorat — Tue, 28 Jul 2015 21:29:46 GMT

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]