How to connect to Shoretel mySQL Configuration Database – Version 10+

As Shoretel versions change the methods for connecting to the configuration database change as well.  On versions 10 and greater,  here is how you can access MySQL and create a read-only user that can be used for SELECT queries in the database.

            Shoretel Configuration Database: shoreware
            Default Username: root
            Default Password: <check with your Shoretel partner>

Connect to mySQL

  1. Open a command prompt.
  2. CD to mySQL Path.
  3. Launch mySQL.
C:\Program Files (x86)\Shoreline Communications\ShoreWare Server\MySQL\MySQL Server 5.0\bin>
mysql -u root -p --port=4308 -D shoreware
Enter password: ************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 284406
Server version: 5.0.68-br30478-enterprise-nt-br30478-log MySQL Enterprise Server (Commercial) [-br30
478]
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show tables;

 

See older post on how to connect to Shoretel MySQL Version 8.

Add comic bubbles to pictures with addcap.py.

I wrote addcap.py to be able to add comic bubbles and captions to photos.

get it on github.

Images have (x,y) pixel coordinates like quadrant 4 in the Cartesian plane. You don’t need to know that. The upper left corner is (0,0). Moving to the right increases x. Moving down increases y.

On an image with dimensions 800×600 the middle point is (400, 300).

Addcap will draw a caption bubble on your image but you have to tell it where. It needs a starting point, and for you to tell it which corner that point is. Addcap will always draw down and to the right. If you tell it the lower right corner coordinate(LOWER_RIGHT), then it will figure out where upper left is and draw from there.

You can pass all kinds of other options too. Check them out with -h.

python addcap.py -h
usage: addcap.py [-h] [-i IMG] [-sx STARTX] [-sy STARTY] [-tw TEXTWRAPWIDTH]
                 [-fs FONTSIZE] [-cc CHARACTERCOUNT] [-sc STARTINGCORNER]
                 [-ca CALLOUTARROW] [-f FOLDER] [-o OUTFILE] [-qf QUOTEFILE]
                 [-q QUOTE [QUOTE ...]]

optional arguments:
  -h, --help            show this help message and exit
  -i IMG, --img IMG     Image to add a caption to.
  -sx STARTX, --startX STARTX
                        Starting X Coordinate for caption bubble.
  -sy STARTY, --startY STARTY
                        Starting Y Coordinate for caption bubble.
  -tw TEXTWRAPWIDTH, --textWrapWidth TEXTWRAPWIDTH
                        How many characters before a line wrap in the bubble?
  -fs FONTSIZE, --fontSize FONTSIZE
                        Font size
  -cc CHARACTERCOUNT, --characterCount CHARACTERCOUNT
                        Character count. limited to 140 by default.
  -sc STARTINGCORNER, --startingCorner STARTINGCORNER
                        The corner that your start coordinates create. Values
                        are Values are UPPER_LEFT, LOWER_LEFT, LOWER_RIGHT,
                        UPPER_RIGHT
  -ca CALLOUTARROW, --calloutArrow CALLOUTARROW
                        Which call out arrow do you want, starting at
                        11oclock, going counter clockwise. Just put the
                        number, like 6
  -f FOLDER, --folder FOLDER
                        If defined, create a folder and store addcap images
                        there. Otherwise defaults to current folder.
  -o OUTFILE, --outfile OUTFILE
                        The outfile prefix name. Will append a number and JPG
                        to the end.
  -qf QUOTEFILE, --quoteFile QUOTEFILE
                        A file with 1 quote per line. Quotes should not exceed
                        140 characters.
  -q QUOTE [QUOTE ...], --quote QUOTE [QUOTE ...]
                        One or more quotes in the form "This is a quote.",
                        "This is another quote", "This is yet another quote".
                        Individual quotes should not exceed 140 characters.

If you run demo.sh, then addcap will create a demo folder for you and fill it up with examples.

 

ATTRIBUTIONS!

All images pulled from FLICKR searching on ‘bad taxidermy’ where the CC flag was set to yes.
(C)Attribution license
(C)Attribution license
(C)Attribution license
pugnax” by  istolethetv
(C)Attribution license
bad taxidermy”  by avidd
(C)Attribution License
crazy taxidermy” by Ed Schipul
(C)Attribution Share-Alike License

