How to display the data for the last 4 weeks by week number?

[auaave]

Hi,

I want to create dashboard that displays the 4 weeks data by week number. The database normally have 3 months of data.

What should I do to display only the last 4 weeks data by week #, sort the table based on the values of the most recent week?

This is my statement for this week:

| dedup IDEVENT
| addinfo
| eval weeknumber=strftime(_time,"%U")
| chart count by DESCRIPTION weeknumber
| sort - 32 limit=10
| fields - 27 28
| rename 29 as "Wk29", 30 as "Wk30", 31 as "Wk31" 32 as "Wk32"

Thanks a lot!

[auaave]

@DalJeanis, on the below query, the "sortfield" is looking at the latest events data. However if there's no error that occurred on latest week, let's say week 7, it takes the error data of data of week 6.

Is there anyway we can avoid this?
Thanks!

| appendpipe
[ stats latest(eventcount) as sortfield max(_time) as _time by Location
| sort - sortfield limit=10
| streamstats count as descorder
| eval descorder=descorder+100
| table Location sortfield descorder]

[DalJeanis]

sure - you can kill everything that is too old. If everything should have the same date for each week, then you would use something like...

| appendpipe   [
    | stats latest(eventcount) as sortfield max(_time) as _time by Location 
    | eventstats max(_time) as maxtime
    | where _time = maxtime
    | sort - sortfield limit=10 
    | streamstats count as descorder 
    | eval descorder=descorder+100 
    | table Location sortfield descorder]

On the other hand, if the _time figures are daily, and "the last week" is anything with the last 7 days dates, then replace this line...

    | where _time = maxtime

...with this line...

    | where _time > maxtime - 604800

Note - 604800 is the number of seconds in a week , 7*24*3600.

[auaave]

@DalJeanis, the first one did the trick! You are the best! Thanks a lot for helping me.

[DalJeanis · Accepted solution]

We put together a couple of things for you.

Here's the beginning of the search ...

| dedup IDEVENT 
| rename COMMENT as "Get rid of events from unneeded weeks"
| where _time>relative_time(now(),"-4w@w")
| rename COMMENT as "Bin the events by week, then count them up"
| bin _time span=1w
| stats count as eventcount by DESCRIPTION _time

... for testing, the above may be replaced with this run-anywhere code ...

| gentimes start=12/25/16 end=1/19/17 increment=7d
| eval DESCRIPTION=mvappend("DESC11","DESC12","DESC13","DESC14","DESC15", 
                            "DESC16","DESC17123","DESC18","DESC19","DESC20ABCDEF",
                            "DESC01","DESC02","DESC03","DESC04","DESC0507123", 
                            "DESC06","DESC07","DESC08","DESC09","DESC10")
| bin starttime as _time span=1w
| table _time DESCRIPTION
| mvexpand DESCRIPTION
| eval eventcount = random()%100
| stats max(eventcount) as eventcount by _time DESCRIPTION
| rename COMMENT as "Everything above this creates sample data down to the stats command."

...then continue like this...

| rename COMMENT as "Figure out which DESCRIPTIONS to retain, drop the rest, prepend sort order to description"
| appendpipe 
    [| stats latest(eventcount) as sortfield latest(_time) as _time by DESCRIPTION 
     | sort - sortfield limit=10 | streamstats count as descorder | eval descorder=descorder+100 | table DESCRIPTION descorder]
| eventstats max(descorder) as descorder by DESCRIPTION
| where isnotnull(descorder)
| eval DESCRIPTION = descorder." ".DESCRIPTION
| fields - descorder

| rename COMMENT as "Figure out if we are rolling over a year here"
| appendpipe 
    [| stats min(_time) as mintime max(_time) as maxtime 
     | eval rollover=if(strftime(mintime,"%Y")!=strftime(maxtime,"%Y"),"Y","N")
     | table rollover]
| eventstats max(rollover) as rollover

| rename COMMENT as "calculate the week name.  Putting an underscore before the number will move rollover weeks to the right"
| eval weeknumber=strftime(_time,"%U")     
| eval weeknumber=case(weeknumber<10 AND rollover="Y","Wk_".weeknumber,  
                        true(),"Wk".weeknumber)

| rename COMMENT as "Chart the data, then pull off the sort order off the front of the description."
| chart max(eventcount) as count over DESCRIPTION by weeknumber
| eval DESCRIPTION=substr(DESCRIPTION,5,len(DESCRIPTION)-4)

... resulting in output that looks like this...

DESCRIPTION   Wk51   Wk52   Wk_01  Wk_02   

DESC19          59     83      67     99  
DESC07          27     19      15     91  
DESC16          28     88      96     88  
DESC09          37     25      73     81  
DESC02          38     22      58     78  
DESC11          51     95      87     75  
DESC17123       29     61      17     73  
DESC04          36     68      80     68  
DESC18          54     58      66     66  
DESC13           1      9      69     53  

[auaave]

Thanks DalJeanis, thank you for your reply. The above output is what I'm looking for, however I am overwhelmed by the statement. I am just a newbie and never had previous experience of writing expressions / statements. I will try this out step by step and see if I can make it work.

[auaave]

@DalJeanis, this is really great! I have learned a lot from this query 🙂

[DalJeanis]

Absolutely!

What you can do for learning purposes is, copy the first chunk of code, then add lines one at a time, and see what each one does to the output.

When doing this, and looking at the appendpipe parts with a subsearch in square brackets [] after it, is to remove the appendpipe and just run the data into the next command inside the brackets, until you get to the end of the brackets, then put it all back and continue. This is because appendpipe processes all the records through the subsearch, and then appends them onto the end of the current set of data.

So, for example, to learn this section...

| rename COMMENT as "Figure out which DESCRIPTIONS to retain, drop the rest, prepend sort order to description"
| appendpipe 
    [| stats latest(eventcount) as sortfield latest(_time) as _time by DESCRIPTION 
     | sort - sortfield limit=10 | streamstats count as descorder | eval descorder=descorder+100 | table DESCRIPTION descorder]
 | eventstats max(descorder) as descorder by DESCRIPTION
 | where isnotnull(descorder)
 | eval DESCRIPTION = descorder." ".DESCRIPTION
 | fields - descorder

...you would start with this...

| rename COMMENT as "Figure out which DESCRIPTIONS to retain, drop the rest, prepend sort order to description"
| stats latest(eventcount) as sortfield latest(_time) as _time by DESCRIPTION 

...then add this...

  | sort - sortfield limit=10 

...then this...

| streamstats count as descorder 

... and so on...

[alemarzu]

Hello there, try something like this.

... | eval week_num=case((date_mday > 0 AND date_mday <= 7), "week #1", (date_mday > 7 AND date_mday <= 14), "week #2", (date_mday > 14 AND date_mday <= 21), "week #3", date_mday > 21, "week #4", 1==1, null())
        | chart count by DESCRIPTION week_num
        | sort - week_num

Hope it helps.

[auaave]

Hi Alemarzu, thanks a lot for your reply but I can't make it work 😞

this is my query

| dedup IDEVENT
| addinfo
| eval week_num=case((date_mday > 0 AND date_mday <= 7), "week #1", (date_mday > 7 AND date_mday <= 14), "week #2", (date_mday > 14 AND date_mday <= 21), "week #3", date_mday > 21, "week #4", 1==1, null())
| chart count by DESCRIPTION week_num

[alemarzu]

Can you explaing why ? What was the output ?