Splunk Search

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

zen29d
Explorer

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>

 

 

 

 

 

Labels (1)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Something like

index=sampledata | head 10 | table Date Score | sort -Date| head 1
| rename Date as Saved_Date, Score as Saved_Score
| append
    [| inputlookup score.csv ``` Saved_Date, Saved_Score ``` ]
| sort - Saved_Date
| head 1
| outputlookup score.csv
Tags (2)

zen29d
Explorer

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.



0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

index=sampledata | head 10 | table Date Score | sort -Date| head 1
| rename Date as Saved_Date, Score as Saved_Score
| append
    [| inputlookup score.csv ``` Saved_Date, Saved_Score ``` ]
| stats list(Saved_Date) as Saved_Date list(Saved_Score) as Saved_Score dc(Saved_Score) as distinct
| foreach Saved_*
    [eval <<FIELD>> = if(distinct == 1, mvindex(<<FIELD>>, 0), mvindex(<<FIELD>>, 1)]
| outputlookup score.csv

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

2. Alerting

index=sampledata | head 10 | table Date Score | sort -Date| head 1
| append
    [| inputlookup score.csv ``` Saved_Date, Saved_Score ``` ]
| stats values(Date) as Date values(Saved_Date) as Saved_Date values(Score) as Score values(Saved_Score) as Saved_Score
| eval Delta = Score - Saved_Score
| where Delta != 0
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

| makeresults
| eval score=random()%10
| rename score as saved_score, _time as saved_time
| append 
    [| inputlookup score.csv]
| appendpipe
    [| stats list(saved_time) as saved_time list(saved_score) as saved_score dc(saved_score) as distinct
    | foreach saved_*
        [| eval <<FIELD>>=if(distinct=1, mvindex(<<FIELD>>,0),mvindex(<<FIELD>>,1))]
    | fields - distinct
    | outputlookup score.csv
    | where 1==2 ]
| stats values(saved_time) as time values(saved_score) as score range(saved_score) as delta
| where delta != 0
0 Karma
Get Updates on the Splunk Community!

Enterprise Security Content Update (ESCU) | New Releases

In September, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...

New in Observability - Improvements to Custom Metrics SLOs, Log Observer Connect & ...

The latest enhancements to the Splunk observability portfolio deliver improved SLO management accuracy, better ...

Improve Data Pipelines Using Splunk Data Management

  Register Now   This Tech Talk will explore the pipeline management offerings Edge Processor and Ingest ...