Change OS X default screenshot destination from the command line easily with grabsy.sh

I wrote this script to easily modify the save-to destination for the default screen grab functionality in OS X. (Cmd+shift+4).

Link to grabsy on GitHub.

Run sh grabsy.sh FOLDERNAME and then all screenshots will go into that folder. It’s great for keeping things neat when writing documentation.

Run it again with no arguments to restore the default destination. This will restore it to ~/Documents/Screenshots. That’s my default. Yours is probably ~/Desktop. You can edit the script to do that. It’s already in there. Just commented out.

The read me in the github page is the definitive explanation.

Derived from the link http://lifehacker.com/quickly-change-os-xs-default-screenshot-format-and-loc-1489014578

Fake plankton wallpaper pattern.

Plankton Pattern

Plankton Pattern

 

Drawn with Sharpie No Bleed Fine and Sharpie No Bleed Medium pens.

Drawn on Strathmore Drawing Premium Recycled 18″ x 24″ 80lb stock.

Troubleshooting Shoretel Switch Communication across a Site to Site VPN with Cisco ASA’s – Fragmentation and MTU size.

Scenario:  Shoretel IP Phone system deployed across multiple sites.  One remote site connects into HQ with a Site-to-Site VPN over the internet using Cisco ASAs as the termination points.  The HQ site can call the remote site using extension dialing.  Calls from the remote site to HQ fail and hit the failover DID of the user or receive the ‘Extension cannot be reached at this time’ backup auto attendant message.  THIS IS NOT A ONE WAY AUDIO ISSUE.  Audio works in both directions if the call sets up and is established.  This scenario is a failure of the call to set up even though IP connectivity *appears* to be there.

Topology

Shoretel Topology Scenario

Shoretel Topology

 

Running basic ICMP and LSP_pings prove that full connectivity exists between the two switches (10.10.10.11 and 10.10.20.11).  Since these work, I need to look deeper at the packet flow to get a sense of what is going on. I used two ways to do this – using etherMon on the Shoretel switches and ‘capture‘ filters on the ASA’s. 

Running etherMon from a Shoretel Switch.

etherMon allows you to do a full packet capture on Shoretel switches.  This blog post details it very cleanly.  http://netdungeon.com/packet-capture-shoretel-shoregear-switches

In the .pcap files from running etherMon while making test calls, I could see packets of MTU 1514 bytes leaving the Shoretel switch in the remote site, but never arriving in the HQ site.  I could see all the other UDP traffic hitting the HQ side.  I don’t have a cleaned up screenshot of these .pcaps.

Looking back to the network.

This pointed me away from Shoretel configuration and to look at the ASA’s and verify the MTU / Fragmentation settings.  Per Shoretel KB14603:

ShoreTel versions 7.5 and below had a maximum transmission unit of 1380 bytes.  Versions 8 and above increased the size of the MTU to 1472 for ShoreSIP messages.  Larger MTU sizes were needed to accommodate new features in the ShoreTel switches. One important consideration when deploying an IPsec VPN is how to deal with maximum transmission unit (MTU) and fragmentation issues caused by large IPsec packets. IPsec adds considerable overhead to the IP packets that it encapsulates which can cause fragmentation and dropped packets in the network.  Most IPSEC VPN devices can be set to allow fragmentation and reassembly in the network.

 I confirmed the MTU on both firewalls was set to 1500.

REMOTE-ASA# show run mtu
mtu inside 1500
mtu outside 1500
HQ-ASA# show run mtu
mtu inside 1500
mtu outside 1500

So, if Shoretel needs 1472 and IPSEC has a ~46 byte overhead, that means I need to be able to pass 1472+46 = 1518 bytes.  I cant fit 1518 bytes into a 1500 byte packet, so I’ll have to fragment the packets.

Per the KB, Shoretel creates packets with the ‘Do Not Fragment’ bit.  We can override this in the firewalls.

crypto ipsec df-bit clear-df inside
crypto ipsec df-bit clear-df outside

