topic How to change the _time to values inside the event data? in Splunk Search

How to change the _time to values inside the event data?

twmoffit — Tue, 29 Sep 2020 14:43:58 GMT

I want to make area graphs of data usage on individual servers based on the timestamp given in the event data and not the default _time values.

This is an example of an event:

2017-06-29 19:32:57.254, DBNAME="BRMTPRD", SNAP_DATE="2017-06-18 03:00:32.0", TS_TYPE="REDO", TS_NAME="ONLINE_REDO_STBY", ALLOCATED_KB="3686400", USED_KB="3686400", FREE_KB="0", PCT_USED="100", LARGEST="0"

I want to use the "SNAP_DATE" field as my "_time" values and then measure the "USED_KB" per day of "SNAP_DATE."

Anything helps, thanks.

Re: How to change the _time to values inside the event data?

sbbadri — Tue, 29 Sep 2020 14:41:50 GMT

[]
SHOULD_LINEMERGE=false
NO_BINARY_CHECK=true
TIME_FORMAT=%Y-%m-%d %H:%M:%S.%3N
TIME_PREFIX=\d+-\d+-\d+\s\d+:\d+:\d+.\d+\,\sDBNAME=\"\S+\",\sSNAP_DATE=\"
MAX_TIMESTAMP_LOOKAHEAD=21
CHARSET=UTF-8

Re: How to change the _time to values inside the event data?

gcusello — Fri, 30 Jun 2017 13:51:05 GMT

Hi twmoffit,
try something like this:

your_search
| eval SNAP_DATE=strptime(SNAP_DATE,"%Y-%m-%d %H:%M:%S.%N")
| bin span=1d SNAP_DATE
| eval SNAP_DATE=strftime(SNAP_DATE,"%Y-%m-%d")
| chart sum(USED_KB) by SNAP_DATE

Bye.
Giuseppe

Re: How to change the _time to values inside the event data?

niketn — Fri, 30 Jun 2017 13:53:38 GMT

Similar to what @sbbadri proposed following should be added for your existing sourcetype definition (TIME_PREFIX=SNAP_DATE=" should be sufficient):

[Your_Source_Type]
TIME_FORMAT=%Y-%m-%d %H:%M:%S.%N
TIME_PREFIX=SNAP_DATE="
MAX_TIMESTAMP_LOOKAHEAD=21

You would need to re-index existing data.

Re: How to change the _time to values inside the event data?

Richfez — Fri, 30 Jun 2017 13:55:12 GMT

Base Search ... | eval _time = strptime(SNAP_DATE, "%Y-%m-%d %H:%M:%S.%1Q") | timechart span=1d avg(USED_KB)

Obviously, replace avg(blah) with max or some other function as necessary.

Re: How to change the _time to values inside the event data?

twmoffit — Fri, 30 Jun 2017 13:55:51 GMT

Great that works but now how do I make it so that the data is split up by the database name. I want to be able to compare the usages by each database in the index and create one graph showing the difference.

Re: How to change the _time to values inside the event data?

sbbadri — Tue, 29 Sep 2020 14:41:54 GMT

... | rex field=_raw "DBNAME=\"(?\S+)\" | chart sum(USED_KB) over SNAP_DATE by DBNAME

Re: How to change the _time to values inside the event data?

Claw — Fri, 18 May 2018 15:29:56 GMT

Check out this blog.

https://www.splunk.com/blog/2016/09/16/i-cant-make-my-time-range-picker-pick.html

This will make the time range picker work and more.

Re: How to change the _time to values inside the event data?

liondancer — Tue, 05 Jun 2018 18:08:44 GMT

I followed the instructions on the blog post and could not get it to work. I have a time field within my logs as
year=2018 month=04 day=05 hour=20 event_count=100. The event came in 2018-06-03 2000. I want to use the time picker to select events by their year, month, day, hour time fields. NOT when they came in. I overrode _time as well. This is what I have in my source

        index=index_1 OR index=index_2 category=mobile event_type=hive_events zone=aws
        | eval _time=strptime(time,"%Y-%m-%d-%H:%M:%S") 
        | sort - _time
        | addinfo
        | where _time &gt;= info_min_time AND (_time &lt;= info_max_time OR info_max_time = "+Infinity")
        | eval DateHour=year."-".month."-".day."-".hour
        | eval Start_Time=strftime(info_min_time, "%Y-%m-%d-%H:00")
        | eval End_Time=strftime(info_max_time, "%Y-%m-%d-%H:00")
        | table DateHour _time Start_time info_min_time End_time info_max_time zone event_count

Re: How to change the _time to values inside the event data?

Claw — Tue, 05 Jun 2018 20:55:59 GMT

If I understand your example and you have date and time in year=2018 month=04 day=05 hour=20 you will have to convert this to a single field for the strptime command to work.

| eval my_time=year."-".month."-".day."-".hour."-0-0"
| eval _time=strptime(my_time,"%Y-%m-%d-%H:%M:%S")

Hope this helps.