Joining 2 sourcetypes ( 2 types of events ) based on time range

[tragiccode]

We have 2 sourcetypes that we would like to somehow do a join based on if sourcetype2 has a ArrivalDateTime that falls within any of SourceType1 StartDateTime and EndDateTime

sourcetype2
contains an ArrivalDateTime

Title="Update for Adobe Flash Player for Windows Server 2016 for x64-based Systems (KB4051613)"
LegacyName="KB4051613-WinNextServer-RTM-RS1RTM-X64-TSL"
MsrcSeverity="Unspecified"
KnowledgebaseArticles="4051613"
CreationDate="11/1/2017 5:00:00 PM"
ArrivalDate="11/7/2017 12:11:29 AM"
UpdateType="Software"
PublicationState="Published"

sourcetype1
Contains a StartDateTime and EndDateTime

Id="a775be6b-36ea-41a3-ba4f-3b0ae7f63e82"
StartTime="11/6/2017 10:37:34 PM"
EndTime="11/6/2017 11:05:21 PM"
Result="Succeeded"

I would like the end output of the combined event to look like so

StartTime="11/6/2017 10:37:34 PM"
EndTime="11/6/2017 11:05:21 PM"
Result="Succeeded"
Title="Update for Adobe Flash Player for Windows Server 2016 for x64-based Systems (KB4051613)"
LegacyName="KB4051613-WinNextServer-RTM-RS1RTM-X64-TSL"
MsrcSeverity="Unspecified"
KnowledgebaseArticles="4051613"
CreationDate="11/1/2017 5:00:00 PM"
ArrivalDate="11/7/2017 12:11:29 AM"
UpdateType="Software"
PublicationState="Published"

[DalJeanis]

Try something like this...

 (sourcetype=type1 ) OR (sourcetype=type2)
 | eval time1=strptime(StartTime,"%m/%d/%Y %I:%M:%S %p")
 | eval time2=strptime(EndTime,"%m/%d/%Y %I:%M:%S %p")
 | eval time3=strptime(ArrivalDate,"%m/%d/%Y %I:%M:%S %p")   
 | eval time0=coalesce(time2,time3)
 | sort 0 - time0
 | streamstats current=f last(time1) as lasttime1 last(StartTime) as lastStart last(time2) as lasttime2 last(EndTime) as lastEnd last(Result) as lastResult
 | search sourcetype="type2"
 | eval StartTime=case(isnull(lasttime1) OR isnull(lastStart),"((NO MATCH))", time3<lasttime1,"((NO MATCH))", true(),lastStart)
 | eval EndTime=case(isnull(lasttime1) OR isnull(lastEnd),"((NO MATCH))", time3<lasttime1,"((NO MATCH))", true(),lastEnd)
 | eval Result=case(isnull(lasttime1) OR isnull(lastResult),"((NO MATCH))", time3<lasttime1,"((NO MATCH))", true(), lastResult)
 | fields - time* last*

---

This is based on the assumption that your wording was correct, rather than your example, which falls outside the range for that event.

 CreationDate="11/1/2017 5:00:00 PM"
 ArrivalDate="11/7/2017 12:11:29 AM"
 StartTime="11/6/2017 10:37:34 PM"
 EndTime="11/6/2017 11:05:21 PM"

The above example, to match, would require the StartTime and Endtime to be within the CreationDate and ArrivalDate. The below example is the one that this code would work for.

 CreationDate="11/1/2017 5:00:00 PM"
 ArrivalDate="11/6/2017 11:01:29 PM"
 StartTime="11/6/2017 10:37:34 PM"
 EndTime="11/6/2017 11:05:21 PM"

[nileena]

Assuming ArrivalDate is the timestamp for sourcetype2 and StartTime is timestamp for sourcetype1, you could try writing a search like this:

sourcetype=sourcetype1 OR sourcetype=sourcetype2 | eval EndTimeEpoch=strptime(EndTime, "%m/%d/%Y %H:%M:%S %p") | streamstats current=f last(EndTimeEpoch) AS EndTimeEpoch last(Result) AS Result last(Id) AS Id by _time | where _time>EndTimeEpoch

Streamstats might help you correlate the ArrivalDate field with StartTime and EndTime.

[DalJeanis]

Pretty good work, @nileena! Here's one additional thing to deal with: you generally need to set an explicit sort order before your streamstats command, because (especially in this case) you can't be sure what the _time might be for these events. Some kinds of events, it would be the first time field on the event, some the last.

[nileena]

Oh ya, that makes sense, thanks! 🙂

[kamlesh_vaghela]

Hi

Is that any common field (KEY) in both sourcetype (sourcetype1 & sourcetype2)?
I found Id in sourcetype1. Is that any field which can be used to relate event of courcetype1 to event of sourcetyp2?

Thanks

[tragiccode]

Nope, All i can join on "so-to-speak" is if the datetime of 1 sourcetype falls inside the range of the other sourcetype

[kamlesh_vaghela]

Ok. It is difficult to design search. Well, I have used _time aa related fields. So can you please try below search? It might possible there are multi-valued fields display at single time stamp.

sourcetype=sourcetype1 | fileds _time Id StartTime EndTime Result | append [ search 
sourcetype=sourcetype2 | fileds _time Title LegacyName MsrcSeverity KnowledgebaseArticles CreationDate ArrivalDate UpdateType PublicationState ] | stats values(Id) as Id values(StartTime) as StartTime values(EndTime) as EndTime values(Result) as Result values(Title) as Title values(LegacyName) as LegacyName values(MsrcSeverity) as MsrcSeverity values(KnowledgebaseArticles) as KnowledgebaseArticles values(CreationDate) as CreationDate values(ArrivalDate) as ArrivalDate values(UpdateType) as UpdateType values(PublicationState) as PublicationState by _time

Happy Splunking

[somesoni2]

How many events are there in sourcetype1?