I have a query like below :
bla bla ...| lookup mylookupfile.csv Hostname as Name output Status Creation_Date
| eval Status=MVDEDUP(Status) |eval Creation_Date=mvindex(Creation_Date,-1)| then rest of my query
Here issue happens when while matching for the Hostname i get two Status values
So above query gives me output where :
1) i am getting Creation_Date field as the latest date only
2) But Status i am receiving both Active and Destroyed
i want to get in Status field only the corresponding value of Status for latest(Creation_Date)
How can i do that
If I understand the problem correctly then this should help.
bla bla ...| lookup mylookupfile.csv Hostname as Name output Status Creation_Date
| eval created = strptime(Creation_Date, "format string that matches the date format")
| sort 1000 - created
| fields - created
| head 1
| then rest of my query
The lookup file already associates Status with Creation_Date so all the query needs to do is find the most recent Creation_Date value. We do that by converting the Creation_Date field to epoch form (assuming it is not already an epoch) then sorting the dates in descending order. The first event in the results is the latest date.