How to display date info from past weeks?

[senthamilselvan]

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

[woodcock · Accepted solution]

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]

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]

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]

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