topic Re: How do i correlate events using subsearch from two sources based on two different conditions? in Splunk Search

How do i correlate events using subsearch from two sources based on two different conditions?

Sunil2020 — Sun, 12 Jul 2020 16:45:14 GMT

Hello Splunker,

I have a below scenario where i am struggling to come up with search query, and would like to ask your expert advise to achieve the same.

I have two database tables A and B and i have ingested them in two different sources in my splunk instance.

Table A has data related to Job which basically has fields like JobID, JobName, StartTime, EndTime

Table B has data related to Job execution details like JobID, AgentName, JobType, JobDate,JobEndHour

Certain Jobs (Table A) takes longer time to finish and to find out the details in terms of whats was going on during the time Job (Which is taking longer) was running can be found from Table B.

To find data from Table B, First we need to find out which Agent (AgentName) was handling the job (using JobID, StartTime, EndTime) and once we have Agent details, we have to search again in Table B that during those hours (StartTime, EndTime) what other Jobs were handled including Job in question by the Agent.

Both tables has JobID as common field.

Any help or pointers are highly appreciated.

Thanks.

Re: How do i correlate events using subsearch from two sources based on two different conditions?

anilchaithu — Mon, 13 Jul 2020 21:49:28 GMT

@Sunil2020

You can use subserach in this scenario. Based on the number of events in the subsearch and in the index, you can use one of the below logic that best suits your use case.

For less number of long running jobs:

index=index_name source="table B" [ search index=index_name source="table A" | (your criteria to get long running job IDs | table jobID | format ] | table JobID, AgentName, JobType, JobDate,JobEndHour

For more/optimal number of long running jobs:

index=index_name source="table B" | table JobID, AgentName, JobType, JobDate,JobEndHour | join type=inner JobID [ search index=index_name source="table A" | (your criteria to get long running job IDs | table jobID ]

Note: subsearch has limitations both in the number of events (10000 OR 50000 depends on where you use) & run time (60s). when it hits either of this limit, it auto completes the search.

If you face this scenario, please create a csv file for the long running job IDs and use that in the second example.

Hope this helps

Re: How do i correlate events using subsearch from two sources based on two different conditions?

Sunil2020 — Tue, 14 Jul 2020 10:47:32 GMT

Hi @anilchaithu

Thank you for response.

Your suggestion certainly gives me the insight into Table B based on value from Table A.

In my scenario, i have to lookup twice into Table B actually. Fist I will have to query Table B with JobID from Table A which gives me Agent Name. Second lookup into Table B is to query using Agent Name, Data and Hours where Hours needs to be taken from Table A record (Start time, End Time).

This search will basically gives me list of events/jobs Agent was working on before Actual Job (Which is delayed).

So for example,

Table A

JobID Start Time EndTime

1234 2020-07-14 10:30:00 2020-07-14 11:15:15

Table B

AgentName Date Hour JobID JobStatus

ABC 2020--07-14 10 42242 A

XYZ 2020--07-14 10 42212 A

ABC 2020--07-14 11 42215 A

RRR 2020--07-14 10 42218 A

ABC 2020--07-14 11 42213 A

ABC 2020--07-14 11 1234 A

In above example, JobID 1234 is delayed more than 10 minutes, so when we check Table B for that JobID, we can see Agent ABC has handled that job. Now if we look at Hours (Start Time, End Time) from Table A, it is 10:30 and 11:15 so we have to consider lower and higher side of hours which is 10 and 12.

We now need to query Table B with Agent ABC , Date 2020-07-14 and Hours between 10 to 12 which will give results as below.

AgentName Date Hour JobID JobStatus

ABC 2020--07-14 10 42242 A

ABC 2020--07-14 11 42215 A

ABC 2020--07-14 11 42213 A

ABC 2020--07-14 11 1234 A

In above results, i can see my job 1234 executed after 3 other jobs by the Agent ABC. This is the output I am expecting from my search.

Any inputs or help is highly appreiciated.

Cheers.