Join two data sets to report their event times

[sashpdhar]

want to report a pattern for each day and grab event times from different logs for that pattern , tried something like this but not working as expected , need suggestions.

Current Query

index="blah" source="*blah*" "Incomming"
| rex "Incomming: (?<s_json>.*])"
| timechart span=1d earliest(_time) as a_time by s_json
| join type=outer s_json
[ search index="blah" source="*blah*" "Internal"
| rex "Internal: (?<s_json>.*])"
| timechart span=1d latest(_time) as c_time by s_json
]
| convert ctime(a_time)
| convert ctime(c_time)
| table _time,s_json,a_time,c_time

Expected -

Date1 , j1 , a_time,c_time

Date1,j2,a_time,c_time

Date2,j3,a_time,c_time

Date3,j4,a_time,c_time

Date4,j1,a_time,c_time

Date4,j2,a_time,_ctime

Each day can have its own unique patterns ( j1,j2,j3,j4 ... ) , so need to dyamicallly pick that for that day and report a_time and c_time

[ITWhisperer · Accepted solution]

Try something like this

index="blah" source="*blah*" "Incomming"
| rex "Incomming: (?<s_json>.*])"
| bin _time span=1d
| stats earliest(_time) as a_time by _time s_json
| join type=outer _time s_json
[ search index="blah" source="*blah*" "Internal"
| rex "Internal: (?<s_json>.*])"
| bin _time span=1d
| stats latest(_time) as c_time by _time s_json
]
| convert ctime(a_time)
| convert ctime(c_time)
| stats values(a_time) as a_time values(c_time) as c_time by _time s_json
| table _time,s_json,a_time,c_time

[sashpdhar]

Thanks you , getting below error

Error in 'from' command: Invalid dataset specifier 's_json', expected dataset-type:dataset-name. Verify your search string.

Query - 

index="blah" source="*blah*" "Incomming"
| rex "Incomming: (?<s_json>.*])"
| bin _time span=1d
| stats earliest(_time) as a_time by _time s_json
| join type=outer _time s_json
| [search index="blah" source="*blah*" "Internal"
| rex "Internal: (?<s_json>.*])"
| bin _time span=1d
| stats latest(_time) as p_time by _time s_json
]
| convert ctime(a_time)
| convert ctime(p_time)
| stats values(a_time) as a_time values(c_time) as p_time by _time s_json
| table _time,s_json,a_time,p_time

[ITWhisperer]

If this is your query, you have an extra pipe "|" in the join before the open bracket "["

Also, you aren't using "from" anywhere in the search

If this isn't your actually search, how do you expect us to be able to help you diagnose the problem?

[sashpdhar]

thanks @ITWhisperer  , it works after that correction.

Sorry first time doing SPL commands , so overlooked  the extra '|' before join