Mitel/Shoretel Call Reporting

 

Installation docs

Overview

This product has several steps to the installation. The overall steps are:

  • Set user permissions in the Mitel/Shoretel Database
  • Download and install Splunk
  • Install perform the basic configuration of several apps
    • Sideview Utils
    • Mitel/Shoretel Call Reporting
    • DB Connect 3
  • Install the Java Runtime Environment and the MySQL driver
  • Configure the inputs via DB Connect 3
  • Index the data

1) Set user permissions in the Mitel/Shoretel Database

There are a variety of possible ways this could be configured, but what we need is a MySQL user that has permission to read all the tables in the DB ‘shorewarecdr’. If you have a preferred method of creating and granting access to a user for MySQL, please use it. Otherwise, this might help get you started:

  1. Open an “Administrator” command prompt on the Mitel/Shoretel server.
  2. Change the directory to the MySQL path, which defaults to C:\Program Files (x86)\Shoreline Communications\ShoreWare Server\MySQLCDR\MySQL Server\bin
  3. Launch MySQL (mysql.exe -u root -p). Enter the MySQL root password when asked.
  4. From here, to add a read only user called ‘splunk’, you would run the following command:
    mysql> GRANT SELECT ON shorewarecdr.* TO 'splunk'@'%' IDENTIFIED BY 'somePassword';

    NOTE: you should pick a real password of course, and if you prefer you can specify just the particular IP of the Splunk server instead of “%”.

You might have to open port 3306 in your local firewall – please see your system documentation on how to do so.


2) Download Splunk

Download Splunk and install it on its own server. Below are some links to help determine your hardware needs. If it’s just a demo you can put it on somewhat of a lesser machine, however be warned that Splunk will be much happier on a machine that meets their recommended hardware specs.

  1. Splunk’s System Requirements page
  2. Splunk’s Hardware Capacity Planning page.

If you have an existing Splunk server, please work with your Splunk admins to coordinate this installation.  Feel free to give us a call – or have them give us a call – to talk about what sort of impact it might have on their installation.

When you have a working copy of Splunk that you can log into and access, please continue.


3) Installing the apps

We will actually install three Splunk apps in total.

The Sideview Utils app and DB Connect will come from apps.splunk.com, and the Mitel/Shoretel Call Reporting app will come from the Sideview site.

Install Sideview Utils

  • Log into the Splunk UI as an admin user.
  • Click the “splunk>enterprise” logo in the upper left
  • Click the gears icon next to Apps
  • In the upper right, click Browse more apps.
  • In the resulting page, search for “sideview utils“.
  • When you find the app “Sideview Utils (free internal use license), click the button to install it.
  • Follow the prompts as required.
  • If Splunk Enterprise prompts you to restart, you do NOT need to do so!
    • There’s no harm in restarting, but we’ll have to restart later so there’s no need to do it now.

Installing the DBConnect 3 app

  • Log into the Splunk UI as an admin user, if you aren’t already logged in.
  • Click the “splunk>enterprise” logo in the upper left
  • Click the gears icon next to Apps
  • In the upper right, click Browse more apps.
  • In the resulting page, search for “DB  Connect”.
  • When you find it, click the button to install it.
  • Follow the prompts as required.
  • If Splunk Enterprise prompts you to restart, please do so at this time.
    • We will still have to restart one more time, but sometimes a restart here is necessary for DB Connect to work properly.

Download the Mitel/Shoretel Call Reporting app

You can download the 90 day trial version of the Mitel/Shoretel Call Reporting app from our site in much the same say as you did Sideview Utils.

  • Go to the app’s Download Trial page on our website
  • Read the form and fill it in as appropriate.
    • As always, we don’t sell your email or other information to anyone else.
  • After you click submit, you will be emailed a link to download the app.
    • If you don’t see the email within a few minutes, check your spam filter, try filling out the form again, and/or contact us. Among other ways we might help, we can manually send you your download link.
  • In the email, click the link to download the app.
  • Save that file somewhere that it will be easy to find in the next step.

Install Mitel/Shoretel Call Reporting app

  • Log into the Splunk UI as an admin user, if you aren’t already logged in.
  • Click the “splunk>enterprise” logo in the upper left
  • Click the gears icon next to Apps
  • In the upper right, click Install app from file.
  • Locate the downloaded file (shoretel.tar.gz) and click Upload.
  • If Splunk Enterprise prompts you to restart, you do NOT need to do so!
    • There’s no harm in restarting, but we’ll have to restart later so there’s no need to do it now.

Installing and setting up the Java Runtime environment (JRE)

As part of DB Connect, you will also need to set up a Java Runtime Environment (JRE) as per the first few links in the DB Connect app’s documentation: Splunk DB Connect Installation and setup overview.  Please follow the steps appropriate for your operating system, then return here.

