Help to pass time to subsearch?

[k31453]

Hi, I have SPL which includes just using bunch of lookups and producting following data:

_time	turnaround_time	diff_time	customer	product_to	product_from
2022-06-30 04:04:43.399	2022-06-30 04:12:53.556	490.156810	nike	cat	dog
2022-07-07 05:15:14.209	2022-07-07 05:31:22.881	968.671302	adidas	bear	cat

I have got another lookup jira_data.csv which contains Jira data associated with it:

Ticket	customer	Summary	Status	Created	Resolved	Updated
COW-245	nike	customer complaining	open	2022-06-30 03:04:43.399	-	2022-06-30 03:21:43.399
COW-456	nike	product change	closed	2022-06-30 02:04:43.399	2022-06-30 07:04:43.399	2022-06-30 07:20:43.399

I am attempting to do follow:

• Use turnaround_time and lookup in the jira_data.csv and find all jiras if turnaround_time is around 2h back or front of Resolved.  In this example I am expecting COW-456 as an output.

[yuanliu]

As @bowesmana mentioned, your illustrated data and expected result do not match.  Assuming it was a mistype, that the turnaround_time was actually 2022-06-30 05:12:53.556 so it was within 2 hour ahead of 07:04:43, you still need to answer whether any field should match.  Given the mentioning of lookup, I assume that you want to match for customer.  If this is the case, it is a simple exercise of calculating time difference after match, like

| lookup jira_data.csv customer
| where strptime(Revolved, "%Y-%m-%d %H:%M:%S.%3N") - strptime(turnaround_time, "%Y-%m-%d %H:%M:%S.%3N") < 7200

Is this what you are looking for?

[bowesmana]

Are you looking at ANY jira_data entry regardless of customer? The COW-456 has a Resolved time of 07:04:43 but there is no turnaround_time that is within two hours of that. My understanding is that you are looking for a turnaround_time that is between 05:04:43.399 and 09:04.43.399