This should allow the Shoretel packets to be fragmented.  Let’s set up some packet captures in the ASA’s and make some test calls!

Capturing Packets on the ASAs

First we need to define some interesting traffic.  I want to see all TCP/UDP packets into and out of the SG switch at 10.10.20.11 heading to the HQ location.  I add this ACL to both firewalls.

access-list calls extended permit tcp 10.10.10.0 255.255.255.0 host 10.10.20.11
access-list calls extended permit udp 10.10.10.0 255.255.255.0 host 10.10.20.11
access-list calls extended permit tcp host 10.10.20.11 10.10.10.0 255.255.255.0
access-list calls extended permit udp host 10.10.20.11 10.10.10.0 255.255.255.0

Now we build the packet capture on that ACL on both firewalls.

 capture capture_call access-list calls interface inside

Then make the test call from REMOTE to HQ. Review the results on each firewall.

show capture capture_call detail

I got the same results here. I could see the traffic leaving the REMOTE site, but never arriving on the HQ site.  I needed better captures and ACLs.  I need to see something more about WHY these packets are being dropped and WHERE.

no capture capture_call
no access-list calls extended permit tcp 10.10.10.0 255.255.255.0 host 10.10.20.11
no access-list calls extended permit udp 10.10.10.0 255.255.255.0 host 10.10.20.11
no access-list calls extended permit tcp host 10.10.20.11 10.10.10.0 255.255.255.0
no access-list calls extended permit udp host 10.10.20.11 10.10.10.0 255.255.255.0

