How To Write a Search query for the Timestamp field which is different from _time value

[anandhalagaras1]

Hi All,

Need a help to write a query based on the field "Timestamp" which is different from "_time" value.

Sample Event in XML Format:

Email: xyz@gmail.com
RoleName: User
RowKey: 123456
Timestamp: 2023-12-13T23:56:18.200016+00:00
UserId: mno
UserName: acho

This is one of the sample event in xml format and there is a specific field as "Timestamp" in the event and this "Timestamp" field is completely different from _time value. Hence I want to pull only the "Timestamp" value for a particular day might be yesterday 2023-12-13 i.e. from 2023-12-13 00:00:00 to 2023-12-13 23:59:59

So how can I write the query for the same.

index=abc host=xyz sourcetype=xxx

[VatsalJagani]

@anandhalagaras1 - Run the query like this:

<your index, sourcetype search> earliest=0 latest=now
<your_search>
| eval Timestamp=strptime(Timestamp,"%Y-%m-%dT%H:%M:%S.%6N%:z")
| addinfo
| where Timestamp>=info_min_time AND Timestamp<=info_max_time

With this query, you can change the timerange from UI to apply on search directly.

Performance Hint: This query will be run on "All Time" as the earliest and latest suggest in the first line of the query, you can twick it for dashboard to improve performance of the query.

I hope this helps!!!

[gcusello]

Hi @anandhalagaras1,

to compare dates you have to convert them in epochtime and compare using the eval command and strptime function:

e.g. to take only events with Timestamp in the period  from 2023-12-13 00:00:00 to 2023-12-13 23:59:59, yu could run:

<your_search> 
| eval 
   Timestamp=strptime(Timestamp,"%Y-%m-%d %H:%M:%S"),
   from=strptime("2023-12-13 00:00:00","%Y-%m-%d %H:%M:%S"),
   to=strptime("2023-12-13 23:59:59"","%Y-%m-%d %H:%M:%S")
| where Timestamp>=from AND Timestamp<=to

Ciao.

Giuseppe

[anandhalagaras1]

@gcusello 

index=xyz host=abc sourcetype=mkb
| eval
Timestamp=strptime(Timestamp,"%Y-%m-%d %H:%M:%S"),
from=strptime("2023-12-13 00:00:00","%Y-%m-%d %H:%M:%S"),
to=strptime("2023-12-13 23:59:59","%Y-%m-%d %H:%M:%S")
| where Timestamp>=from AND Timestamp<=to

When i used this search query I am not getting any events at all. I ran the query for last 30 days as well but there is no events getting displayed but actually there are events for the same. So is there any modification needs to be done in the query kindly let me know.

[gcusello]

Hi @anandhalagaras1 ,

sorry, I didn't notice that the format of Timestamp was different than the other two, please try this:

<your_search> 
| eval 
   Timestamp=strptime(Timestamp,"%Y-%m-%dT%H:%M:%S.%6N%:z"),
   from=strptime("2023-12-13 00:00:00","%Y-%m-%d %H:%M:%S"),
   to=strptime("2023-12-13 23:59:59"","%Y-%m-%d %H:%M:%S")
| where Timestamp>=from AND Timestamp<=to

Ciao.

Giuseppe

[isoutamo]

Hi

You must remember that as splunk is using a timeseries "database" those values are stored into buckets based on _time. Splunk always use that _time value when you are searching events from buckets!

This means that if you are using earliest + latest to get events from buckets and then make final selection based on that separate Timestamp field, Splunk do that only from events which _time is between earliest and latest. If your Timestamp fields has your needed values outside of earliest - latest then you didn't get those!

For that reason you should think (based on your data and use case), should you fix the ingestion to put that Timestamp field into _time? Or is your current way that _time have something else than Timestamp better way. IMHO is that you should fix your _time value on ingestion phase instead of trying to guess where those event could be (usually this leads quite open time spans).

r. Ismo