Splunk Search

How to display date info from past weeks?

senthamilselvan
Engager

My Query:

| tstats count where index=p___ AND error* by sourcetype,_time span=1d | eval count=tostring(count,"commas") |eval Day=strftime(_time,"%A") | eval Date=strftime(_time,"%m-%d-%Y") | stats list by sourcetype | rename list(Date) as Date list(Day) as "Day of the week" list(count) as Count | table sourcetype Date "Day of the week" Count

Output
sourcetype Date Day of the week Count

p____db2 08-26-2017 Saturday 4,44
08-27-2017 Sunday 6,24

p____syslog 08-27-2017 Sunday 45

Expected Output

sourcetype Date Day of the week Count

p____db2 08-26-2017 Saturday 4,44
08-27-2017 Sunday 6,24

p____syslog 08-26-2017 Saturday Not Available
08-27-2017 Sunday 45

0 Karma
1 Solution

woodcock
Esteemed Legend

One of the nice things about timechart is that it creates empty buckets for you ( count=0 ), so let's leverage that by inserting these 2 lines:

| timechart limit=0 useother=f span=1d count by sourcetype
| untable _time sourcetype count

Like this:

| tstats count where index=p___ AND error* by sourcetype,_time span=1d

| timechart limit=0 useother=f span=1d count by sourcetype
| untable _time sourcetype count

| eval count=tostring(count,"commas")
| eval Day=strftime(_time,"%A")
| eval Date=strftime(_time,"%m-%d-%Y")
| stats list by sourcetype
| rename list(Date) as Date list(Day) as "Day of the week" list(count) as Count
| table sourcetype Date "Day of the week" Count

View solution in original post

woodcock
Esteemed Legend

One of the nice things about timechart is that it creates empty buckets for you ( count=0 ), so let's leverage that by inserting these 2 lines:

| timechart limit=0 useother=f span=1d count by sourcetype
| untable _time sourcetype count

Like this:

| tstats count where index=p___ AND error* by sourcetype,_time span=1d

| timechart limit=0 useother=f span=1d count by sourcetype
| untable _time sourcetype count

| eval count=tostring(count,"commas")
| eval Day=strftime(_time,"%A")
| eval Date=strftime(_time,"%m-%d-%Y")
| stats list by sourcetype
| rename list(Date) as Date list(Day) as "Day of the week" list(count) as Count
| table sourcetype Date "Day of the week" Count

gcusello
SplunkTrust
SplunkTrust

Hi senthamilselvanj,
try

| tstats count where index=p___ AND error* by sourcetype,_time span=1d 
| timechart span=1d count AS Count by sourcetype
| untable _time sourcetype Count
| eval "Day of the week"=strftime(_time,"%A"), Date=strftime(_time,"%m-%d-%Y") 
| table sourcetype Date "Day of the week" Count

Bye.
Giuseppe

gcusello
SplunkTrust
SplunkTrust

Hi senthamilselvanj,
try something like this

| tstats count where index=p___ AND error* by sourcetype,_time span=1d 
| bin _time span=1d 
| stats count AS Count by _time sourcetype 
| eval "Day of the week"=strftime(_time,"%A"), Date=strftime(_time,"%m-%d-%Y") 
| table sourcetype Date "Day of the week" Count

Bye.
Giuseppe

senthamilselvan
Engager

Hi Giuseppe,

Thank you for the response. But i didn't get what i expected. Let me explain again.

We have 3 source types in an index. On one particular day i was searching the logs using the query (index=pdoa error* | stats count by sourcetype)

We have received out as below, for sourcetype1 , we have error on both sat & sun. for sourcetype2, we have error message on only Saturday.

sourcetype Date Day of the week Count
p_db2 08-26-2017 Saturday 4,44
08-27-2017 Sunday 6,24
p
_syslog 08-27-2017 Saturday 45
The requirement is like, if no error message like sourcetype2 also we have to populate the details as below.

p____syslog 08-27-2017 Saturday 0

As of now we are not getting any row populating.

Thanks
Selvan

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