You can use ASP to get some more detail on what’s going on with packets traversing your ASA.  More here (http://www.fir3net.com/Cisco-ASA/what-is-asp-and-how-to-troubleshoot-asp-drops-on-an-asa.html)

capture DROPS type asp-drop all
show cap DROPS
no cap DROPS !to get rid of it.

This gave alot of information but nothing particularly helpful to this situation that I saw. I really needed to get a capture on the inside and outside interface at the same time, even if the outside interface is encrypted via IPSEC.

!This one works fine for the calls on each firewall LAN side.
access-list calls extended permit tcp 10.10.10.0 255.255.255.0 host 10.10.20.11
access-list calls extended permit udp 10.10.10.0 255.255.255.0 host 10.10.20.11
access-list calls extended permit tcp host 10.10.20.11 10.10.10.0 255.255.255.0
access-list calls extended permit udp host 10.10.20.11 10.10.10.0 255.255.255.0
!This one should get all traffic from each firewall to the other on the outside interface.
access-list peer extended permit tcp host 2.2.2.2 host 1.1.1.1
access-list peer extended permit tcp host 1.1.1.1 host 2.2.2.2
access-list peer extended permit udp host 2.2.2.2 host 1.1.1.1
access-list peer extended permit udp host 1.1.1.1 host 2.2.2.2
access-list peer extended permit ip host 2.2.2.2 host 1.1.1.1
access-list peer extended permit ip host 1.1.1.1 host 2.2.2.2
!Then we build the inside and outside captures. (structure found here (http://isamology.blogspot.com/2009/12/asapix-packet-capture-feature.html))
capture pi interface inside access-list calls circular-buffer buffer 10000000
capture po interface outside access-list peer circular-buffer buffer 10000000

From here, we should be good to make a test call and check the caps.

show capture pi
show capture po

This is it! I can see the large packets leaving the REMOTE-INSIDE, REMOTE-OUTSIDE, hitting the HQ-OUTSIDE and THEN JUST DISAPPEARING!

At that point, I knew it wasn’t a carrier issue and was something in the firewall – either a bug or configuration issue.  I pored through line after line of code in the ASA until I hit a few lines I didn’t recognize and didn’t add in myself.

fragment chain 1 outside

Huh, that sounds interesting.  Fragment.  Quick search yields this. ASA Packet Captures

By default, the ASA allows up to 24 fragments per IP packet, and up to 200 fragments awaiting reassembly. . .To set disallow fragments, enter the following command:
hostname(config)# fragment chain 1 [interface_name]

Sounds EXACTLY like the problem we’re seeing, right.  I ran a quick ‘ no fragment chain 1 outside‘ and now calls work just fine in both directions!

This was a tough one, with a ton of packet tracing on numerous devices to confirm settings and what what actually traversing the networks.  I’ll know to look for this first in the future.  You can check quickly now by doing a ‘show run | inc fragment’.  Good luck!

TLDR; The Cisco ASA has a command called ‘ fragment chain 1 outside’.  If you see this, any fragmented packets will be discarded.  Remove it with ‘no fragment chain 1 outside’.


this is an owl.

this is an owl.

this is an owl.

 

Drawn with Sharpie No Bleed Fine and Sharpie No Bleed Medium pens.

Drawn on Strathmore Drawing Premium Recycled 18″ x 24″ 80lb stock.

PRTG Alarm Light with Arduino

A friend asked if there was a way to trigger an alarm light in his data center when PRTG detected an alert on monitored devices.  I couldn’t find an off-the-shelf solution so I decided to make one using the following approach.  It works.  I don’t know if it’s the best or if it’s even good.

In the production environment, I plan to set up a lightweight PC to run the processing.org sketch, then connect the alert light box to the USB port for power and instructions.

 PRTG Alarm Light
PRTG Alarm Light with Processing.org and Arduino

 

 

Things you need:

PRTG v 14: Great application from Paessler to monitor and alert on hardware, network, or application performance.  I really like this product.  Their API is straightforward, easy to consume, and easy to make actionable. Download PRTG and  Read up on their HTTP API.  In PRTG, Create an APIUSER account – name/pw of your choosing – whatever you pick, update it in ‘settings.conf’.

processing.org: Java based programming language to query the PRTG API, then command the Adruino to do something.  In this case, trigger an RGB LED in specific patterns and colors.  Download processing.org.

Arduino: Programmable circuit board. Download the Arduino programming IDE.  Download the Firmata Sketch for the Arduino. Load the Firmata sketch to the Arduino.  All other programming is done in processing.org.  I put the board into this box made for Arduino.  I also dismantled an Energizer LED Touch Light for the housing / diffuser.  Is that called a diffuser?

RGB LED (I used common cathode): This is the thing that lights up in a variety of colors and blinks.

Simple pinout for arduino and rgb LED

Simple pinout for arduino and rgb LED

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here are some pics.(Key for scale.)

PRTG Alarm Light

PRTG Alarm Light

PRTG Alarm Light
PRTG Alarm Light

Steps of the program:

1) Load settings.conf configuration file via loadJSON method.

Sample ‘settings.conf’

{
 "friendlyDelayInterval": 1,
 "HttpApiUserName": "prtgadmin",
 "HttpApiPassword": "prtgadmin",
 "HttpProtocol": "HTTP://",
 "HttpSiteToCheck": "IP_ADDRESS_OR_HOSTNAME", 
 "HttpSiteToCheckPortNumber": "", 
 "HttpApiSensorDownString": "/api/table.json?content=sensors&columns=objid,downtimesince,device,sensor,lastvalue,status,message,priority&filter_status=5&filter_status=4&filter_status=10&filter_status=13&filter_status=14&sortby=priority",
 "Sound_CriticalAlert": "acheeve.mp3",
 "Sound_AllClear": "ding.mp3",
}

2) Confirm that the PRTG server/API is reachable.

3) Consume the PRTG API in JSON format.

4) Interpret the results of the PRTG API.

5) Trigger the appropriate light pattern based on JSON results.

6) Loop.

 

HERE’s THE CODE!

PRTG_Alert_Light processing.org sketch.

 

What do you need to do?

  1. Download my processing.org sketch.
  2. Push the Firmata sketch to your Arduino.
  3. Update the ‘settings.conf’ with your particulars.
  4. Find some fun wav/mp3 files for audio alerts and put them in the same sketch folder.
  5. Connect the RGB LED to your Arduino.
  6. Find a cool housing to put it in.
  7. Extend the code to do other things.  The PRTG HTTP API is very flexible.  Let me know what you end up doing with it.

A quick look at the PRTG API

