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

[sdanayak]

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

[livehybrid]

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

[sdanayak]

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.

[PickleRick]

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.

[PickleRick]

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

[sdanayak]

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

[PickleRick]

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.

[sdanayak]

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.

[PickleRick]

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?

[sdanayak]

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