Status of nested hourly fields within daily chart (Screenshot attached)

[zovinchong]

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]

[to4kawa]

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

[zovinchong]

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

1. How can I have have the split by status for each time_group?
2. Will this be possible if I use | dbxquery query=" " ? When I use it, I won't have the _time field in my table.
3. If I'm not using | dbxquery, my data will being indexed into the splunk event every 1 hour, and I want to remove duplicates from the unique ID value. How can I add this statement into the sample query you have provided? |dedup 1 BATCH_ID

Some required fields in raw data: BATCH_ID, CREATED_DT, STATUS, _time

[to4kawa]

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.

[niketn]

@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

[zovinchong]

Hi @niketnilay

Sorry link to image here: https://pasteboard.co/JajlU1N.jpg

Thanks!