Dashboards & Visualizations

Splunk - Display Events when there is no matching subsearch events

ramakrhe
New Member

In the logs I will see event with text CanonicalItemLoggingService and id=3632735.
Similarly in the logs, I may or may not have events for 3632735 with text TargetItemLoggingService and canonicalItem=3632735 and action=17243

I want to write a splunk query that will display events which are present in CanonicalItemLoggingService but not in TargetItemLoggingSErvice.

Below query that I wrote it gives me the results when the event is present in both CanonicalItemLoggingService and TargetItemLoggingService.

host="xyz", sourcetype=dh, "[c.h.d.l.i.canonicalItemLoggingService]"
| fields traceId, batchId, id
| rename id as canonicalItem
| table traceId, batchId, canonicalItem
| append
[ search host="xyz*", sourcetype=dh, "[c.h.d.l.i.TargetItemLoggingService]"
| fields canonicalItem, id , action
| rename id as pubId
| table canonicalItem, pubId, action
| fillnull pubId value=NULL
| stats list(pubId) as pubId, list(action) as action by canonicalItem ]
| stats list(traceId) as traceId, list(batchId) as batchId, list(pubId) as pubId, list(action) as action by canonicalItem
| table traceId, batchId, canonicalItem, pubId, action
| where canonicalItem="3632735"

The result appears as below
traceId batchId canonicalItem pubId action
d7b 449996 3632735 29664000 . 17243
29664035 . 17243
29663967 . 17243

I want the list show up as below when there is no matching TargetItemLoggingService event
traceId batchId canonicalItem pubId action
d7b 449996 3632735

Tags (1)
0 Karma

mydog8it
Builder

See if this gets you where you want to be...

host="xyz", sourcetype=dh, "[c.h.d.l.i.canonicalItemLoggingService]"
| join type=outer left=L right=R where L.id=R.canonicalItem
[ search host="xyz*", sourcetype=dh, "[c.h.d.l.i.TargetItemLoggingService]"]
| stats list(traceId) as traceId, list(batchId) as batchId, list(pubId) as pubId, list(action) as action by canonicalItem

0 Karma

ramakrhe
New Member

It gives "No results found" for cases where I have Canonical and Target Event and also for the case where there is only Canonical Event

0 Karma

ramakrhe
New Member

CanonicalItemLoggingService Event
2019-12-14 21:05:06,465 [INFO] [itemEventTaskExecutor-4] [c.h.d.l.i.CanonicalItemLoggingService] SAPCUSTOMER_INBOUND_POOL uuid=c4101aba-e9f3-46f1-8592-d2e9de6ef960 id=3632735 action=24009 status=SUCCESS integrationKey='005xxx' fields={} traceId=d7b batchId= 449996 documentId=xxx_KNA1

2019-12-14 21:05:11,422 [INFO] [itemEventTaskExecutor-3] [c.h.d.l.i.TargetItemLoggingService] SAPCUSTOMER_INBOUND_POOL id=29664000 canonicalItem=3632735 action=17243 targetSystem='HybrisCore' fields={} exportCode=Address

2019-12-14 21:05:11,370 [INFO] [itemEventTaskExecutor-3] [c.h.d.l.i.TargetItemLoggingService] SAPCUSTOMER_INBOUND_POOL id=29664035 canonicalItem=3632735 action=17243 targetSystem='HybrisCore' fields={} exportCode=Address

2019-12-14 21:05:11,365 [INFO] [itemEventTaskExecutor-2] [c.h.d.l.i.TargetItemLoggingService] SAPCUSTOMER_INBOUND_POOL id=29663967 canonicalItem=3632735 action=17243 targetSystem='HybrisCore' fields={} exportCode=Address

0 Karma

mydog8it
Builder

Can you provide event data?

0 Karma

ramakrhe
New Member

When I post event data, the comment is not displaying. Let me know how I can send this to you.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...