Splunk Search

join the two search results from two different data sets with different time stamps

Laya123
Communicator

Hi,

Is it possible to get join the results with 2 different time stamps with two different indexes

Means I have some data in index A and some data in Index B with common field of PID. i want to join the search results from A and B using the common field of PID. but the problem is the time stamp is different in each index. if we are searching results in index A from 10:00am to 10:15 am, is it possible to search in Index B from 10:00am to 10:30am in same query. here I am trying to join two search results in to one output.

If pid from index A was started at 10:15am but index B the same pid with same project it was started at 10:17am. using the below query I am missing count of pid from B started at 10:17am.

query: index =A sorucetype=X host=L|stats count(pid) as Acount by project pid|join pid [search index =B sourcetype=Y host=M |stats count (pid) as Bcount by project pid]|table project pid Acount Bcount

Thanks

Tags (1)
0 Karma

sagrl
Explorer

You can use Search Time modifiers and can pass the time range within your Search. http://docs.splunk.com/Documentation/Splunk/6.4.3/SearchReference/SearchTimeModifiers

index="A" earliest=-7h  latest=-6h | append [search index="B" earliest=-5h latest=-4h]
0 Karma

Laya123
Communicator

I have time picker in my dashboard I am selecting the date and time from that time picker, the same time frame I am using for other reports in that dashboard so for this report also I want the same time period. but for the report I requested your help is the time period which selected from the timepicker, just I want add to 5min to that time, not earliest and latest.

is there any way to add 5 min to

$Time_Frame.earliest$
$Time_Frame.latest$

like $Time_Frame.earliest$ + 5m
$Time_Frame.latest$ +5m

thanks

0 Karma

Richfez
SplunkTrust
SplunkTrust

Why can't you just make the time frame larger? Obviously, PID can't be re-used for another process in that same time period or else the two things would never ever be able to be hooked up properly (on PID, at least), so ...

Oooh, join and a subsearch. Can you provide a sample of a few of each of the events? There might be ways to make this run 1000 times faster and better, and also solve your "connect them together" problem at the same time.

0 Karma

Laya123
Communicator

Thank you so much for your response, sorry for the late response.

I think I confused you. Actually what I want is. I am trying to join to different data sources. but I have uniqueID called PID for these 2 sources. I am using the subquery to join those two sources, why I am using subquery is I want the PIDs which were there in first source I want to search the same PIDs in second source also, I am getting these using subquery. The problem is we are selecting the time range from time picker so the time range will be the same for subquery and main query, but some of the PIDs from second source wont be there in the time range which we selected from time picker, so I am missing some PIDs from the second source. If I want all PIDs which were coming from first source for that time range which we selected in my output.

Example:

logs from Source 1:

2016-08-25 10:29:59 ABCDE 111.11.11.11 GET /surveys.aspx Project=XYZ&pid=cb8a63f

Source2:
2016-08-25 10:30:01 ABCDE 111.11.11.11 GET /surveys.aspx Project=XYZ&pid=cb8a63f

if have taken the time period 2016-08-25 10:15:00 to 2016-08-25 10:30:00; I will get PID "cb8a63f" from source 1; I will miss the from source 2

is there any chance to get these PID from source 2 also

I am using the query: I am using this report in dashboard my dashboard is having timepicker in the top to select time range for all the reports. I using same time to get this report also. is there any chance to add 60 seconds to timetoken to get the all PIDs which were coming from subquery

index=ABC sourcetype=XYZ host=LMN | [search index=iis sourcetype=iis host=IJK |stats count(PID) as PCount by _time c_ip ProjectName PID|table ProjectName PID]||chart count(PID) as Phits by host

Thanks in advance

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!