create a monitoring excel sheet with static data

[jamesbond19]

Hi, this is James

For an IT company I'm working with, I would like to create a monitoring excel sheet for myself with EXCEL 2003

Requirement : Use external data and create alerts

I have used web queries (external data) for an INTRAnet website, (only works in the company) to extract data. The data from the INTRAnet website is real time, being updated every 1 minute, showing resource utilization for each individual machine.

Having it said in fore mentioned, I have now 1 sheet, that is using web queries (external) data, and also ticked the option to refresh it every 1 minute. I have currently called this sheet "ez1 CPU"
***************************************************************************************************************

Now I have created another sheet, to use an IF formula, that shows if the resource for 1 particular machine is for example higher than a value it will show "NOT OK"....if below that value it will show "OK"....

This new sheet, I have called "ez1 monitoring"

Example
Cell A3 has the server name with formula --> ='ez1 CPU'!B16 <-- basically means, show data from another worksheet from cell B16...nothing special
Cell B3 has the IF condition with formula --> =IF(C3<=2,"OK","NOT OK") <-- basically comparing the value with cell C3....in addition I have used conditional formatting such as condition EQUAL ="NOT OK" <-- then it will be RED COLOR, and vice versa
Cell C3 has the resource utilization with formula --> ='ez1 CPU'!C16 <-- basically means, show data from another worksheet from cell C16....nothing special

In this sheet, I have 5000 servers, thus 5000 of the forementioned A3, B3, C3 formula's and conditional formatting

In this sheet I have only 1 issue, before describing my issue I would like to give some background information.....the external data from the INTRAnet website, shows resource utilization for each server, per minute...over a period of 1 hour......THIS MEANS I have each server 60 times duplicate....as I did not know how to go around this, I have only used the formula to show the data from the previous sheet for the first 5000 servers........but I assume there should be a formula available, to only show the most up to date, or at least the server that is at the top, instead of the other 59 servers

How can I go around this with a formula, to only show 1 server, instead of 59 others....thus not showing duplicates...but also ensure I have the most up to date server name with it's resource utilization?

***************************************************************************************************************

My issue now is......it's all nice and good....but I do not have time, to look through 5000 rows, to see which are in RED color.....I want in a new sheet (i have called the new sheet "SUMMARY") a function that ONLY shows which are in RED......but not only in RED, I want to know what is in RED, and how high the value is.....that means I want to know which server (A3 row), OK/NOT OK (B3 row), and how high the utilization is (C3 row)

I have been looking on the website, not knowing how to describe my requirement/criteria, but found something that was quite similar, but not yet what I want

The formula I am currently using is
=INDEX('ez1 monitoring'!A3:A5000,SMALL(IF(ISNUMBER((SEARCH(Summary!$R$3,'ez1 monitoring'!A3:A5000))*(SEARCH(Summary!$R$6,'ez1 monitoring'!B3:B5000))),ROW('ez1 monitoring'!B3:B5000)-MIN(ROW('ez1 monitoring'!B3:B5000))+1,""),ROW(A1)))

