Multiple joins based of conditions.

[onthakur]

Team,

I got 3 logs, I need to fetch Transaction_id,Event and Total_Count from LOG1. After that I need to join the 3 logs to get Successfull and Failures.
successfull transaction will have only LOG2.
Failure transactions will have both LOG2 and LOG3

Finally I need data in timechart (span=1h).

_time Event Total_Count Successfull Error

LOG1 = 024-05-29 12:35:49.288 [INFO ] [Transaction_id] : servicename : access : Event : process : Payload:

LOG2 = 2024-05-29 12:11:09.226 [INFO ] [Transaction_id] : application_name : report : servicename (Async) : DB save for SubscribersSettingsAudit record completed in responseTime=2 ms

LOG3 = 2024-05-24 11:25:36.307 [ERROR] [Transaction_id] : application_name : regular : servicename (Async) : Couldn't save the SubscribersSettings record in DB

[gcusello]

Hi @onthakur,

you have to categorize the events:

if

• LOG1, LOG2 and LOG3 have different sourcetypes (or something else to recognize them),
• the Event is a field that you already extracted,
• CorrelationID is a common key between the three logs,
• success is an action when you have the message "record completed",
• error is an action when you have the message "Couldn't save the SubscribersSettings record in DB",

remember that you cannot have more columns in timechart, so you must use stats.

you could create a search like the following:

index=your_index sourcetype IN (LOG1, LOG2, LOG3)
| bin span=1h _time
| stats 
     values(Event) AS Event
     count AS Total_Count
     count(eval(searchmatch("record completed") AS "success"
     count(eval(searchmatch("Couldn't save the SubscribersSettings record in DB") AS "Error"
     BY _time CorrelationID

 Adapt the search to your conditions.

Ciao.

Giuseppe

[ITWhisperer]

Your requirement is not completely clear - which time do you want? can there be multiple entries in any of the logs for the same transaction id? if there are multiples, how do you want these counted? do you actually need log2 since every transaction is in log1 (giving you the total) and errors are in log3 so successful is the difference between these two count?