How to Parse Avaya Communication Manager data to a database for analysis – Part 3

Hello all!

This is a continuation of our discussion about pushing Avaya Communication Manager data into a database so you can analyze the results.

Here is part 1 where we connect to the Communication Manager through a perl script

Here is part 2 where we capture a bunch of data and parse it into useful tables

In this part, we push the data into a MySql database. The first step is to create the database. I typically use my own Linux workstation. You can ask your VM team for one. They take almost no resources and they’re open-source, so there are no licensing concerns. Therefore I typically have complete control of the repositories, database, and Apache instance. If you do not have the root password to your Linux workstation, then you might need help from your team with some of these steps.

We need to start with some assumptions here. So let’s assume you have followed parts 1 and 2 of this series and you have MySQL installed, okay?

Step 1 – create the database

Log into MySQL. These commands will create the database and user for this project. This user is called ‘scraper’ with a password of ‘crafty1876’

mysql> create database avaya;
Query OK, 1 row affected (0.01 sec)

mysql> use avaya;
Database changed

mysql>create user scraper@localhost identified by 'crafty1876';
Query OK, 0 rows affected (0.04 sec)

mysql>grant all on avaya.* to scraper@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql>exit

We exit the database and log back in with our new user. This ensures that the user works and has full access to the avaya database

[roger ~]# mysql -hlocalhost -uscraper -pcrafty1876 avaya

Now let’s create the tables we need. I always have a “drop table if exists” statement so I can tweak the table as much as I want and re-create it. This creates a keyvalues table and a display name table.

mysql> drop table if exists avaya_keyvalues;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql]> create table avaya_keyvalues (
 id integer auto_increment primary key,
 active integer,
 key_name varchar(256),
 key_value varchar(256),
 record_status integer,
 created_on datetime,
 updated_on datetime,
 deactivated_on datetime
);
Query OK, 0 rows affected (0.11 sec)

mysql>drop table if exists avaya_display_name;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql>create table avaya_display_name (
 station varchar(12) NOT NULL,
 name varchar(32),
 PRIMARY KEY (station)
);
Query OK, 0 rows affected (0.09 sec)

mysql>

Now we have a database and the empty tables we need. Since you have read part 1 and part 2 of this process, you should have a perl script generating your ‘keyvalue.txt’ file.

Step 2 – Push the keys and values into the database

We have a new perl script that takes the file of keys and values and puts it into the database. You can click here for a download the script. Just rename it to .pl. Here it is so you can see it. It is called keys2db.pl:

#!/usr/bin/perl

#provided as-is by in 2017 by RogerThePhoneGuy.com 
#This script reads a file of "key/value" pairs, and logs them to a database. If the value was changed or deleted, it logs that too
#meant to be used with keyval.php, which shows this data
#assumes there's a local mysql connection

use strict;
use DBI;

my $db;

sub db_open { 
	my $dsn = "DBI:mysql:avaya:localhost";
	my $user = "scraper";
	my $pass = "crafty1876";
	while (!($db)) {
		$db = DBI->connect($dsn, $user, $pass);
		sleep 1;
	}
}

sub db_close { 
	if($db) {
		$db->disconnect();
		sleep 1;
	}
	undef($db);
}

sub generic_replace { 
	my ($table, $fields, $values) = (@_);
	my $sql = "replace into $table ($fields) values ($values)";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to replace with $sql\n";
	}
	$sth->finish();
}

sub generic_create { 
	my ($table, $fields, $values) = (@_);
	my $sql = "insert into $table ($fields) values ($values)";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to insert with $sql\n";
	}
	$sth->finish();
}

sub generic_read {
	my ($select, $table, $where) = (@_);
	my @datarow;
	my $sql = "select $select from $table where $where limit 1";
	my $sth = $db->prepare($sql);
	if($sth->execute()) {
		@datarow = $sth->fetchrow_array();
	} else {
		print "unable to execute $sql\n";
	}
	$sth->finish();
	return $datarow[0];
}

sub generic_update {
	my ($table, $set, $where) = (@_);
	my $sql = "update $table set $set where $where";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to update with $sql";
	}
	$sth->finish();
}

