Splunk Search

How to join multiple condition

Swati
Engager

In my logs I am getting 4 events for 1 id. 

1)Updating DB record with displayId=ABC0000000; type=TRANSFER
2)Updating DB record with displayId=ABC0000000; type=MESSAGES
3)Updating DB record with displayId=ABC0000000; type=POSTING
4)Sending message to  topic ver. 2.3 with displayId=ABC0000000

Sample logs:
[13.01.2025 15:45.50] [XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX] [XXXXXXXXXXXXXXXXXXXXXX] [INFO ] [Application_name]- Updating DB record with displayId=ABC0000000; type=TRANSFER


I want to get the list of all those ids which have all 3 events like "Updating DB........." but missing "Sending message to  topic ........."


Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Assuming your events are as you showed, try using extract

| makeresults
| fields - _time
| eval _raw="[13.01.2025 15:45.50] [XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX] [XXXXXXXXXXXXXXXXXXXXXX] [INFO ] [Application_name]- Updating DB record with displayId=ABC0000000; type=TRANSFER
[13.01.2025 15:45.50] [XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX] [XXXXXXXXXXXXXXXXXXXXXX] [INFO ] [Application_name]- Updating DB record with displayId=ABC0000000; type=MESSAGES
[13.01.2025 15:45.50] [XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX] [XXXXXXXXXXXXXXXXXXXXXX] [INFO ] [Application_name]- Updating DB record with displayId=ABC0000000; type=POSTING
[13.01.2025 15:45.50] [XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX] [XXXXXXXXXXXXXXXXXXXXXX] [INFO ] [Application_name]- Sending message to  topic ver. 2.3 with displayId=ABC0000000"
| multikv noheader=t
| fields _raw
``` The lines above emulate the data you have shared and are unnecessary for your real data ```
| extract
| fillnull value="SENDING" type
| stats values(type) as types by displayId
| where mvcount(types) != 4 or types != "SENDING"

 

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Assuming type and displayId are already extracted, you could try something like this

| fillnull value="SENDING" type
| stats values(type) as types by displayId
| where mvcount(types) != 4 or types != "SENDING"
0 Karma

Swati
Engager

Assuming type and displayId are already extracted,
NO .. I am not able to join All 3 condition together for 1 id. 

So I need full query to get the ids which are updating in all 3 DB but not updating in kafka topic. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Assuming your events are as you showed, try using extract

| makeresults
| fields - _time
| eval _raw="[13.01.2025 15:45.50] [XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX] [XXXXXXXXXXXXXXXXXXXXXX] [INFO ] [Application_name]- Updating DB record with displayId=ABC0000000; type=TRANSFER
[13.01.2025 15:45.50] [XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX] [XXXXXXXXXXXXXXXXXXXXXX] [INFO ] [Application_name]- Updating DB record with displayId=ABC0000000; type=MESSAGES
[13.01.2025 15:45.50] [XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX] [XXXXXXXXXXXXXXXXXXXXXX] [INFO ] [Application_name]- Updating DB record with displayId=ABC0000000; type=POSTING
[13.01.2025 15:45.50] [XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX] [XXXXXXXXXXXXXXXXXXXXXX] [INFO ] [Application_name]- Sending message to  topic ver. 2.3 with displayId=ABC0000000"
| multikv noheader=t
| fields _raw
``` The lines above emulate the data you have shared and are unnecessary for your real data ```
| extract
| fillnull value="SENDING" type
| stats values(type) as types by displayId
| where mvcount(types) != 4 or types != "SENDING"

 

0 Karma

Swati
Engager


If I am using below query I am getting all Ids in output which are having all 3 types. 

index=ABC source=XYX
| stats values(type) as types by displayId
| where mvcount(types) = 3

displayIdtypes
ABC0000001; Posting
Transfer
Message
ABC0000001; Posting
Transfer
Message
ABC0000003; Posting
Transfer
Message




But if I am adding this 2 condition ,  not getting any result.  

|fillnull value="SENDING" type
where mvcount(types) != 4 or types != "SENDING"

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

What do you get if you add the fillnull and the first part of the where condition?

0 Karma

Swati
Engager

I got expected result using your solution , rest I will change condition according to my requirement. 

index=ABC source=XYX
| extract
| fillnull value="Sending message to Common Booked topic" type
| stats values(type) as types by displayId
| where mvcount(types) = 4

