[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

VBA - to calculate server uptime along with incident data in 30 minutes interval

Hi Experts,

in attached excel file, the is
"server uptime" tab- this provides the server uptime in 30 min interval where data is not available it gets reflected in "Summary" tab as "pass" or "fail".

 "Incidents-logged":  Also included is the number of incidents logged in during the day with date and time.

I would like to have extra information on the Summary tab:

if there is incident logged during the day,  the count of incident should reflect the 30 minute time slot.


Example:
if there 4 incidents on 1 july between time slot 8am to 8.30am then
pass data should reflect

Pass (4) in the the summary tab .


Please could you help me.
Server-uptime.xlsx
Kind Regards
macentrap.
0
macentrap
Asked:
macentrap
  • 5
  • 4
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
Clarifications needed:
1) Is it possible that there are incidents while the status is "Pass"?  
2) What about "Fail" conditions; do you also want to see the count of incidents?

For example, based on your actual data
1) Summary: July 1, 8:00 (cell C18) would now display "Pass (1)"
July 1, 8:30 (cell C19) would now display "Pass (2)"
July 1, 9:00 (cell C20) would remain the same "Pass"
July 1, 9:00 (cell C21) would now display "Pass (2)"
and so on
2) July 7, 8:30 (cell I19) would now show "Fail (2)"

-Glenn
0
 
macentrapAuthor Commented:
Hi Glenn
Yes its possible to have incidents when server uptime has "pass"
So does the "fail".

The monitoring tool on server is giving false positive n false negatives, hence trying to present the data along with incidents which are logged when end user reports n outage.

Thank you
0
 
Glenn RayExcel VBA DeveloperCommented:
I have a non-VBA solution for you if you don't mind the long formula.

Insert in cell C2 and copy down and across:
=IF(C$1<=TODAY()-1,IF(ISERROR(VLOOKUP(TEXT(C$1,"d/m/yyy")&TEXT($B2,"h:mm"),'server uptime'!$K:$L,2,FALSE)),"Fail","Pass"),"") & IF(COUNTIFS(Incidents,">="&(C$1+$B2),Incidents,"<"&(C$1+$B2+TIMEVALUE("00:30:00")))>0," ("&COUNTIFS(Incidents,">="&(C$1+$B2),Incidents,"<"&(C$1+$B2+TIMEVALUE("00:30:00")))&")","")
(The bolded section is added to your original formula)

To simplify the formula I added a named range, "Incidents" which covers 'Incidents-logged'!$A$4:$A$590.  Example workbook attached.

I also modified the conditional formatting rules to account for the possible changes in the values.

-Glenn
EE-Server-uptime.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Glenn RayExcel VBA DeveloperCommented:
If you could do without the paranthesis around the incident count (ex., "Pass 3" instead of "Pass (3)") the formula could be a little shorter:
=IF(C$1<=TODAY()-1,IF(ISERROR(VLOOKUP(TEXT(C$1,"d/m/yyy")&TEXT($B2,"h:mm"),'server uptime'!$K:$L,2,FALSE)),"Fail","Pass"),"") & " " & IFERROR(1/(1/(COUNTIFS(Incidents,">="&(C$1+$B2),Incidents,"<"&(C$1+$B2+TIMEVALUE("00:30:00"))))),"")
(bold part added/changed)
0
 
macentrapAuthor Commented:
Thanks Glenn,
is it possible with VBA please

Kind Regards
0
 
macentrapAuthor Commented:
also, Please could you help how could I group incident data in 30 min interval.
Just on the incident tab
0
 
macentrapAuthor Commented:
Hi Glenn,
they other bit which I forgot was the "server uptime" has the users information connected at each timeslot would it possbile to have that inluded in Summary too please .

Sorry i just realized
0
 
Glenn RayExcel VBA DeveloperCommented:
Frankly, the formula works so well, I would be inclined NOT to change it.  It is instanteous and will work on larger data ranges so long as the named range is updated ("Incidents").  A VBA approach would require some event to refresh the data (either a macro run, a button, a Worksheet_Change event, etc.) and, if not re-creating this function, would overwrite the data in all the Summary cells each time.

That said, you have now added two additional requests beyond the scope of the original question.  The first question - adding 30-min interval captures on the Incident tab - is already answered by the logic used to solve the first question (i.e., the compount COUNTIF function).  However, you need to specify exactly how you want this result to be shown.  Note that for the month of July there are 1,488 results on the Summary sheet (half-hour increments over 31 days).

The second question would require another example file that shows the user information you speak of.  IMO, that should be a separate question altogether.

Regards,
-Glenn
0
 
macentrapAuthor Commented:
Thank You Glenn,
sure will submit new question for users
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now