join two event logs between two specific times

[awedmondson]

I have two indexes that I have successfully joined, they are indexA and indexB. There is a field in the resulting (joined) event FieldC. I have another index, indexY with FieldD. I need to join this indexY to indexA and indexB. This works ok.

index=indexA FieldC | join FieldC [search index=indexB FeildC] | join FeildD [search index=indexY FeildD] | table _time, FeildC, FieldD

now the tricky bit, I have indexE which has a start and finish event. How do I run the double join, between the two time events (Logon and logoff) in index E.

[lguinn2]

Every event has a _time field. Out of all the events that you are working with here, which _time field are you chosing and why? I am concerned that it doesn't have the meaning that you expect...

Also, the suggestions here are not optimal, as we don't understand the goal. If you could explain what you want for results - rather than an abstraction of the steps you have undertaken so far - I think we can probably find an easier and faster solution.

[awedmondson]

The _time field is so that the event time is shown in the table

[lguinn2]

As Kristian said, this is probably the wrong way to go. Also, what is the meaning for the _time field?

[kristian_kolb]

I'm sorry, but I can't help thinking that you are not approaching the problem in a splunkish way. Usually, join is not the way to go.

Please provide some sample data, and an outline of your desired results.

[somesoni2]

Not sure I understand your requirement completely. Assuming indexE has a common field with already obtained events by your join query, say FieldE, and we have two events for each value of FieldE (login and logout) and you want both the events to be captured, try below query:

index=indexA FieldC | join FieldC [search index=indexB FeildC] | join FeildD [search index=indexY FeildD] | table _time, FeildC, FieldD | join max=0 FieldE [search index=indexE FieldE]

Updated query

I am not sure how your indexE is storing data about login and logout. I will assume for a given period, for each FieldE there will be two events, one login and one logout. So here is the sample query for it.

index=indexE FieldE | stats first(_time) as LogoutTime last(_time) as LoginTime by FieldE

One this field is obtained, your can go like this.

 index=indexA FieldC | join FieldC [search index=indexB FeildC] | join FeildD [search index=indexY FeildD] | table _time, FeildC, FieldD | join FieldE [search index=indexE FieldE | stats first(_time) as LogoutTime last(_time) as LoginTime by FieldE] | where _time>=LoginTime and _time<=LogoutTime

[somesoni2]

Did you try the updated query?

[somesoni2]

Try updated query if its meeting your requirement.

[awedmondson]

Thanks somesoni2, I need to search the first join (indexA & indexB) between the times of logon and logout of indexE.