How to Span=2w but starting every firts monday of the month and so on?

[pstamati]

I have a metric that want to trend on a timechart but I need to span every 2 weeks, starting the 1 monday of each month (and the 3rd)
How can I do that?

[woodcock]

You will have to do the math manually to create a field like 1st_or_3rd_Monday and then add | where 1st_or_3rd_Monday = "YES" to your search to drop the other events.

[VatsalJagani]

Agree with you @woodcock. But Since long I'm thinking of logic to create 1st_or_3rd_Monday field but that is not very straight forward.

[DavidHourani]

Hi @pstamati,

This should do the trick for you :

  ....| timechart span=2w@w1  ....

Documentation for to define how you want to snap the time can be found here :
https://docs.splunk.com/Documentation/Splunk/7.2.6/SearchReference/SearchTimeModifiers#Specify_a_sna...

Cheers,
David

[DavidHourani]

Hi @pstamati, if you want to fix it for the first and third Monday then you won't be able to do it via the span command alone.
You'll have to first use span=1w@w1 then follow up with an eval to get rid of the first line if it is the second or fourth week making sure that your time chart will always start at week one or three, and then run another timechart to get the chart to span=2w. Let me know if you have trouble achieving that

[pstamati]

first of all, many thanks in advance for your help on this.
It partially worked, so I was originally using : ...|timechart span=2w@w7 count by exploitAvailable

So I replaced with your suggestion: ...| timechart span=w@w1 count by exploitAvailable | reverse | streamstats current=t count AS SERIAL | where SERIAL > 1 | reverse | fields - SERIAL | timechart span=2w@w1 count by exploitAvailable

_time NULL
2019-02-11 2
2019-02-25 2
2019-03-11 2
2019-03-25 2
2019-04-08 2
2019-04-22 2
2019-05-06 2
2019-05-20 1

So it worked for the dates, so it's not "spanning" using the 1st and 3rd mondays, but the counts I'm getting don't make sense,
For my original search: ...|timechart span=2w@w7 count by exploitAvailable I was getting these results:

_time No Yes
2019-02-17 1012 51867
2019-03-03 1088 60388
2019-03-17 757 41096
2019-03-31 781 58903
2019-04-14 912 74508
2019-04-28 850 79864
2019-05-12 1781 114675
2019-05-26 428 36136

[DavidHourani]

should be like this actually :
...| timechart span=w@w1 count by exploitAvailable | reverse | streamstats current=t count AS SERIAL | where SERIAL > 1 | reverse | fields - SERIAL | timechart span=2w@w1 sum(count) as count by exploitAvailable

Notice the second timechart is a sum(count) not a count...

Also this will always remove the first week of the chart so you will need to add a logic to check which week of the year it is and based on that decide to remove it or not instead of always removing 😄

[pstamati]

It worked but it's not consistently fixing the 1st and 3rd week of the month. I just realized if I play with dates, it shows the 2nd and 4th monday.
In any case thank you for your help

[DavidHourani]

Okay here we go, using the wiki link below, I used identified the 1st and 3rd week of every month also 5th week for some months based on the week number because it's the most stable value to work on, you can do it on day number as well if you like: https://en.wikipedia.org/wiki/ISO_week_date#Dates_with_fixed_week_number

From that I built the list of 2nd and fourth week of every month, which are the following in week numbers: 2,4,6,8,10,12,15,17,19,21,24,26,28,30,32,34,37,39,41,43,45,47,50,52

And I modified the search as follows to remove these weeks if they are the first week and only start with the one that followed :

...| timechart span=1w@w1 count as total by exploitAvailable
| eval weeknumber=strftime(_time,"%U")
| streamstats current=t count AS SERIAL
| search NOT (weeknumber IN (2,4,6,8,10,12,15,17,19,21,24,26,28,30,32,34,37,39,41,43,45,47,50,52 ) AND SERIAL=1)
| fields - SERIAL,weeknumber
| timechart span=2w@w1 sum(*) as *

You can use that to pick the specific weeks you don't want and exclude them from there.

Please make sure you up-vote the comments and accept the answer if this helps !

Cheers,
David

[pstamati]

@DavidHourani any chance you can give me how that example would look like?

[DavidHourani]

yeah have a look here for removing a line : https://answers.splunk.com/answers/294299/how-to-print-results-excluding-the-last-line-in-sp.html

your search would look something like this and in the logic to remove lines u should only remove if it's not the first or third week:

....| timechart span=w@w1  count as totalevents | reverse | streamstats current=t count AS SERIAL | where SERIAL > 1 | reverse | fields - SERIAL | timechart span=2w@w1  sum(count) as totalevents

[pstamati]

Hey, thanks for your response, I was using that for other metrics but, best I can say that value is not always giving me what I need, as the W1 stands for any Monday and 2W@ for every 2 weeks, but I'm looking to fix it only to 1st and 3rd Mondays.