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:
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?
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