Splunk Search

How to chart data in chronological order by day of the week, not alphabetically?

halkelley
Path Finder

can I chart data by day of the week, but have it come out chronologically instead of alphabetically?.. i.e. I want Sunday, Monday, Tuesday... instead of Friday, Monday, Saturday...

Tags (3)
1 Solution

strive
Influencer

I think this is not the cleanest wat, but this should work. Have a CSV lookup for date_wday to day_number. After chart, do the lookup then the result will be 3 columns. Sort based on day_number and then ignore the day_number.

I will be eager to know if there is any clean way to get this done

View solution in original post

somesoni2
Revered Legend

May be like this (without lookups)

source=Ipc.Recon.Web New merchant. Redirecting to SWC. ServiceKey: 1846869E3691300C, AppPid: 0F4633D95001300C NOT M66128 | dedup MPID | eval date_wday=strftime(_time,"%w-%A")| chart count AS auth.net BY date_wday | appendcols [search source=Ipc.Sync.Web New merchant. Redirecting to SWC. ServiceKey: 970A36376A11300C, AppPid: A476DC90C001300C | dedup MPID | eval date_wday=strftime(_time,"%w-%A")| chart count AS clover BY date_wday ]| eval date_wday=replace(date_wday,"(\d+-)(\w+)","\2")

halkelley
Path Finder

@somesoni2 that works great too...and don't need the lookup...thanks!

newbie2tech
Communicator

thanks @somesoni2 ,date_wday=strftime(_time,"%w-%A") suggestion from your query helped crack my usecase

0 Karma

strive
Influencer

I think this is not the cleanest wat, but this should work. Have a CSV lookup for date_wday to day_number. After chart, do the lookup then the result will be 3 columns. Sort based on day_number and then ignore the day_number.

I will be eager to know if there is any clean way to get this done

strive
Influencer

There is one more option, same as sort but without using external lookup. See example 5 in this link

http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Sort

0 Karma

halkelley
Path Finder

@strive that worked!...thanks very much...a lot better than prefixing a 0-6 in front of every weekday name!

ppablo
Retired

@strive Thanks for chiming in. I was hoping you or another search guru would take a stab at it 😛 I definitely want to learn from the different solutions to this.

0 Karma

halkelley
Path Finder

it just seems if you chart By date_wday, you would want it to come out sunday, monday, tuesday, etc., and not the days alphabetically

0 Karma

halkelley
Path Finder

sorry it's kind of messy, but here it is:

source=Ipc.Recon.Web New merchant. Redirecting to SWC. ServiceKey: 1846869E3691300C, AppPid: 0F4633D95001300C NOT M66128 | dedup MPID | chart count AS auth.net BY date_wday | appendcols [search source=Ipc.Sync.Web New merchant. Redirecting to SWC. ServiceKey: 970A36376A11300C, AppPid: A476DC90C001300C | dedup MPID | chart count AS clover BY date_wday ]

0 Karma

ppablo
Retired

@halkelley

Would you be able to paste your current search you're using? This will make it easier for search experts to help figure out your issue and work from there 🙂

0 Karma

halkelley
Path Finder

yeah, the problem is I'm grouping all data into day of the week bins, so I have Sunday = cumulative data for every Sunday, etc.

0 Karma

ppablo
Retired

Hi @halkelley

Have you tried using the timechart command?
http://docs.splunk.com/Documentation/Splunk/6.1.3/SearchReference/Timechart

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...