topic Re: How to get compare latest values across an index with a lookup and display the latest? in Splunk Search

How to get compare latest values across an index with a lookup and display the latest?

hummingbird81 — Wed, 12 Mar 2025 12:00:15 GMT

Hi All, looking for some advice as in how to take the latest values from 2 datasets . We have a base search that pulls user details like name, start_date, end_date, title, location etc from an index =okta.

name	start_date	end_date	title	user_id
John Smith	2021-06-28T23:59:59.00+05:30	2025-06-28T23:59:59.00+05:30	Consultant	001

The above index has the most current data of a user.

Next we have another master lookup file (identities.csv) where we maintain all user details from past few years. This master lookup also contains same fields as the above index. For example:

name	start_date	end_date	title	user_id
John Smith	2021-06-28T23:59:59.00+05:30	2022-06-28T23:59:59.00+05:30	Administrator	001

Notice the end _date and title are different in the lookup.

Below is our current search that compares the 2 datasets. We want it to update the date fields or any other field whichever is the latest but at the moment it does NOT update the fields even if any field like end_date or title is modified under index.

index=okta stats latest(_time) as _time, values(profile.title) as title, values(profile.email) as email, values(profile.startDate) as start_Date,values(profile.endDate) as end_Date, values(profile.Name) as Name by user_id | append [|inputlookup identities.csv] |stats latest(_time) as _time, latest(profile.title) as title, latest(profile.email) as email, latest(profile.startDate) as start_Date,latest(profile.endDate) as end_Date, latest(profile.Name) as Name by user_id | table Name title start_date end_date user_id

Running the above query still shows the old info which has the old end_date and title even though i am using |stats latest() . Pls advise how to retrieve the latest be it date format or be it string format which is "title"

name	start_date	end_date	title
John Smith	2021-06-28T23:59:59.00+05:30	2022-06-28T23:59:59.00+05:30	Administrator

Re: How to get compare latest values across an index with a lookup and display the latest?

kiran_panchavat — Wed, 12 Mar 2025 14:31:42 GMT

@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

Re: How to get compare latest values across an index with a lookup and display the latest?

hummingbird81 — Wed, 12 Mar 2025 14:07:00 GMT

Thank you so much. Let me test it out in detail but it looks promising.

Re: How to get compare latest values across an index with a lookup and display the latest?

kiran_panchavat — Wed, 12 Mar 2025 14:32:51 GMT

@hummingbird81 Sounds good! Please test it and let me know. If everything works fine, kindly accept the solution.

Re: How to get compare latest values across an index with a lookup and display the latest?

hummingbird81 — Wed, 12 Mar 2025 15:16:03 GMT

can you pls confirm why are you doing a " mod_time=if(isnull(_time), strptime(end_Date, "%Y-%m-%dT%H:%M:%S.%2Q%z"), _time)" in the append portion ? What is the need to do a strptime here ?

Re: How to get compare latest values across an index with a lookup and display the latest?

kiran_panchavat — Wed, 12 Mar 2025 15:26:48 GMT

@hummingbird81

Why the if(): To handle cases where _time might be missing in the CSV, using end_Date as a fallback.
Why strptime: To convert end_Date from a string to a numeric epoch timestamp for proper sorting.
Why in append: Because the CSV’s timestamp situation is less certain than Okta’s