Installing the correct MySQL Connector/J driver

That last thing you’ll need in order to connect it to your Mitel/Shoretel DB is the MySQL driver. The newest MySQL driver (8.0) does not necessarily work with the older, embedded version of MySQL that the Mitel/Shoretel DB uses, so follow these instructions to use version 5.1.47 (or the latest in the 5.1.x series):

  1. Click this link to open the MySQL Connector/J driver for versions 5.1.x
    • Confirm as below that you are downloading the Platform Independent version, are looking at the older releases, and can find the ZIP file.
  2. Download and uncompress the ZIP or TAR archive files.
  3. You will have to open a folder or two within the zip file until you find the mysql-connector-java-5.1.XX-bin.jar file, as in the below screenshot.
  4. Copy that mysql-connector-java-5.1.47-bin.jar file (or whatever 5.1.x version you have) and place it in the drivers  folder for DB Connect:
    • $SPLUNK_HOME/etc/apps/splunk_app_db_connect/drivers for Linux hosts
      • ($SPLUNK_HOME being shorthand for the Splunk installation directory, which is usually /opt/splunk)
    • %SPLUNK_HOME%\etc\apps\splunk_app_db_connect\drivers for Windows hosts
      • (%SPLUNK_HOME% being shorthand for the Splunk installation directory, which is usually C:\Program Files\Splunk\)
  5. Reload the driver under Settings>Drivers

Validation Steps

When finished with these steps, please confirm the following:

  • Selecting the Splunk DB Connect app shows no errors.
  • Clicking the Splunk DB Connect app’s Configuration > Settings > General shows a JRE installation path and has no errors.
  • Clicking the Splunk DB Connect app’s Configuration > Settings > Drivers shows a green check mark and “Yes” beside “MySQL”

Once you have a working copy of DB Connect, you should be able to proceed.


4) Entering your Mitel/Shoretel MySQL DB credentials and connection information

We have to now configure a Splunk DB Connect identity and connection. The identity is the username and password for the MySQL account. With this set, we then build a connection using that identity that includes the server information for your MySQL DB.

If you aren’t already there, go to the Splunk DB Connect app in your Splunk installation.

  1. Click Configuration > Databases > Identities.
  2. Click the New Identity button on the right.
  3. Enter the following information:
    • Identity Name: shoretel_identity
    • User Name: <fill in the username of the user you created above, like … > splunk
    • Password: <fill in the password of the user you created above, like … > somePassword
    • Leave the rest at their default and click Save
  4. Click Configuration > Databases > Connections.
  5. Click the New Connection button on the right.
  6. Enter the connection information as below:
    • Connection Name: shoretel_connection. (Use this exact name, this will be important later.)
    • Identity: select the one you just created, shoretel_identity.
    • Connection Type: to “MySQL”.  Be careful here, there are a lot of ones that start with “M”
    • Host: provide the Shoretel server’s IP address or hostname.
    • Default Database: shorewarecdr. (Make sure it is all in lowercase.)
    • Port: Unless the Shoretel DB was set up in a nonstandard way on a nonstandard port, leave at its default.
    • Leave the other fields at their defaults and click Save.
    • NOTE – if you get an error about timezones being unrecognized, there is a bug in a lot of versions of the JDBC driver for MySQL that require you to…
      • Click the checkbox to “Edit JDBC URL”
      • To the end of that line, add “?serverTimezone=GMT”
      • Try saving it again.

If you get any errors here, read them closely and correct as required. You can’t proceed with the rest of the instructions until this part works.


5) Indexing the Data

Restart Splunk at this time.  When all of the above steps are completed and then Splunk is finally restarted, the app should

  1. Check that the DB Connect connection works.
  2. Create checkpoints for your inputs.
  3. Enable the inputs, and
  4. Start indexing the data.

Splunk will now index all of the historical data available, and it will also index new data as it comes in.

Please note – this process can take a while if you have a lot of data, perhaps even up to half a day. This is largely due to it throttling itself to prevent impacting your Mitel/Shoretel system’s performance.

Validation Steps

After restarting and waiting for at least a couple of minutes, open the Splunk DB Connect app.

Click Data Lab > Inputs and confirm you see several inputs starting with shoretel_…

Click Health > DB Connect Input Health and confirm there are no significant errors and that most queries are completing.


6) Start playing around and creating reports and dashboards.

Log into the Splunk indexer using your browser, and navigate to the “Mitel/Shoretel Call Reporting” app.  You should see no error messages and you should be able to test drive the Browse, Report, and Call Detail pages. When you find a report or chart you like, choose “Create > Dashboard Panel” or “Save Report”. Explore the app. Contact us with any questions at all, or to set up a webex for some Q&A.