comparing the data of lookup with index data and extrcat the info from Index

[smanojkumar]

index=*sap sourcetype=FSC*
| fields _time index Eventts ID FIELD_02 FIELD_01 CODE ID FIELD* source
| rex field=index "^(?<prefix>\d+_\d+)"
| lookup lookup_site_ids.csv prefix as prefix output name as Site
| eval name2=substr(Site,8,4)
| rex field=Eventts "(?<Date>\d{4}-\d{2}-\d{2})T(?<Time>\d{2}:\d{2}:\d{2}\.\d{3})"
| fields - Eventts
| eval timestamp = Date . " " . Time
| eval _time = strptime(timestamp, "%Y-%m-%d %H:%M:%S.%3N")
| eval Time = strftime(_time, "%Y-%m-%d %H:%M:%S.%3N"), Condition="test"
| eval Stamp = strftime(_time, "%Y-%m-%d %H:%M:%S.%3N")
| lookup Stoppage.csv name as Site OUTPUT Condition Time as Stamp
| search Condition="Stoppage"
| where Stamp = Time
| eval index_time = strptime(Time, "%Y-%m-%d %H:%M:%S.%3N")
| eval lookup_time = strftime(Stamp, "%Y-%m-%d %H:%M:%S.%3N")
| eval CODE=if(isnull(CODE),"N/A",CODE), FIELD_01=if(isnull(FIELD_01),"N/A",FIELD_01), FIELD_02=if(isnull(FIELD_02),"N/A",FIELD_02)
| lookup code_translator.csv FIELD_01 as FIELD_01 output nonzero_bits as nonzero_bits
| eval nonzero_bits=if(FIELD_02="ST" AND FIELD_01="DA",nonzero_bits,"N/A")
| mvexpand nonzero_bits
| lookup Decomposition_File.csv Site as name2 Alarm_bit_index as nonzero_bits "Componenty_type_and_CODE" as CODE "Component_number" as ID output "Symbolic_name" as Symbolic_name Alarm_type as Alarm_type Brief_alarm_description as Brief_alarm_description Alarm_solution
| eval Symbolic_name=if(FIELD_01="DA",Symbolic_name,"N/A") , Brief_alarm_description=if(FIELD_01="DA",Brief_alarm_description,"N/A") , Alarm_type=if(FIELD_01="DA",Alarm_type,"N/A") , Alarm_solution=if(FIELD_01="DA",Alarm_solution,"N/A")
| fillnull value="N/A" Symbolic_name Brief_alarm_description Alarm_type
| table Site Symbolic_name Brief_alarm_description Alarm_type Alarm_solution Condition Value index_time Time _time Stamp lookup_time

[PickleRick]

Is there a question to it? What is it supposed to do?

[smanojkumar]

Hello @PickleRick ,

I have tried implementing that, the timetsamp in lookup is not correctly passed in this search, I was bit confused. Whatever the lookup has in timestamp value, it was passed as diffrent value in the search, I feels strange.

please let me know if i have missed anything.

Thanks!

[PickleRick]

Ok. Not knowing your use case, there are some general tips:

1) Don't overwrite _time unless you're absolutely sure what you're doing. If you must overwrite time by other value extracted calculated from your event high chance is that your source onboarding wasn't done correctly

2) As @gcusello a;ready pointed out - typically the best way of handling timestamps is using the unix epoch-based value, not a strftimed string representation.

These are general rules and sometimes there are border cases when you need to do otherwise. But here comes another painful truth

3) Be wary of timezones

[smanojkumar]

Hello @gcusello & @PickleRick ,

Thanks for your time!

I have converted both index time and Lookup time to epoch. It seems perfect with makeresults but while I'm using index data the time stamps were changed to an another new value in the search,

I herewith attched the snap of makeresults where its workign fine, and the other snaps as well,

Please let me know if i missed anything.

Thanks!

[gcusello]

Hi @smanojkumar ,

let me understand: why do you fix _time to a fixed value?

Ciao.

Giuseppe

[smanojkumar]

Hello @gcusello ,

I was just testing with single value, Obviously it will be dynamic.

Thanks again!

[gcusello]

Hi @smanojkumar ,

without the static value, does it run?

Ciao.

Giuseppe

[smanojkumar]

Hello @gcusello ,

If you mean with multiple values from Lookup, I didnt tried.

I would like to check the time from lookup with index timestamp events of deviation +0.5Sec or -0.5Sec from the time in index and i need to show the result.

Please let me know if there are any other way to do it.

Thanks!

[PickleRick]

Ok. Back to square one - what (in terms of business goal of your search, not technical means you're trying to use) is your search supposed to achieve?

[smanojkumar]

Hello @PickleRick ,

I have an alert which run on every 5mins, there are data from 23 index, which indexes are meeting the criterion that have been created as an alert, on the same index i need to ran diffrerent search to know the error message and match the event with the time stamp with an deviation of +0.5 Sec or -0.5 sec in the event and need to show the results in an another alert.

Please let me know if there are anything.

Thanks!

[gcusello]

Hi @smanojkumar ,

in general, to compare timestamps it's always better to transform both of them in epochtime format (using the strptime function of the eval command).

Ciao.

Giuseppe

[smanojkumar]

Hi @gcusello ,

As you see in 3rd screenshot, the smaple time that i have ingested is 2025-05-27 17:38:07.991, but in the 2nd screenshot the time stamp chnaged to 2025-05-23 05:25:50.795 in the field name Loo_time in the results , I dont know the reason.

also I have use only epoch time from the lookup while comparing with index data which is already in epoch time.
This was my only concern, Can you please help me to fix this. 

Thanks!