@hummingbird81 I tested this using makeresults with dummy data. Copy and paste this query into your Splunk search bar to run it. It doesn’t depend on your actual index or CSV, so it's safe for test...
See more...
@hummingbird81 I tested this using makeresults with dummy data. Copy and paste this query into your Splunk search bar to run it. It doesn’t depend on your actual index or CSV, so it's safe for testing. Dummy data:- | makeresults
| eval _time=strptime("2025-03-01T12:00:00.00+05:30", "%Y-%m-%dT%H:%M:%S.%2Q%z"),
user_id="001",
Name="John Smith",
title="Consultant",
email="john.smith@example.com",
start_Date="2021-06-28T23:59:59.00+05:30",
end_Date="2025-06-28T23:59:59.00+05:30",
source="okta",
mod_time=_time
| fields user_id, Name, title, email, start_Date, end_Date, mod_time, source
| append [
| makeresults
| eval _time=strptime("2022-06-01T12:00:00.00+05:30", "%Y-%m-%dT%H:%M:%S.%2Q%z"),
user_id="001",
Name="John Smith",
title="Administrator",
email="john.smith@example.com",
start_Date="2021-06-28T23:59:59.00+05:30",
end_Date="2022-06-28T23:59:59.00+05:30",
source="csv",
mod_time=if(isnull(_time), strptime(end_Date, "%Y-%m-%dT%H:%M:%S.%2Q%z"), _time)
| fields user_id, Name, title, email, start_Date, end_Date, mod_time, source
]
| sort 0 -mod_time
| dedup user_id
| table Name, title, start_Date, end_Date, user_id You can try this:- index=okta
| eval source="okta", mod_time=_time
| fields user_id, Name, title, email, start_Date, end_Date, mod_time, source
| append [
| inputlookup identities.csv
| eval source="csv", mod_time=if(isnull(_time), strptime(end_Date, "%Y-%m-%dT%H:%M:%S.%2Q%z"), _time)
| fields user_id, Name, title, email, start_Date, end_Date, mod_time, source
]
| sort 0 -mod_time /* Sort by mod_time descending to prioritize latest */
| dedup user_id /* Keep only the first (latest) record per user_id */
| table Name, title, start_Date, end_Date, user_id