How to use NOT in subsearch to find orders in search but not the other?

[scaparelli]

First, let me explain my intention:
I am attempting to create a query that would notify our team of a “stuck order”.  An order is “stuck” when one team has produced an event and another team has not responded to said event. In this specific case, one team is producing an AuthorizationSucceeded event and another team is expected to produce a FraudDeclined/Approved event. 

I have tried using map, but I need to find the orderId's that do not exist in the second search, so I have moved on to subsearches using NOT. Here is my current query, but it is not producing the results I want. Ideally I want a list of orderIds that exist in:

index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*"

but not in

index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*"" 

Here is my query thus far:

index=k8s_main container_name=fraud-single-proxy-listener message="Successfully handled AuthorizationSucceeded event*" NOT [search index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*" | rename properties.orderId as contextMap.orderId | table contextMap.orderId]

Any Help would be amazing

[Azmeentun]

You have a conceptually correct search.The problem is that you have a limit: A sub-search can yield a maximum of 50,000 results, which means the filtering may not be complete.

[PickleRick]

You're thinking about the problem like a programmer, not like a splunker 😉

But don't worry, it comes with experience.

The typical "splunk-way" approach would be to find all events, clasify them, summarize them and filter the results.

(index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*") OR (index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*")

This will give you al your events of both types. Now you have to normalize the id field so you can summarize over it.

| eval contextMap.orderId=if(index="app_pci",properties.orderId,contextMap.orderId)

Now you have all your events with a field contextMap.orderId. Since you can differentiate between the "types" of events we don't have to add amy artificial field so we can jump straight to summarization.

| stats values(index) as index by contextMap.orderId

Since you want only the ones which do are not included in the app_pci index you filter on that value

| search NOT index=app_pci

Mind you that this condition is not the same as index!=app_pci!

[scaparelli]

Having trouble setting the eval "contextMap.orderId". If I run:

(index=k8s_main container_name=fraud-single-proxy-listener message="Successfully handled AuthorizationSucceeded event*") OR (index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*") 
| eval contextMap.orderId=if(index="app_pci",properties.orderId,contextMap.orderId)
| table contextMap.orderId

I get rows but the contextMap.orderId = null 

[gcusello]

Hi @scaparelli,

Please try renaming the fields without using dot in the field name:

(index=k8s_main container_name=fraud-single-proxy-listener message="Successfully handled AuthorizationSucceeded event*") OR (index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*") 
| rename properties.orderId AS properties_orderId contextMap.orderId AS contextMap_orderId
| eval contextMap_orderId=if(index="app_pci",properties_orderId,contextMap_orderId)
| table contextMap_orderId

or use quotes.

Ciao.

Giuseppe

[scaparelli · Accepted solution]

I actually just used rex to pull from the other fields. Here is my final solution:

(index=k8s_main container_name=fraud-single-proxy-listener message="Successfully handled AuthorizationSucceeded event*") OR (index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*") 
| rex field=_raw "\"orderId\":\"(?<nefi>.*?)\"" 
| rex field=message.message "\"orderNumber\":\"(?<nepp>.*?)\"," 
| eval orderId = if(index="app_pci", nepp, nefi) 
| stats dc(index) AS indexCount values(_time) AS eventTime values(index) AS index BY orderId
| eval timeElapsed = now() - eventTime
| where indexCount = 1 AND index = "app_pci" AND (timeElapsed > (30*6000))
| fields orderId

Thanks for your help!

[gcusello]

Hi @scaparelli,

you search is conceptually correct, but it has a limit: using a subsearch you have a limit of 50,000 results, so the filtering could be incomplete.

In this cases, you should use a different approach: to put both the searches in the main search, something like this:

(index=k8s_main container_name=fraud-single-proxy-listener message="Successfully handled AuthorizationSucceeded event*") OR (index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*")
| eval orderId=coalesce("properties.orderId", "contextMap.orderId")
| stats dc(index) AS index_count values(index) AS index BY orderId
| where index_count=1 AND index=k8s_main

Ciao.

Giuseppe

[scaparelli]

I am assuming you meant:

| eval orderId=coalesce(properties.orderId, contextMap.orderId)

and not

| eval orderId=coalesce("properties.orderId", "contextMap.orderId")

but even with the former, the "eval" does not want to be declared for some reason.  even if I set orderId directly to "properties.orderId" it remains null:

| eval orderId=properties.orderId

I am positive that properties.orderId and context.orderId fields are present because I can get results by running:

(index=k8s_main container_name=fraud-single-proxy-listener message="Successfully handled AuthorizationSucceeded event*") OR (index=app_pci source=http:nepp host=nepp-service-v3-prod message.message="Attempt to produce Kafka event finished: AuthorizationSucceeded*") 
| table properties.orderId, contextMap.orderId