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.
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
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.
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.
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
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.
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
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.
... | rex field=_raw "DBNAME=\"(?\S+)\" | chart sum(USED_KB) over SNAP_DATE by DBNAME
[]
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
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.