How to join multiple condition

[Swati]

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 ........."

[ITWhisperer]

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"

[Swati]

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. 

[ITWhisperer · Accepted solution]

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"

[Swati]

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

displayId	types
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"

[ITWhisperer]

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

[Swati]

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

[ITWhisperer]

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

[Swati]

I need to show time for all present events. 

[ITWhisperer]

| 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.

[Swati]

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

[ITWhisperer]

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.

[Swati]

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 

[ITWhisperer]

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?

[JohnEGones]

Does 

index=WhatEverIndexTheseLogsAreIn type OR displayId

produce any of the logs you want?

[JohnEGones]

Have you set-up any eventtypes or tagging?