topic Re: Combine 2 log events based on uniqueId into one single row in table in Splunk Search

Combine 2 log events based on uniqueId into one single row in table

sdanayak — Wed, 07 May 2025 10:21:04 GMT

I want to have result in table with 2 or 3 log events combined based on unique key in all events and return 1 single row for all those events having unique key in them.

for all my log events I have a common unique key for which I want to combine them and get in table as single row for that unique key and if value for any column is not present then null for that particular cell in table.

Log event 1: Message="Taken the response",UniqueId="329wey98fywe",Status=Pending

Log event 2: Message="Process completed",UniqueId="329wey98fywe",Status=Finalized

Log event 3: Message=,UniqueId="329wey98fywe",Status=Pending

Re: Combine 2 log events based on uniqueId into one single row in table

PickleRick — Wed, 07 May 2025 10:32:06 GMT

I'm not sure what "columns" you want from this data but assuming that you want to have a table with various messages per id and status you might want something like

<your initial search>
| stats values(Message) as Message by UniqueId Status
| xyseries UniqueId Status Message

Re: Combine 2 log events based on uniqueId into one single row in table

sdanayak — Wed, 07 May 2025 10:43:48 GMT

I am getting this error
Error in 'xyseries' command: At least one data field must be specified.

Should my initial search provide already a table for stats and xyseries ?

Can I put condition for 2 log events that they must match uniqueID? As I do not want to combine 2 log events where uniqueId in both is not matching.

Re: Combine 2 log events based on uniqueId into one single row in table

sdanayak — Wed, 07 May 2025 11:09:29 GMT

I have used the uniqueId and message in xyseries, but getting that error message for xyseries.

I have 2 log events and both will have uniqueId in that event, now I want both log events to be in my result table only when they both have same value for uniqueId.

While as per below query it brings even the logs events which do not have same uniqueId or matching message in them.

index=finder_db AND (host="host1" OR host="host2") AND (("Wonder Exist here") OR ("Message=Limit the occurrence" AND "FinderField=ZEOUS")) | table uniqueId, FinderField by uniqueId | stats values(FinderField) as FinderField, values(FinderField) as FinderField by uniqueId

Host1 and host2 in this query is my servers name where these log would exist.

I am searching 2 string in log events, one is "Wonder Exist here" and second is starting with Message=

and both log will have uniqueId which I want to match for both events and bring as 1 single row in result

Hope I am able to explain and thanks for help

Re: Combine 2 log events based on uniqueId into one single row in table

PickleRick — Wed, 07 May 2025 11:12:53 GMT

I assumed your fields are already extracted. After some thought, actually the stats doesn't add anything here. It should be enough to just do the xyseries. As long as you have fields properly extracted.

Re: Combine 2 log events based on uniqueId into one single row in table

livehybrid — Wed, 07 May 2025 12:13:11 GMT

Hi @sdanayak

Does this work for you?

|stats values(*) AS * by UniqueId

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

Re: Combine 2 log events based on uniqueId into one single row in table

PickleRick — Wed, 07 May 2025 12:58:28 GMT

OK. Let's start at the start 🙂

index=finder_db AND (host="host1" OR host="host2") AND (("Wonder Exist here") OR ("Message=Limit the occurrence" AND "FinderField=ZEOUS"))

This will select the events for further processing.

But the question is whether you're extracting any fields from those events. Before we're going anywhere further, we need to know whether:

1) The uniqueId field (to which you're referring in subsequent posts in a case-inconsistent manner) is extracted.

2) The "data" field(s) which you want to "merge" are extracted.

Generally, the field extraction should be (actually, should already have been) handled at data onboarding stage.

When you have this one covered, you can get to the second part - handling the logic behind "joining" your events.

Re: Combine 2 log events based on uniqueId into one single row in table

sdanayak — Wed, 07 May 2025 14:15:09 GMT

yes, the below query would extract log events from which I am expecting final list of data

index=finder_db AND (host="host1" OR host="host2") AND (("Wonder Exist here") OR ("Message=Limit the occurrence" AND "FinderField=ZEOUS"))

this query will give 2 log events and both events will include uniqueId in it. So for final result I want to have

uniqueId, FinderField as table where uniqueId is listed when both log events have it and also above string exists with the same uniqueId.

Re: Combine 2 log events based on uniqueId into one single row in table

sdanayak — Wed, 07 May 2025 14:26:40 GMT

For eg:

If one log event has uniqueId=abc123 with "Wonder Exist here" and for this uniqueId with "Message=Limit the occurrence" AND "FinderField=ZEOUS" DO NOT exist then that one should not be in result
and same in reverse also should satisfy so uniqueId only with log of "Message=Limit the occurrence" AND "FinderField=ZEOUS" should not come in result

Re: Combine 2 log events based on uniqueId into one single row in table

PickleRick — Wed, 07 May 2025 20:41:23 GMT

I'm not asking whether the right events are selected. I'm asking whether the fields are extracted.

If you do

index=finder_db AND (host="host1" OR host="host2") AND (("Wonder Exist here") OR ("Message=Limit the occurrence" AND "FinderField=ZEOUS"))
| table uniqueId FinderField Message

Is your table populated with field values or are they empty?