How to parse Avaya Communication Manager output into useful csv or database tables

Hello all!

This is a follow-up to my post about extracting data from Avaya Communication Manager. In that post, I showed you how to extract any “list”, “display”, or “status” from Avaya Communication Manager to a text file. First, I would like to show you my bash shell script that extracts this data. And then I’ll show you my perl script that converts the output to a text file that you can open with Excel or import into a database.

What information should you pull from Communication Manager? Well, in my case, I have a text file called ‘commands.txt’ that contains these lines:

display time
list ips
list surv
list med
status cdr
status trunk 1
status trunk 2
status trunk 8
status trunk 11
status trunk 16
status trunk 21
status trunk 79
list station
list reg
status station 2291
status station 2292
status station 2293
status station 2294
list call-forwarding
list off s
display time

So those are the various pieces of information that I care about. Looking at that info, those are my active trunk groups, and I want to know the status of four particular extensions (those happen to be the digital ports of my fax server). Also, I have a port network, so I want the status of IPSIs. Anyway, think about all the stuff you care about when you log into your PBX in the morning. And stick them in that “commands.txt” file. I put a “display time” at the beginning and end so I can look at the file later and tell how long it took to run the file.

So we want to run these commands automatically. So let’s create a file called “sanity.sh” and put this in the file:

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

Once you save the file, make it executable with

chmod +x sanity.sh

Let’s go over this script line-by-line, okay?

  1. The first line creates a variable for the file that will contain the data. There’s a little linux trick to embed a timestamp in the filename.
  2. The second line runs the file av.pl that I shared with you in a previous post about this script. It’s magical. I cannot take all the credit, but I have modified it for our purposes.
  3. The third line runs a file that parses the output of all the commands. This is a very useful script and I can take full credit for this one. It generates a list of “key/value” pairs to a text file.

My thought process with this script is to create a unique key for every piece of useful data. For example, I want the name, IP address, firmware version, gatekeeper, and network region for every station. I also want the EC500 mapping. And I would like the port number for analog stations. Oh! I also want a sense of low or high priority (Is it bad if the data changes?) and I need to include a site identifier in case I have the same extension in multiple sites. That’s a lot of information to display. And, if I have 1000 stations, it’s too much to display all this information on separate lines. That’s like 7000 lines of data for my stations. Fine for machines to read, but it seemed like too much for humans. I want to put them into one (and in the case of ec500, two) lines of data. I do this by assigning a “Key name” for my stations like this:

Key Name Key Value
lo.la.station.8348.offpbx EC500=>2135552978, OPS=>8348
lo.la.station.8348.status Anderson, Roger (ip=10.10.60.138;reg=3;ver=3.260A;gk=10.2.86.180)

See how much data I squeezed into that? And note that very few stations will have off-pbx information. Most stations will just have that station.status key. The script called sanity.pl takes the raw output from the Communication Manager and converts it to these key/value pairs above. Here is a link to sanity.pl. You’ll need to rename it to a ‘.pl’ file. This should compile just fine. When you copy it to your linux server, try

perl -c sanity.pl

That -c just means to check the syntax and don’t run it. It hopefully says “syntax okay”. Actually, I’m sorry but you’ll need to edit the file and change line 45 to the path where you want your ‘keyvalues.txt’ file, which will contain all your data. This should use a command line param, but it doesn’t. Sorry.

You might consider looking in more detail at this file. It’s where the magic happens and it’s a good framework for parsing other data. If you’re interested, here is a summary of how it works:

  • Starting in line 56, we check the raw text file line-by-line looking for the header for each ‘list’ or ‘display’ or ‘status’ section. For example, when we see the text ‘REGISTERED IP STATIONS’, then we know we are expecting to see the result of a ‘list reg’ (by setting our variable of type to 7).
  • Then on line 183 we actually process the registration status of stations. If type=7, then we are in the ‘list reg’ mode and if a line contains registration information for a station, then we capture the data.
  • Each section has some customization, but you’ll notice that (almost) each section performs a ‘set_data’ of the keys and values appropriate for that section.
  • At the end, the trunk group and station summary is generated and the keys are reported alphabetically.

Here are some examples of what the script does:

  • The script parses ‘list station’ and assumes all stations are unregistered.
  • The script parses ‘list reg’ and fills in the registration status for all stations (anything not parsed in this section has a status of ‘unregistered’)
  • The script parses ‘status trunk xxx’ and simply counts the trunks that are in service (ignoring the “idle/in use” values. We just count trunk members in service
  • The script parses ‘status cdr’ and simply stores the percent full of the buffers
  • The script parses ‘list off s’ and stores the off pbx mappings for all stations
  • The script parses various critical fields for IPSIs, media gateways, and survivable servers
  • The script parses ‘list call-forwarding’ and generates a list of all stations that are call forwarded.

At this point, if you ran the bash script, you would have an awesome list of keys and values in a text file called keyvalues.txt. You probably don’t need to know anything about Perl to get this working for you. But if you do know a bit of Perl, you’ll be able to do amazing things with it.

There’s more!

This data is useless unless you’re looking at it. In a future post, I will show you how to schedule this script with cron, push the data into a database, read it with a simple ‘web site in a single file’ php script, filter the data (including historical values!), and get alerts when the values change.

And then I have something REALLY amazing to show you!

Thanks for reading, everyone! Feel free to contact me at roger@rogerthephoneguy.com or post a comment here. If you need help getting this code working, or you want to tweak it a bit, let me know!

Roger

12 thoughts on “How to parse Avaya Communication Manager output into useful csv or database tables

  1. Ed Price

    Did you ever post the conclusion of exporting the CM info into a web page? Curious how that turned out.

    Am looking to see if I can get complete station information on a live feed, possibly using LDAP. Am looking to integrate E911 to automate the collection. I am currently using expect to do a list station, and clean up all the data, but if there was a less cpu intensive way to do this, I would be very interested. Currently, we have approximately 11 cores with 50000 stations, so you can imaging the load on the processors if we do this every hour.

    Thanks.

    Reply
  2. Brett

    Found a few things were not working on CM 6.3, the survivable processors was not lining up. You had some code that grabbed the right fields commented out but I see you had issues with it locking up on the IP line. What I did was add a $line = trim($line); to the line immediately after elsif($type == 3). It allowed the regex to work in your if statement. This cleared the large amount of white space that was choking the regex.

    Additionally I added code to track active trunk members as well. If you want me to send you the modified version email me Roger.

    Reply
    1. roger Post author

      Wow, thanks Brett!

      You have plucked the pebble from my hand. If you’d like to email me the code at roger@rogerthephoneguy.com, I’ll post a revision if you don’t mind. I’ll credit you. This might turn out to be a poor-man’s open-source project.

      Anyone want Brett to parse anything else for us?

      Thanks!
      Roger

      Reply
  3. Tom Lynn

    Roger, have you noticed that if you provide invalid commands in your command file, the script returns 0? This may be useful for your readers to know. I think it provides a 0 for each invalid command that it attempts.

    Reply
  4. Steve G

    Hi Roger,

    I’m loving the potential of this information, I can use this info to gather a lot of info from CM but we have SIP endpoints and CM is not a great source of information for SIP endpoints. Is it possible to complete any of these queries on Avaya system manager?

    Many thanks,

    Steve

    Reply
  5. SC

    Hi Roger, thanks for sharing the solution. I was reading your the post when I am looking for solution to speed up the migration of configuration in old to new PBX, says station, queue/skill, etc. Going to tweak the code to try that and hopefully it works.

    Thank you so much!

    Reply

Leave a Reply

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