sub generic_delete {
	my ($table, $where) = (@_);
	my $sql = "delete from $table where $where";
	my $sth = $db->prepare($sql);
	if(!($sth->execute())) {
		print "WARNING - unable to update with $sql";
	}
	$sth->finish();
}

db_open();

my $counter = 0;
generic_update("avaya_keyvalues","record_status=9","active=1");
open SCRAPED, $ARGV[0];
while (my $line = ) {
	chomp($line);

	my ($key,$value) = split(/\t/,$line);
	my $id = generic_read("id", "avaya_keyvalues","active=1 and key_name='$key'") + 0;
	my $currvalue = generic_read("key_value", "avaya_keyvalues","active=1 and key_name='$key'");
	if($id > 0) {
		if($value ne $currvalue) {
			$value =~ s/'/\\'/g;
			generic_update("avaya_keyvalues","active=0,record_status=2,updated_on=now()","id=$id");
			my $fields = "id,active,key_name,key_value,record_status,created_on,updated_on,deactivated_on";
			my $values = "null,1,'$key','$value',1,now(),now(),null";
			generic_create("avaya_keyvalues", $fields, $values);	
		} else {
			generic_update("avaya_keyvalues","active=1,record_status=1","id=$id");
		}
	} else {
		$value =~ s/'/\\'/g;
		my $fields = "id,active,key_name,key_value,record_status,created_on,updated_on,deactivated_on";
		my $values = "null,1,'$key','$value',1,now(),now(),null";
		generic_create("avaya_keyvalues", $fields, $values);
	}
	if($key =~ /\.station\.(\d+)\.status/) {
		my $station = $1;
		my $name = '';
		if($value =~ /^(.+?) \(ip=/) {
			$name = $1;
		} elsif($value =~ /^(.+) \(unregistered/) {
			$name = $1;
		}
		$name =~ s/'/\\'/g;
		generic_replace("avaya_display_name","station,name","'$station','$name'");
	}
}
close SCRAPED;
generic_update("avaya_keyvalues","active=0,record_status=3,deactivated_on=now()","record_status=9");

my $create = "
drop table if exists avaya_keyvalues;
create table avaya_keyvalues (
	id integer auto_increment primary key,
	active integer,
	key_name varchar(256),
	key_value varchar(256),
	record_status integer,
	created_on datetime,
	updated_on datetime,
	deactivated_on datetime
);
";

#my @count = generic_read("count(*)","callrecord","1=1");
#print "count is " . $count[0] . "\n";
db_close();

This script is really basic. It just does these things:

  1. Line 88, It marks all records in the database as ‘old’
  2. Starting in line 90, It cycles through the keyvalues file and checks for records where the value has changed (line 97). It marks these as ‘changed’ and then inserts the new record. It marks the unchanged records as ‘unchanged’ (line 104).
  3. If the current value is not in the database, it inserts it (line 110)
  4. It also replaces the value of the display name in case we want to see all extensions and names later (line 121)
  5. Lastly, any record that was not touched through this loop is NOT in the text file. Does that make sense? So any record that is still marked ‘old’ in step 1 must have been deleted from the Avaya system. So we mark these records as ‘deleted’ (line 125).

When this script is done, we have a list of all active records in the database. We also have a list of old values and deleted values. This gives you a rolling history of all extensions in your database. I cannot tell you how handy it is to see a history of an extension. You can see when the IP address changes, when registration changes, when firmware changes, network region, etc. And of course when a station is deleted you still have a record of it. This is very handy.

For those of you interested in Perl, there are also a bunch of helpful database functions in here. I keep these in my back pocket and often copy/paste them to the next project.

To tie this new perl script into our ‘sanity.sh’ script from last time, we should now have this:

file=/home/roger/avaya/sanity/data/sanitycheck_`date +"{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}Y-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}m-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}d-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}H-{0ed28e3470e974017c124b0897303dd14e34b5245564abb28916e7d48d9b07c0}M"`.txt
perl /home/roger/avaya/sanity/av.pl /home/ARES/randerson/avaya/sanity/la.pbx /home/roger/avaya/sanity/commands.txt >$file
perl /home/roger/avaya/sanity/sanity.pl $file > /home/roger/avaya/sanity/keyvalues.txt
perl /home/roger/avaya/sanity/keys2db.pl /home/roger/avaya/sanity/keyvalues.txt

At the end of this shell script, you should have a fresh copy of all stations in your PBX in the database! Ready for reading with a simple PHP script. That will be next time, and hopefully MUCH sooner than this post. Sorry for the delay.

Before I finish, I want to thank you for reading this. I recently “moved” from an Avaya environment to a Cisco environment, so I don’t have easy access to the Avaya environment anymore. I am using some copies of the scripts so if you find any syntax errors or something doesn’t work, please let me know and we’ll get it working.

Once I finish this series, I’ll show you a way to AUTOMATICALLY generate labels for the 96xx phones. That’s the most exciting thing I’ve been wanting to share with you!

Roger

 

13 thoughts on “How to Parse Avaya Communication Manager data to a database for analysis – Part 3

  1. Nick

    Hi Roger
    Playing around with your keys2db script. All runs and I have an empty database.
    looking at the script you are opening a file named SCRAPED, are we not wanting to open the keyvalues.txt file?
    my $counter = 0;
    generic_update(“avaya_keyvalues”,”record_status=9″,”active=1″);
    open SCRAPED, $ARGV[0];
    while (my $line = ) {
    chomp($line);
    I modified the script to read keyvalues but still empty database?
    Am I barking up the wrong tree!

    Reply
    1. roger Post author

      Hey Nick. It should open the file that is passed at the command line. You can add a “print $line;” right before the chomp($line) to make sure the script is reading the file. When you run the program with

      perl keys2db.pl mykeyfile.txt

      it should load mykeyfile.txt for example.

      Can you let me know if you get any output after adding the ‘print $line’ statement?

      Reply
      1. Nick Forster

        Hi Roger
        All sorted now, my mistake i did not correct the path correctly in my script.
        Now fixing the issue of invalid characters in peoples name that SQL won’t take, especially the apostrophe which i am replacing with a space
        Thanks
        Nick

        Reply
  2. Jonathan

    Thank you for sharing this script; it is wonderful! Love it.
    Question if I may: In Avaya Site Administrator when one sets up a scheduled task, there is the option to ‘not check’ “disconnect from the system after task has been processed”. With this option not-checked (the connection is maintained), a ‘list history’ will not show the repeated logn/logf in the log. Leveraging your script, however, there are multiple entries in the list history for the account I use. I can’t imagine there is a way to “not disconnect” within the scripting. However, I was wondering if there is a way to suppress this logging in list history.

    Reply
    1. roger Post author

      Ugh – sorry about that logging. I don’t know of a way to do it but I did have a tech tell me that it’s possible to suppress logging.

      Another option is to have the perl script “list hist” and then push it to the database. Then filter out the username when displaying it. Who looks at that log? Just you?

      Reply
  3. Nick

    Roger: Amazing work! I’m trying to implement this in my lab, but as a Business Partner I will find this VERY VERY useful, so I cannot thank you enough for your work! I did notice an error when running the script to add to the db in my environment. I’m getting an error because I have some stations with apostrophe’s in them. I edited the station names to remove them for my testing, but wasn’t sure if you were aware of this, and thought I would bring it up. Thanks again for all your hard work and I look forward to more updates 😀

    Reply
    1. roger Post author

      Oops. If it’s not in my script, it should be. I am always careful to escape single quotes. With php, it’s just

      $text=addslashes($text);

      and with perl it’s

      $text =~ s/’/\\’/g;

      Did I forget it somewhere?

      Reply
    1. roger Post author

      Yes! Thanks for the friendly encouragement. I should be able to post the php file soon. I’m really struggling with my access to the old Avaya environment. I recently migrated to a Cisco PBX and, while I have access to the old Avaya, I do not work with it much anymore so I cannot easily test the scripts that I post here. Anyway, I will get it to you soon!

      Roger

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *