#! /usr/local/bin/perl -w # A perl program to do things with the alarm log use FileHandle; use IPC::Open2; use Symbol; # use Tk; use Sybase::CTlib; use Time::Local; use strict; my(%dispatch) = (); my(%systemNames); systemNames(); # fill in the hash my $dbAlarm=new Sybase::CTlib 'ops','opsops','OPSYB1',''; $dbAlarm->ct_sql("use TomTest"); %dispatch = (help =>\&help,buildhistory=>\&buildHistory,createdbtables +=>\&createDbTables); my(@lines, $line); my $count = 0; #logMe(); my $numberOfArgs = @ARGV; if($numberOfArgs == 0) { help(); exit 1; } my($what) = shift(@ARGV); my($whatLower) = $what; $whatLower =~ tr/[A-Z]/[a-z]/; if ($what eq undef) { help(); exit 1; } if(exists($dispatch{$whatLower})) { &{$dispatch{$whatLower}}(@ARGV); exit 0; } help(); exit; sub buildHistory { # no input # for each entry in the notifAlarmLog Table build a history record my $sql = "SELECT distinct topic from notifAlarmEntry WHERE category like 'WARNING' OR category like 'OK NOW'"; my(@sources, $source); @sources = $dbAlarm->ct_sql($sql); my ($TLCount) = 0; # Keeps track of Transaction Log (TL) my ($StartStopCount) = 0; my ($esequence,$eutime,$edtime,$esource,$eprocess,$ecategory,$etopic +) = (0,1,2,3,4,5,6); foreach $source (@sources) { $TLCount++; # Incremnt TL $sql = "SELECT * from notifAlarmEntry where topic like '$source->[ +0]' ORDER by datetime"; # print "$sql\n"; my(@records,$recordA,$recordB); @records = $dbAlarm->ct_sql($sql); if (($TLCount%1000) == 0) { $sql = "dump transaction TomTest with truncate_only"; $dbAlarm->ct_sql($sql); } # these should now be in pairs, see that they are my($index); for ($index=0; $index<@records; ) { $recordA = $records[$index++]; $recordB = $records[$index]; $StartStopCount++; # Keeps track of each pair of Alarms (Start/S +top) print "$StartStopCount alarm(s) occurred \n"; my $result = GetPairs($recordA, $recordB); if($result < -1) { # maybe out of order $result = GetPairs($recordB, $recordA); } $index++ if($result == 0); print "Problem with $recordA->[$etopic] $recordA->[$esequence] $ +recordA->[$eutime] $recordA->[$edtime] $recordA->[$esource] $recordA +->[$eprocess] $recordA->[$ecategory] and $recordB->[$etopic] $recordB +->[$esequence] $recordB->[$eutime] $recordB->[$edtime] $recordB->[$e +source] $recordB->[$eprocess] $recordB->[$ecategory] \n" if($result ! += 0); } } } sub GetPairs { # two notifAlarmEntry records (Alarm: Start/Stop) # Input is two notifAlarmEntry records that should be an WARNING and + OK NOW # make a history if they are OK else return an error # return -1 if names are wrong (not same PS) # return -2 if 1st message is not WARNING # return -3 if 2nd message is not OK NOW # use strict; # my ($esequence,$eutime,$edtime,$esource,$eprocess,$ecategory,$etop +ic) = (0,1,2,3,4,5,6); my ($recordA) = shift; my ($recordB) = shift; # print "record A $recordA->[0] $recordA->[1] $recordA->[2] $reco +rdA->[3] $recordA->[4] $recordA->[5] $recordA->[6] \n"; # print "record B $recordB->[0] $recordB->[1] $recordB->[2] $reco +rdB->[3] $recordB->[4] $recordB->[5] $recordB->[6] \n"; return -1 if($recordA->[6] ne $recordB->[6]); return -2 if($recordA->[5] ne 'WARNING'); return -3 if($recordB->[5] ne 'OK NOW'); # found a pair my ($f1,$adoName,$f3) = split(":",$recordA->[6]); my $SiteWideName= $main::systemNames{$adoName} . " : $f3"; # print ":$adoName: :$SiteWideName: \n"; my $sql = "INSERT notifAlarmLog (name, AlarmStart, AlarmStop, UnixTi +meStart, UnixTimeStop, SiteWideName) values ('$recordA->[6]','$record +A->[2]', '$recordB->[2]', $recordA->[1], $recordB->[1], '$SiteWideNam +e')"; # print "$sql\n"; $dbAlarm->ct_sql($sql); return 0; } sub systemNames { # fill in the systemName hash use strict; my $dbAdo=new Sybase::CTlib 'harmless','harmless','OPSYB1','paramPro +pTable'; $dbAdo->ct_sql("use serverAdo"); my $sql = "SELECT name,systemName from adoInst where name like 'psWa +tch%'"; my(@rows,$row); @rows = $dbAdo->ct_sql($sql); foreach $row (@rows) { $main::systemNames{$row->[0]} = $row->[1]; # print "<$row->[0]> <$row->[1]> \n"; } } sub createDbTables { use strict; my $tblName = "notifAlarmLog"; print "Create table name $tblName\n"; my $sql = "CREATE TABLE $tblName( name varchar(65) not null, AlarmStart datetime not null, AlarmStop datetime not null, UnixTimeStart int not null, UnixTimeStop int not null, SiteWideName varchar(35) null )"; } sub help { # display help for this tool print "NotifAlarmTool- A program to do a variety of operations inte +racting with the Notif log. The first argument is a command verb. So +me commands require additional arguments. List of commands <buildHistory> makes history records from the alarm records. <createDbTables> Creates an alarm and an alarmHistory table in the TomTest database. Of course the old alarm and alarmH +istory records are gone. "; }
In reply to dropping or deleting tables by cjacksonjr
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |