How to display the Time(SavedTime) from the saved sourcetype?

[Thulasinathan_M]

I'm trying to store the results into a source_type and use the saved sourcetype to check whether the Event is already there in saved sourcetype or not. If it's not in saved sourcetype, I'm inserting the event. If the event is there in sourcetype and the time is greater than the time from current search then I'm updating the event with the earliest event. But the below query fails to display the Time(SavedTime) from the saved sourcetype and my query is failing to update events. Any advise would be very much appreciated. Thanks in advance!!

source=testSource
| stats count earliest(_time) as Time first(host) as host first(source) as source by EventCode
| join type=left EventCode [ search index=main sourcetype=saved_sourcetype | eval savedTime=strptime(Time, "%Y-%m-%d %H:%M:%S")
| stats count as Known values(Time) as sTime values(host) as host values(source) as source by EventCode]
| fillnull Known value=0
| eval insertRequired=if(Known=0, "Yes", "No")
| eval UpdateRequired=if(Time < savedTime , "Yes", "No")
| eval SaveAction=case(
insertRequired == "Yes" AND UpdateRequired != "Yes", "insert",
UpdateRequired == "Yes", "update",
1=1, "ignore"
)
| eval Time=strftime(Time, "%Y-%m-%d %H:%M:%S")
| stats count earliest(savedTime) as savedTime latest(Time) as Time values(Known) as Known first(host) as host last(source) as source by EventCode, SaveAction, insertRequired, UpdateRequired

[isoutamo]

Hi

If I look this correctly, you haven't any savedTime on stats in sub search?

| join type=left EventCode 
    [ search index=main sourcetype=saved_sourcetype 
    | eval savedTime=strptime(Time, "%Y-%m-%d %H:%M:%S") 
    | stats count as Known values(Time) as sTime values(host) as host values(source) as source by EventCode] 
| fillnull Known value=0 

If you want that savedTime exists after stats you must add it into stats' parameters.

Maybe this should be

    [ search index=main sourcetype=saved_sourcetype 
    | eval savedTime=strptime(Time, "%Y-%m-%d %H:%M:%S") 
    | stats count as Known values(savedTime) as savedTime values(host) as host values(source) as source by EventCode] 

 r. Ismo

[Thulasinathan_M]

Hi @isoutamo 
Thanks, my bad. I'm wondering how I missed that, really a good catch. I've adjusted the query and everything is working fine. But the update part is not working as I wish. I want to update the existing event from the saved_sourcetype, but it's insering it as newEvent. Could you please advise whether this is how it works or is there a work-around to update the existing event.

</>source=testSource
| stats count earliest(_time) as time first(host) as host first(source) as source by EventCode
| join type=left EventCode [ search index=main sourcetype=saved_sourcetype earliest=1 latest=now()
| stats count as Known Values(Time) as SavedTime by EventCode]
| fillnull Known value=0
| eval SavedTime = coalesce(SavedTime, "N/A")
| eval savedTime=strptime(SavedTime, "%Y-%m-%d %H:%M:%S")
| eval insertRequired=if(Known=0, "Yes", "No")
| eval UpdateRequired=if(time < savedTime , "Yes", "No")
| eval SaveAction=case(
insertRequired == "Yes" AND UpdateRequired != "Yes", "insert",
UpdateRequired == "Yes", "update",
1=1, "ignore"
)
| where SaveAction != "ignore"
| eval SavedTime = if(SaveAction == "update", strftime(time, "%Y-%m-%d %H:%M:%S"), SavedTime)
| eval Time=strftime(time, "%Y-%m-%d %H:%M:%S")
| stats values(SavedTime) as SavedTime latest(Time) as Time values(Known) as Known first(host) as host last(source) as source values(SaveAction) as SaveAction, values(insertRequired) as insertRequired values(UpdateRequired) as UpdateRequired by EventCode
| fields - SavedTime, Known
| collect index=main sourcetype="saved_sourcetype"</>

[richgalloway]

---

@Thulasinathan_M wrote:

Hi @isoutamo 
Thanks, my bad. I'm wondering how I missed that, really a good catch. I've adjusted the query and everything is working fine. But the update part is not working as I wish. I want to update the existing event from the saved_sourcetype, but it's insering it as newEvent. Could you please advise whether this is how it works or is there a work-around to update the existing event.

---

The collect command only writes new events.  Indexed events cannot be changed in any way, by any method.

[isoutamo]

Please use </> block on editor when you paste code! It's much easier to read and we can be sure that it's exactly what you have paste.

I see there a couple of issues:

• SavedTime and savedTime are two separate variables (maybe editor has changed those and both are using same CamelCases?)
• You cannot use savedTime=strptime(SavedTime, "%Y-%m-%d %H:%M:%S") for mv variable, if returns always null, not converted values
• You couldn't do strptime for value "N/A"
• Use always index=<your index> on search, you cannot be sure what are users default search indexes!
• Try to avoid to use main index! Always create separate indexes for different purposes, even on your own test instance
• When you have only couple of values (e.g. in SaveAction) it's better to use SaveAction in ("insert", "update") instead of use SaveAction != "ignore". Splunk is not good for look negations in performance point of view.