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.
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
| eval ActiveWeeks=0
| foreach Week*
[| eval ActiveWeeks=if(isnull('<<FIELD>>'),ActiveWeeks,ActiveWeeks + 1)]
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
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
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)
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
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>>'
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
Of course you can addtotals. You didn't mention that in the beginning.
Thanks PickleRick,
My focus was on ActiveWeeks , not on the totals. Anyways, thanks a lot for the help. Much appreciated.
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?
Ok. And what do your events look like?