Network Monitoring Tool, calculate PC disconnections.

[mexiro]

Hello Guys,

I have somehow a complex question {As per my opinion }

I have created a tool, which monitors PCs in a network if they have a ping or not. Each 10 min. Then some macro writes in a sheet the pc name, and the time disconnected (no ping response)

Example is below:
PC NAME TIME of Disconnection
AFT00017 26/Oct/14 10:26
AFT00018 26/Oct/14 10:26
AFT00017 26/Oct/14 10:36
AFT00018 26/Oct/14 10:36
AFT00017 26/Oct/14 10:46
AFT00017 26/Oct/14 10:56
AFT00019 26/Oct/14 11:06
AFT00017 26/Oct/14 11:16
AFT00019 26/Oct/14 11:16
AFT00017 26/Oct/14 11:26
AFT00018 26/Oct/14 11:26
AFT00019 26/Oct/14 11:26

As you see here, macro is running each 10 min, and recording offline pcs. Example above: AFT00017 is offline between: 10:26-10:56, then goes online, then again offline in 11:16-11:26.

So from the above list I would like to have a summary (each week) like the below

PC Name Time of Disconnection Duration
AFT00017 26/Oct/14 10:26 30min
AFT00018 26/Oct/14 10:26 20min
AFT00019 26/Oct/14 11:06 30min
AFT00017 26/Oct/14 11:16 20min


After this summary comes the second part,

I need to visualize this as a gannt chart but again there is a problem. As you see in the above data AFT00017 is repeated twice, so in Gannt chart will be displayed as separate job (x axis). I would need some type of Gannt chart which could see in details (min hour) during a week or a month of time. So details are needed and the chart be very long so scrollable to see details on when the disconnections are.

Some google charts get close to it. There are some google chart which may display somehow what I want but I don't know if that is possible for very big data.
https://developers.google.com/chart/...llery/timeline

or even Calendar chart of google gets close to it

https://developers.google.com/chart/...llery/calendar


If you know some tips that would be very valuable

Thanks in advance!

[martindwilson]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.



Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
cross post @
http://www.mrexcel.com/forum/excel-q...nnections.html

[BuZZarD73]

After studying your problem above, I think it's complicated greatly by the way the data is handled, due to disconnects and what are essentially reconnects being recorded (or assumed) in the same column.

The logic of calculating what you need from a single column is too complex.

If I'm reading it right, then you first need a formula to calculate the trigger point which would be when the device was last reported NOT offline which would be a missing entry of a 10 minute interval after a previous recorded disconnect instance - so find the first disconnect time that was >=11 minutes after the last recorded entry.

This would provide you with your first operand and then you have to use further logic to ascertain the next gap in the 10 minute polling interval as the termination point to provide your second operand for what would essentially be a sum.

I think you need to consider putting a column in that shows the time of the reconnect - then you can use a much more workable calculation - ideally, there will be a condition in your macro that states that if there is no entry in a polling interval, then put an entry in another column labelled 'reconnect'.

These are my thoughts anyway. Although it doesn't solve the problem, hope it helps with the approach.

[mexiro]

After studying your problem above, I think it's complicated greatly by the way the data is handled, due to disconnects and what are essentially reconnects being recorded (or assumed) in the same column.

The logic of calculating what you need from a single column is too complex.

If I'm reading it right, then you first need a formula to calculate the trigger point which would be when the device was last reported NOT offline which would be a missing entry of a 10 minute interval after a previous recorded disconnect instance - so find the first disconnect time that was >=11 minutes after the last recorded entry.

This would provide you with your first operand and then you have to use further logic to ascertain the next gap in the 10 minute polling interval as the termination point to provide your second operand for what would essentially be a sum.

I think you need to consider putting a column in that shows the time of the reconnect - then you can use a much more workable calculation - ideally, there will be a condition in your macro that states that if there is no entry in a polling interval, then put an entry in another column labelled 'reconnect'.

These are my thoughts anyway. Although it doesn't solve the problem, hope it helps with the approach.

Thanks for the thoughts my friend. it i's difficult to set up the trigger when there is a disconnection. Especially for PCs that are offline for long time like for a day. I am considering inserting a column checking the next 10 min if there is an available record for that PC. If not it is back online. But again the process is somehow complicated when summing up.

There should be somewhere in the web some tools for monitoring PC/Network up-time., I am not the first IT to have in excel such data

thanks,