topic Re: Compare the Date from the Lookup and Write new value. in Splunk Search

How to compare the date from the lookup and write new value?

zen29d — Tue, 06 Jun 2023 13:12:01 GMT

Hello, Splunkers.

Problem Statement:
I've searched the data with "date" and "score" to get the latest data and got the result. (Date may or may not be the current time.)

index=sampledata | head 10 | table Date Score | sort -Date| head 1

Result:

Date	Score
2023-02-24	20

I have created a lookup table "score.csv" to behave like variables to store data.

Saved_Date	Saved_Score
2023-01-15	30

Now, I want to compare something like below

| eval current_timestamp=strptime(Date, "%Y-%m-%d") | lookup score.csv Saved_Date <Required Help > | eval saved_timestamp=strptime(Saved_Date, "%Y-%m-%d") | eval new=if(current_timestamp > saved_timestamp, "Yes","No") | where new="Yes" | <want to overwrite with "Date" and "Score" in score.csv>

Re: Compare the Date from the Lookup and Write new value.

yuanliu — Mon, 05 Jun 2023 08:38:49 GMT

Something like

Re: Compare the Date from the Lookup and Write new value.

zen29d — Mon, 05 Jun 2023 13:08:59 GMT

Thanks for the help, I tried and it worked, and thought it will solve the logic however, I think, I messed up more.
The logic goes like this.

Current_Date = Date Current_Score = Score Previous_Date = Stored_Date Previous_Score = Stored_Score 1. Read the Current_Date, Current_Score, Previous_Date, Previous_Score. (Solved) 1. Compare the Scores: Delta = Current_Score - Previous_Score. 2. If a change is observed: 0<Delta>0, Generate an Alert. 3. If NOT, evaluate the Current_Date and Current_Score and write back the lookup.

Re: Compare the Date from the Lookup and Write new value.

yuanliu — Tue, 06 Jun 2023 08:15:29 GMT

I don't think you can use one command to both do alerting and write back with the logic you just described. So, the two functions have to be in separate searches.

1. Write-back

In the above, if the two scores are different, simply write back the values from lookup itself.

2. Alerting

Re: Compare the Date from the Lookup and Write new value.

ITWhisperer — Tue, 06 Jun 2023 10:38:59 GMT

Not quite true - you can combine them into a single search using appendpipe - here is a runanywhere example which updates the date if the scores are the same, and returns no results, or returns results if the scores are different without updating the csv

Re: How to compare the date from the Lookup and Write new value?

PickleRick — Tue, 06 Jun 2023 10:51:10 GMT

As an additional hint not directly related to the main issue - it's often better to store the timestamp as a unix timestamp than a string rendition of it. It's easier to manipulate, calculate offsets and so on. You only render it to string when displaying.