Splunk Search

Join two data sets to report their event times

sashpdhar
Explorer

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

Labels (5)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma

sashpdhar
Explorer

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

 



0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

sashpdhar
Explorer

thanks @ITWhisperer  , it works after that correction.

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...