Hi All,
I am fetching data from the data base and have the below fields (no raw time provided):
1. Date field (eg. 2020-04-28 00:00:00.0 ["%Y-%m-%d %H:%M:%S.%Q" format]
2. Status field (eg. A,B,C,D,E)
How can I have the below nested hour columns be inside the daily field?: (I have attached the image of what I want to achieve but in Splunk)
1. 10am - 12pm
2. 12pm - 3pm
3. 3pm - 6pm
4. 6pm - 10am
Currently, I am only able to achieve daily view of status, but now I want the status for above hours within a daily view in a column bar chart.
My current query to achieve the daily view of status in column bar chart.
| dbxquery query=" " connection=" "
| eval create_date = strptime(CREATED_DT, "%Y-%m-%d %H:%M:%S.%Q")
| where create_date >= relative_time(strptime(strftime(now(),"%d-%b-%y"),"%d-%b-%y") , "-3d") AND create_date <= strptime(strftime(now(),"%d-%b-%y %H:%M:%S.%Q"), "%d-%b-%y %H:%M:%S.%Q")
| eval create_date_new = strftime(create_date,"%d-%b-%y")
| chart count over create_date_new by STATUS
Appreciate if anyone can help me with this issue. (Attached screenshot of what I want)
Thanks!
Zovin
][1]
sample query:
| tstats count where index=_internal sourcetype=splunkd_* by _time span=1h sourcetype
| eval date=strftime(_time,"%d-%h-%Y"), date_hour=tonumber(strftime(_time,"%H"))
| eval times=case(date_hour >= 10 AND date_hour < 12, "10AM-12PM"
,date_hour >= 12 AND date_hour < 15, "12PM-3PM"
,date_hour >= 15 AND date_hour < 18, "3PM-6PM"
,date_hour >= 18 OR date_hour < 10, ">6PM")
| stats sum(count) as counts by date times sourcetype
| eval {sourcetype}=counts
| fields - sourcetype counts
| stats values(*) as * by date times
Try Visualization >> Column Chart with trellis by date
@to4kawa
I think it somewhat works but the counts is not the same as what I should have and I'm missing the split by status. Sample screenshot of what I need: https://pasteboard.co/JajlU1N.jpg
I have 3 further questions:
Some required fields in raw data: BATCH_ID, CREATED_DT, STATUS, _time
1 Use status
instead of sourcetype
2 Yes, it is. | eval create_date = strptime(CREATED_DT, "%Y-%m-%d %H:%M:%S.%Q") | eval _time=create_date
3 I have no idea without log.
@zovinchong image did not get uploaded so try uploading to any image sharing site and share the link using <img>
button or shortcut Ctrl+G