According to some people they call this a "ARRAY formula"......you also see twice a SEARCH function.......in the summary sheet I can define via
SEARCH(Summary!$R$3 <-- in summary sheet, cell R3....a particular server name, if I want to look up a specific server
SEARCH(Summary!$R$6, <-- in summary sheet, cell R6...the status.....at this point in time I filled in "NOT OK"....thus it will show me all servers that are with status NOT OK....this is a great help....I'm stuck here with 2 things

issue 1 : I want to see the resource utilization, I have no clue how I can incorporate in this array formula (or an alternative formula) to see the values from cell C3 row.

issue 2 : I dragged this so called array formula....for 40 rows further...as I do not expect more than 40 servers would have issues. The issue here is that the formula is quite stupid, when dragging, it adds numbers from A3 to A4, to A5....let me show you example, and highlighting it in BOLD

First formula in sheet SUMMARY cell Z1
=INDEX('ez1 monitoring'!A3:A5000,SMALL(IF(ISNUMBER((SEARCH(Summary!$R$3,'ez1 monitoring'!A3:A5000))*(SEARCH(Summary!$R$6,'ez1 monitoring'!B3:B5000))),ROW('ez1 monitoring'!B3:B5000)-MIN(ROW('ez1 monitoring'!B3:B5000))+1,""),ROW(A1)))

Second formula in sheet SUMMARY cell Z2
=INDEX('ez1 monitoring'!A4:A5001,SMALL(IF(ISNUMBER((SEARCH(Summary!$R$3,'ez1 monitoring'!A4:A5001))*(SEARCH(Summary!$R$6,'ez1 monitoring'!B4:B5001))),ROW('ez1 monitoring'!B4:B5001)-MIN(ROW('ez1 monitoring'!B4:B5001))+1,""),ROW(A1)))

Third formula in sheet SUMMARY cell Z3
=INDEX('ez1 monitoring'!A5:A5002,SMALL(IF(ISNUMBER((SEARCH(Summary!$R$3,'ez1 monitoring'!A5:A5002))*(SEARCH(Summary!$R$6,'ez1 monitoring'!B5:B5002))),ROW('ez1 monitoring'!B5:B5002)-MIN(ROW('ez1 monitoring'!B5:B5002))+1,""),ROW(A1)))

Because of this stupid issue, I would only show me for example 4 servers with status NOT OK....while the REAL AMOUNT OF ISSUES are 10 or perhaps 12

How it actually SHOULD BE according to me, highlighting again in bold

First formula in sheet SUMMARY cell Z1
=INDEX('ez1 monitoring'!A3:A5000,SMALL(IF(ISNUMBER((SEARCH(Summary!$R$3,'ez1 monitoring'!A3:A5000))*(SEARCH(Summary!$R$6,'ez1 monitoring'!B3:B5000))),ROW('ez1 monitoring'!B3:B5000)-MIN(ROW('ez1 monitoring'!B3:B5000))+1,""),ROW(A1)))

Second formula in sheet SUMMARY cell Z2
=INDEX('ez1 monitoring'!A3:A5000,SMALL(IF(ISNUMBER((SEARCH(Summary!$R$3,'ez1 monitoring'!A3:A5000))*(SEARCH(Summary!$R$6,'ez1 monitoring'!B3:B5000))),ROW('ez1 monitoring'!B3:B5000)-MIN(ROW('ez1 monitoring'!B3:B5000))+1,""),ROW(A2)))

Third formula in sheet SUMMARY cell Z3
=INDEX('ez1 monitoring'!A3:A5000,SMALL(IF(ISNUMBER((SEARCH(Summary!$R$3,'ez1 monitoring'!A3:A5000))*(SEARCH(Summary!$R$6,'ez1 monitoring'!B3:B5000))),ROW('ez1 monitoring'!B3:B5000)-MIN(ROW('ez1 monitoring'!B3:B5000))+1,""),ROW(A3)))

You see what I mean?

**************************************************************************************************************
I have more requirements, but I think I will ask them later....I will put them below for the moment, otherwise I need to explain the entire idea what I'm trying to achieve with an excel sheet

Issue 3 : In the same sheet "SUMMARY", it is good that I can see which servers are in status "NOT OK".....but I want to know for how many minutes they were at this status......for 1 minute? 2 minutes? 5 minutes? 10 minutes? As a manager, I do not have the time, to look into the sheet, and then look at my watch and wait it off for 15 minutes, that would be insane.......remember the external data (web queries) is being refreshed every 1 minute....and the real time data INTRAnet website is also being updated every 1 minute

Issue 4 : I also want something pop up, as a message box, whenever a the total utilization for ALL 5000 servers has exceeded a certain amount. What I have basically done, is in cell number U12 --> =SUM('ez1 CPU'!C16:C5012) that would basically show me the total resource utilization on all 5000 servers with the SUM formula....

Then via internet I was able to find a VB code, to trigger an alert, see below

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("U12").Value >= 84832 Then _
MsgBox "Available memory at EZLINUX1 LPAR level is at a critical stage. Please immediately raise a Sev2 ticket to ENLNR_MFHLISG", vbOKOnly, "Alert"

The issue here is, even if cel U12 exceeds the value 84832 it will NOT show an alert......even not after a fresh update (static data)......what I did notice, whenever I change a field manually or enter a plan number anywhere in the sheet (does not matter where, could for example be XX3, or AA5, or GG1321) then it will show an alert...........how do I go around this?

**************************************************************************************************************

Thanks for reading my message, for whomever may read this. I am a basic excel user, majority of the formula's I have used, were searched via internet, so somehow in a way, I made a mess of it........but I hope with some guidance/help somebody could really help me, because I am now at that point after 1 week searching on internet I am officially STUCK

If required, I can send you the excel sheet that has macro's and vb coding on excel 2003 (noticed it's not compatible with 2010, because I sent it to a colleague who uses 2010, and it immediately hangs/freezes)......do note, that if I sent you the sheet, you can not use the "enable automatic refresh" as it's takes data from an INTRAnet website

Thanks James -----

 

[jamesbond19]

Hi all

Just solved issue 1 & 2 via vbcoding

For the ones that are asking how I solved it, visit below website, it explains in detail and also has an sample xls....FYI I'm using excel 2003
Lookup with multiple criteria and display multiple search results using excel formula, part 3 | Get Digital Help - Microsoft Excel resource