topic Re: How To Write a Search query for the Timestamp field which is different from _time value in Splunk Search

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

anandhalagaras1 — Thu, 14 Dec 2023 07:21:11 GMT

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

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

gcusello — Thu, 14 Dec 2023 07:51:45 GMT

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

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

anandhalagaras1 — Thu, 14 Dec 2023 08:22:16 GMT

@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.

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

isoutamo — Thu, 14 Dec 2023 08:37:24 GMT

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

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

gcusello — Thu, 14 Dec 2023 08:53:58 GMT

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

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

VatsalJagani — Thu, 14 Dec 2023 12:45:26 GMT

@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!!!