Splunk Search

How to drill down with readable format of date and NOT EPOCHs?

joydeep741
Path Finder

I have a search

index=abc sourcetype=xyz | bucket created_time span=1w | stats count by date_epoch | eval date_readable = strptime(date_epoch, "%m-%d-%Y %H:%M:%S")
This results in a BAR GRAPH
Y-AXIS -> Count
X-AXIS -> date_readable

BUT, when the user clicks on any BAR, the drill down takes him to the search page with ALL THE EVENTS and not just the clicked events.
That is happening because of last condition in search query, where I am converting the EPOCH to READBLE.

I need to show READABLE format of date on graph and at the same time want my drill down to work.
Drill down seems to work only on EPOCHS.

How can I achieve this?

Tags (3)
0 Karma
1 Solution

niketn
Legend

@joydeep741, your current query seems incorrect as created_time seems to be use to bucket time as 1w span. However, date_epoch has been used in the stats command afterwards.

We are not sure whether any of these two are actually converted from string time to epoch time or not. strptime() converts string time to epoch and strftime() converts epoch time to string. Also between eval and fieldformat command the later converts the value for display while retaining the original data type i.e. if you use strftime() with fieldformat, underlying value will be epoch time but display value will be string time.

With a brief of these behaviors assuming created_time is the string time based on which you want to create this chart and chart drilldown, and that it has format MM-DD-YYYY HH:MM:SS, you can try the following

 index=abc sourcetype=xyz 
| eval _time=strptime(created_time,"%m-%d-%Y %H:%M:%S")
| timechart span=1w count

PS: You would need further base search based on Overall selected time in Time Picker as you need to work with created_time as earliest and latest and not _time as earliest and latest time. However, it becomes a bit complicated because your string date format is not sorted as YYYY-mm-dd HH:MM:SS instead of which it is mm-dd-YYYY HH:MM:SS. So you would need to ensure that time selected is a wider range to cover all the events with required created time. And also additional filter within query to compare epoch created time to fall within the range of required earliest and latest time.

You can re-evaluate, whether you always need to use created time as _time for your searches. Only if so, you should define props.conf in a way that created_time is selected as event timestamp. However, this might not be true so verify the requirement first.

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

Try this:

index=abc sourcetype=xyz 
| eval _time = date_epoch
| timechart span=1w count 
0 Karma

joydeep741
Path Finder

Hi @woodcock

This gives me right chart.
I am able to drill down.
BUT, say I selected events for the month of MAY in the bar graph.
On the drill down page it will show me all events of MAY.

AND NOT all events where date_epoch was in MAY.

So I am getting slightly deviated results on drill down.

0 Karma

woodcock
Esteemed Legend

Do not rely on automatic drilldown; manually set it and carry over the | eval _time = date_epoch part. See theDashboards Examplesapp or wait for @niketnilay to show up and save the dashboard day.

0 Karma

niketn
Legend

@joydeep741, your current query seems incorrect as created_time seems to be use to bucket time as 1w span. However, date_epoch has been used in the stats command afterwards.

We are not sure whether any of these two are actually converted from string time to epoch time or not. strptime() converts string time to epoch and strftime() converts epoch time to string. Also between eval and fieldformat command the later converts the value for display while retaining the original data type i.e. if you use strftime() with fieldformat, underlying value will be epoch time but display value will be string time.

With a brief of these behaviors assuming created_time is the string time based on which you want to create this chart and chart drilldown, and that it has format MM-DD-YYYY HH:MM:SS, you can try the following

 index=abc sourcetype=xyz 
| eval _time=strptime(created_time,"%m-%d-%Y %H:%M:%S")
| timechart span=1w count

PS: You would need further base search based on Overall selected time in Time Picker as you need to work with created_time as earliest and latest and not _time as earliest and latest time. However, it becomes a bit complicated because your string date format is not sorted as YYYY-mm-dd HH:MM:SS instead of which it is mm-dd-YYYY HH:MM:SS. So you would need to ensure that time selected is a wider range to cover all the events with required created time. And also additional filter within query to compare epoch created time to fall within the range of required earliest and latest time.

You can re-evaluate, whether you always need to use created time as _time for your searches. Only if so, you should define props.conf in a way that created_time is selected as event timestamp. However, this might not be true so verify the requirement first.

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

joydeep741
Path Finder

created_time is a typo.. It is actually date_epoch.
But I have understood the logic here.
Thanks man.. This resolves the issue.

niketn
Legend

@joydeep741 , glad this worked for you. Do consider adding right filters in your base search in case create_time is not _time! Let us know if you need further help!

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

joydeep741
Path Finder

@niketn,

Drill down works fine. But as you said, since the events displayed will consider the earliest and latest of _time and not created_time, the results on the search page are slightly deviated.

I am not sure how I can tweak the logic to cater this part as well.

0 Karma

hagjos43
Contributor

This may be an instance where renaming created_time to _time and using a timechart might be best.

try:

index=abc sourcetype=xyz | rename created_time as _time | timechart span=1w count
0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...