We help IT Professionals succeed at work.

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

macentrap
macentrap asked
on
221 Views
Last Modified: 2014-08-21
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.
Comment
Watch Question

Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

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
Excel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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)

Author

Commented:
Thanks Glenn,
is it possible with VBA please

Kind Regards

Author

Commented:
also, Please could you help how could I group incident data in 30 min interval.
Just on the incident tab

Author

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
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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

Author

Commented:
Thank You Glenn,
sure will submit new question for users

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.