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

[twmoffit]

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.

[Claw]

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.

[Claw]

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.

[liondancer]

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 >= info_min_time AND (_time <= 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

[Richfez]

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.

[gcusello · Accepted solution]

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

[twmoffit]

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.

[sbbadri]

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

[sbbadri]

[]
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

[niketn]

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.