How to calculate Active Weeks

[sangaraju]

Hi All 

I would like to calculate active weeks (weeks count where there is data).

The below is the scenario

RepoName     *Week1*         * Week2*          * Week3*      **Active Weeks** 

repo1                     10                         5                          7                             3

repo2                    abc                    <data>                                                  2

repo3                                                                              fslkdfs                     1   

any help would be highly appreciated. 

[ITWhisperer]

| eval ActiveWeeks=0
| foreach Week*
  [| eval ActiveWeeks=if(isnull('<<FIELD>>'),ActiveWeeks,ActiveWeeks + 1)]

[sangaraju]

Thanks ITWhisperer for the tip. Kind of working...but, now I am seeing my ActiveWeeks column as 1 for all rows.

This is my search string...I used your snippet in it. Am I placing it correctly? Thanks for the help.

Search String:

index=myIndex source=mySource earliest=-30d@d
| bin _time span=1w
| eval weeknumber=strftime(_time,"%U")
| eval time=strftime(_time, "%Y-%m-%d %l:%M:%S %p")
| chart sum(sum) as Traffic by repoName, time
| eval ActiveWeeks=0
| foreach time
[| eval ActiveWeeks=if(isnotnull('Traffic'),ActiveWeeks,ActiveWeeks + 1)]
| sort - time
| addtotals
| rename Total as TotalSum

[ITWhisperer · Accepted solution]

index=myIndex source=mySource earliest=-30d@d
| bin _time span=1w
| eval weeknumber=strftime(_time,"Week%U")
| eval time=strftime(_time, "%Y-%m-%d %l:%M:%S %p")
| chart sum(sum) as Traffic by repoName, weeknumber
| eval ActiveWeeks=0
| foreach Week*
[| eval ActiveWeeks=if(isnotnull('<<FIELD>>'),ActiveWeeks,ActiveWeeks + 1)]
| addtotals fieldname=TotalSum
| eval TotalSum=TotalSum-ActiveWeeks

[sangaraju]

Thanks a ton ITWhisperer.

This is 95% close to what I am looking. 

My only struggle is to avoid the week where there is no data..... I modified the string a little bit. But, I am always getting ActiveWeeks as 5 for a 30day span. If I dont have data for week1...I want to avoid that from 5 weeks duration

ActivitySum includes ActiveWeeks also. So, I substracted the weeknumber from ActivitySum . 

I would like to see Activeweeks as 4 (removing that week from the weeks count from 30days span. ) not sure how to substract the null data weeks.... 😞

index=myindex source=mysource earliest=-30d@d

| bin _time span=1w
| eval weeknumber=strftime(_time,"Week%U")
| eval time=strftime(_time, "%Y-%m-%d %l:%M:%S %p")
| chart sum(sum) as Traffic by repoName, weeknumber
| eval ActiveWeeks=0
| foreach Week*
[| eval ActiveWeeks=if(isnotnull('weeknumber'),ActiveWeeks,ActiveWeeks + 1)]
| addtotals fieldname=ActivitySum
| eval ActivitySum = ActivitySum-ActiveWeeks
| eval Activity=round(ActivitySum/ActiveWeeks,0)

[sangaraju]

ITWhisperer:

I think, I figured it out. Thanks for the help. I am accepting your tip as Accepted Solution. But, my final string appears like below though...I modified it a bit. 

index=myindex source=mysource earliest=-30d@d
| bin _time span=1w
| eval weeknumber=strftime(_time,"Week%U")
| eval time=strftime(_time, "%Y-%m-%d %l:%M:%S %p")
| chart sum(sum) as Traffic by Service, weeknumber
| eval ActiveWeeks=0
| foreach Week*
[| eval ActiveWeeks=if(isnull('<<FIELD>>'),ActiveWeeks,ActiveWeeks + 1)]
| addtotals fieldname=WeeklyActivitySum
| eval WeeklyActivitySum = WeeklyActivitySum-ActiveWeeks

[PickleRick]

If you're already calculating weeknumber, why do you calculate the string time representation?

You should chart by weeknumber in order to get week-long statistics, not time.

Aaaaand, you can simplify it by using timechart

index=myIndex source=mySource earliest=-30d@d
| timechart span=1w sum(sum) by repoName
| eval week="Week".strftime(_time,"%U")
| fields - _time
| transpose 0 header_field=week column_name=repo
| eval ActiveWeeks=0
| foreach Week*
  [ | eval ActiveWeeks=ActiveWeeks+coalesce('<<FIELD>>',0) ]

 In fact, if I remember correctly, timechart should take care of nulls and give you 0 if there were no hits in given time period so you probably could lose the coalesce call altogether and get away with ActiveWeeks+'<<FIELD>>'

[sangaraju]

Hi PickleRick

Thanks for the suggestion. But, that is not what I am expecting. My calculations supposed to be addition of all Weeks (in 30day span) . Which is working fine as TotalSum column. I just need to know the weeks where there is data in ACtiveWeeks column. 

Like 

RepoName    Week1 Week2 Week3  ... TotalSum                  activeweeks

rep1                      3             3          4                    10                                        3

rep2                                       6          1                        7                                        2

rep3                                                    4                       4                                           1

Hope that makes sense

[PickleRick]

Of course you can addtotals. You didn't mention that in the beginning.

[sangaraju]

Thanks PickleRick, 

My focus was on ActiveWeeks , not on the totals. Anyways, thanks a lot for the help. Much appreciated. 

[sangaraju]

Thanks for the quick response PickleRick. 

But, I didnt exactly get your question, My source would be a Summary Index. I am running the query for last 30days. Is anything specific you want me to look for in events?

[PickleRick]

Ok. And what do your events look like?