Help with code.

[Forsaken]

Hello,

I trying to get a few things automated, maybe someone can help

Cell A1 will have the following in it:

PE4-KWI-ME#show interface Serial1/1:1
Serial1/1:1 is up, line protocol is up
  Hardware is PA-MC-8TE1 Plus
  Description: Service:IGNITE MPLS;CID:1142115;SR:1055422;Configured on:31-08-2007;by User:YALEWS;
  MTU 1500 bytes, BW 512 Kbit, DLY 20000 usec, 
     reliability 255/255, txload 1/255, rxload 1/255
  Encapsulation FRAME-RELAY IETF, crc 16, loopback not set
  Keepalive set (10 sec)
  LMI enq sent  0, LMI stat recvd 0, LMI upd recvd 0
  LMI enq recvd 82927, LMI stat sent  82927, LMI upd sent  0, DCE LMI up
  LMI DLCI 0  LMI type is ANSI Annex D  frame relay DCE
  FR SVC disabled, LAPF state down
  Broadcast queue 0/64, broadcasts sent/dropped 0/0, interface broadcasts 0
  Last input 00:00:00, output 00:00:00, output hang never
  Last clearing of "show interface" counters 1w2d
  Input queue: 0/75/0/0 (size/max/drops/flushes); Total output drops: 0
  Queueing strategy: weighted fair
  Output queue: 0/1000/64/0 (size/max total/threshold/drops) 
     Conversations  0/3/16 (active/max active/max total)
     Reserved Conversations 0/0 (allocated/max allocated)
     Available Bandwidth 512 kilobits/sec
  5 minute input rate 0 bits/sec, 3 packets/sec
  5 minute output rate 0 bits/sec, 3 packets/sec
     1764145 packets input, 53946184 bytes, 0 no buffer
     Received 0 broadcasts, 0 runts, 0 giants, 0 throttles
     0 input errors, 0 CRC, 0 frame, 0 overrun, 0 ignored, 0 abort
     1760050 packets output, 52945060 bytes, 0 underruns
     0 output errors, 0 collisions, 0 interface resets
     0 output buffer failures, 0 output buffers swapped out
     0 carrier transitions  no alarm present
  Timeslot(s) Used:9-16, subrate: 64Kb/s, transmit delay is 0 flags

Now from this code, I need a few things automated.

1) "Serial1/1:1 is up, line protocol is up" this has to always be up up so in cell B1 i would like something like if serial/0/0/0 (number may vary), is up up then display pass if up down or down down or down up then display fail in cell B1.

2) "counters 1w2d" this should be always above 24hr/1d, so a formula that will do something like if counters is less then 1d cell C1 should display a Fail and if its above 1d it should say pass.

3) In this row "0 input errors, 0 CRC, 0 frame, 0 overrun, 0 ignored, 0 abort" first calculate the acceptable value this should be (row "1764145 packets input, 53946184 bytes") the number of bytes 53946184 bytes * 8 =431569472 then divide the result by 1000000 = 431.569472 so anything above 431 in this row "0 input errors, 0 CRC, 0 frame, 0 overrun, 0 ignored, 0 abort" should display a fail in cell D1 or pass if its below 431. The numbers will vary; the wording will always be the same.

4) In this row "0 carrier transitions" if the value it’s above 0 in cell E1 display a fail if its 0 display a pass.

PS: the numbers will vary and the text will always be the same.

If anyone can help I’m opened to suggestions.

Thanks,
Forsaken.

 

[Forsaken]

anyone?

 

[dr_shivan]

The above are strings of text.. thus you need to isolate those text of interest with those that are trash. then only you can get things done. If all the strings are constant, then you would require a lot of helper cells IMHO to get your desired output.

Here's a bit to get you started off.
text in A2 "Serial1/1:1 is up, line protocol is up"
E2=MID(A2,16,2),
F2=RIGHT(A2,2)
G2=IF(AND(E1="up",F1="up"),"PASS","FAIL")