Splunk Search

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

zovinchong
New Member

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]

Tags (3)
0 Karma

to4kawa
Ultra Champion

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

0 Karma

zovinchong
New Member

@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

0 Karma

to4kawa
Ultra Champion

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.

0 Karma

niketn
Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

zovinchong
New Member

Hi @niketnilay

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

Thanks!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...