Paessler has made one of the most inviting Network Performance Monitoring tools I have used, PRTG.  Their API is also designed very well, and it’s nice that it’s self-documenting.

To get started with the API, you can navigate to your installed instance of PRTG (https://<servername>/api.htm).  If you don’t yet have it installed, you can check out Paessler’s public demo site which grants API access.

In this post, we are going to focus on filtering results based on how your HTTP request string is formatted.  From their example in their documentation, here is an HTTP query that will pull all ‘sensors that are not up (based on filter status codes) with their current state and downtime information (based on what columns you include in the query string)’.

https://<servername>/api/table.xml?content=sensors&columns=objid,downtimesince,device,sensor,lastvalue,status,message,priority&filter_status=5&filter_status=4&filter_status=10&filter_status=13&filter_status=14&sortby=priority

The current filter status codes are:

  1. Unknown
  2. Collecting
  3. Up
  4. Warning
  5. Down
  6. NoProbe
  7. PausedbyUser
  8. PausedbyDependency
  9. PausedbySchedule
  10. Unusual
  11. PausedbyLicense
  12. PausedUntil
  13. DownAcknowledged
  14. DownPartial

You can select the format of your return values as well.  The query listed above will return xml formatted results.


https://<servername>/api/table.xml?content=sensors&columns=objid,downtimesince,device,sensor,lastvalue,status,message,priority&filter_status=5&filter_status=4&filter_status=10&filter_status=13&filter_status=14&sortby=priority

 <sensors>
 <prtg-version>14.1.8.1370</prtg-version>
 <item>
 <objid>2031</objid>
 <downtimesince/>
 <device> IP_ADDRESS_REDACTED (BLANKNAME)</device>
 <sensor>(003) LAN (LAN)</sensor>
 <lastvalue>1,106 kbit/s</lastvalue>
 <lastvalue_raw>138260.0833</lastvalue_raw>
 <status>Unusual</status>
 <status_raw>10</status_raw>
 <message>
 <div>1 day interval average of 60 kbit/s (Traffic In) is unusually low for this weekday<div></div></div>
 </message>
 <message_raw>
 1 day interval average of 60 kbit/s (Traffic In) is unusually low for this weekday
 </message_raw>
 <priority>3</priority>
 </item>
 </sensors>


https://<servername>/api/table.json?content=sensors&columns=objid,downtimesince,device,sensor,lastvalue,status,message,priority&filter_status=5&filter_status=4&filter_status=10&filter_status=13&filter_status=14&sortby=priority

 {
 "prtg-version":"14.1.8.1370",
 "treesize":1,
 "sensors":[
 {
 "objid":2031,
 "downtimesince":"",
 "downtimesince_raw":"",
 "device":"IP_ADDRESS_REDACTED (BLANKNAME)",
 "sensor":"(003) LAN (LAN)",
 "lastvalue":"1,088 kbit/s",
 "lastvalue_raw":135970.3333,
 "status":"Unusual",
 "status_raw":10,
 "message":"<div class="status">1 day interval average of 60 kbit/s (Traffic In) is unusually low for this weekday<div class="moreicon"></div></div>",
 "message_raw":"1 day interval average of 60 kbit/s (Traffic In) is unusually low for this weekday",
 "priority":3
 }
 ]
 }

This is a very basic example of what you can do.  In my case, I wanted to trigger an alarm light when there were ‘down’ sensors.  I preferred to work with JSON strings since the built in processing.org JSON library was pretty easy to use.  There’s alot of strength and flexibility in this API though – you can craft these HTTP queries to do quite a bit.  This example focuses on ‘read only’.  They offer the ability to ‘rename’ sensors via HTTP API, but I’ve not had a need for that yet.

 

Shattered 1

shattered 1

shattered 1

 

 

Drawn with Sharpie No Bleed Fine and Sharpie No Bleed Medium pens.

Drawn on Strathmore Drawing Premium Recycled 18″ x 24″ 80lb stock.

Shattered 2

shattered 2

shattered 2

Drawn with Sharpie No Bleed Fine and Sharpie No Bleed Medium pens.

Drawn on regular old 8.x5″ x 11″ white printer paper.

Next Page »