Just one more help I need how to add Time also in table. Tried adding this but time is not printing. 

|table _time, displayId, types

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Which time do you want - there are 4 events with different times!

0 Karma

Swati
Engager


I need to show time for all present events. 


0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| stats values(type) as types values(_time) as times by displayId

Note that this will give you the times in internal format (number of seconds since the beginning of 1970)

If you want the times formatted, you should create a field with the formatted version and collect those values.

0 Karma

Swati
Engager
index=ABC source=XYZ 'ABC00000000001'
| fillnull value="SENDING" type
| stats values(type) as types by display


Using above query,  getting wrong output
1) 'Sending Type is coming with All event event if there is not sending event for that ID
2)  For the Ids which have 'sending' event 2 times in logs it should print twice in output. 
3) Sample log, can we get this time from log event also in output. 
[21.12.2024 00:33.37] [] [] [INFO ] [] - Updating DB record with displayId=ABC00000000001; type=RANSFER

ID Type
ABC0000001;

TRANSFER

SENDING

ABC0000002

TRANSFER

SENDING

ABC0000003

POSTING

TRANSFER

SENDING

MESSAGES

ABC0000004

POSTING

TRANSFER

SENDING

MESSAGES

ABC0000005

TRANSFER

SENDING




Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

From what you have shared (which is all I can go on), are you saying that the events which have been marked as "SENDING" in the type are not actually "Sending" messages? If so, presumably they also don't have a type field?

Please can you share accurate but anonymised examples of the all event types you are trying to process because doing it piecemeal is not very productive.

0 Karma

Swati
Engager
There are 2 ids ABC00000000001 and ABC00000000002
 
ABC00000000001 has events types : 'Transfer' and 'MESSAGES'
 
[21.12.2024 00:31.37] [] [] [INFO ] [Application_name] - Updating DB record with displayId=ABC0000001; type=TRANSFER
[21.12.2024 00:32.37] [] [] [INFO ] [Application_name] - Updating DB record with displayId=ABC0000001; type=MESSAGES
 
ABC00000000002 has events:
[21.12.2024 00:33.37] [] [] [INFO ] [Application_name] - Updating DB record with displayId=ABC0000002; type=TRANSFER
[21.12.2024 00:34.37] [] [] [INFO ] [Application_name] - Updating DB record with displayId=ABC0000002; type=MESSAGES
[21.12.2024 00:35.37] [] [] [INFO ] [Application_name] - Updating DB record with displayId=ABC0000002; type=POSTING
[21.12.2024 00:35.37] [] [] [INFO ] [Application_name] - Sending message to  Booked topic ver. 1.0 with displayId=ABC0000002
[21.12.2024 00:35.37] [] [] [INFO ] [Application_name] - Sending message to  Booked topic ver. 2.0 with displayId=ABC0000002
 
index=ABC source=XYZ
| fillnull value="SENDING" type
| stats values(type) as types by displayId
 
Expected output is
-------------------------
ABC0000001 - TRANSFER 
                                MESSAGES
 
ABC0000002 - TRANSFER
                                MESSAGES
                                POSTING
                                Sending message to Common Booked topic ver. 1.0
                                Sending message to Common Booked topic ver. 2.3
 
But Ouput is:
 
ABC0000001 - TRANSFER 
                                MESSAGES
                                Sending 
 
ABC0000002 - TRANSFER
                                MESSAGES
                                POSTING
                                Sending 
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

This is different from what you originally asked for. Worse than that, the expected output is subtly different to your input events. Please can you explain precisely how the input events are to be processed to give the expected output?

0 Karma

JohnEGones
Communicator

Does 

index=WhatEverIndexTheseLogsAreIn type OR displayId

 

produce any of the logs you want?

0 Karma

JohnEGones
Communicator

Have you set-up any eventtypes or tagging?

0 Karma
Get Updates on the Splunk Community!

Message Parsing in SOCK

Introduction This blog post is part of an ongoing series on SOCK enablement. In this blog post, I will write ...

Exploring the OpenTelemetry Collector’s Kubernetes annotation-based discovery

We’ve already explored a few topics around observability in a Kubernetes environment -- Common Failures in a ...

Use ‘em or lose ‘em | Splunk training units do expire

Whether it’s hummus, a ham sandwich, or a human, almost everything in this world has an expiration date. And, ...