Dashboards & Visualizations

Results displayed by day

sweiland
Path Finder

Hi there,

I have a dashboard which splits the results by day of the week, to see for example the amount of events by Days (Monday, Tuesday, ...)

My request is like that:

myrequest | convert timeformat="%A" ctime(_time) AS Day | chart count by Day | rename count as "SENT" | eval wd=lower(Day) | eval sort_field=case(wd=="monday",1, wd=="tuesday",2, wd=="wednesday",3, wd=="thursday",4, wd=="friday",5, wd=="saturday",6 ,wd=="sunday",7) | sort sort_field | fields - sort_field, wd

 

Only problem with the request is that sometimes a day or two could be missing in the histogram (0 entries), and I wanted to have always the 7 days displayed (even with 0 results).

Any way to do this ? Any help appreciated! (like a left join in SQL)

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

It looks like my query is producing the zeros you wanted, but has more than one entry for each day.  I think we can correct that with stats.

index=_internal 
| timechart span=1d count 
| convert timeformat="%A" ctime(_time) AS Day 
| stats sum(count) as count by Day
| rename count as "SENT" 
| eval wd=lower(Day) 
| eval sort_field=case(wd=="monday",1, wd=="tuesday",2, wd=="wednesday",3, wd=="thursday",4, wd=="friday",5, wd=="saturday",6 ,wd=="sunday",7) 
| sort sort_field 
| fields - sort_field, wd 
| table Day SENT
---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

This works for me.

index=_internal 
| timechart span=1d count 
| convert timeformat="%A" ctime(_time) AS Day 
| rename count as "SENT" | eval wd=lower(Day) 
| eval sort_field=case(wd=="monday",1, wd=="tuesday",2, wd=="wednesday",3, wd=="thursday",4, wd=="friday",5, wd=="saturday",6 ,wd=="sunday",7) 
| sort sort_field 
| fields - sort_field, wd 
| table Day SENT
---
If this reply helps you, Karma would be appreciated.
0 Karma

sweiland
Path Finder

Hmmm nope, here is my first attempt (with my query, I miss the Sunday = 0 event), and also comparison with your query (below)

SplunkTest.pngMaybe I need then a group by ?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

It looks like my query is producing the zeros you wanted, but has more than one entry for each day.  I think we can correct that with stats.

index=_internal 
| timechart span=1d count 
| convert timeformat="%A" ctime(_time) AS Day 
| stats sum(count) as count by Day
| rename count as "SENT" 
| eval wd=lower(Day) 
| eval sort_field=case(wd=="monday",1, wd=="tuesday",2, wd=="wednesday",3, wd=="thursday",4, wd=="friday",5, wd=="saturday",6 ,wd=="sunday",7) 
| sort sort_field 
| fields - sort_field, wd 
| table Day SENT
---
If this reply helps you, Karma would be appreciated.

sweiland
Path Finder

Thanks, it works almost flawlessly ! 🙂

Just a problem when I put "Since start of week", Saturday is missing.. dont know why..

sweiland_0-1598042307460.png

I would have thought that I should miss Saturday & Sunday (because I put start of week and both days are not in the request at all)

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

"Start of week" is Sunday. Saturday was tomorrow so timechart thinks you only care about Sun-Fri. Try using latest=+1w@w.

---
If this reply helps you, Karma would be appreciated.
0 Karma

sweiland
Path Finder

Maybe I just find a way to limit the timepicker to 7 days range minimum (but did not find how), or I just do not display the panel if the range is less than 7 days

Both case I do not know how to do, but will try to find 🙂 (2nd optionis better, to hide the panel if range too short)

0 Karma
Get Updates on the Splunk Community!

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

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...