March 2018

Database Task Automation for Asterisk (AsteriskNOW)

Andy Harris

Like most sales managers, our internal sales manager needed access to the Call Record Database of our AsteriskNOW implementation. This blog explains how we used our Privileged Task Management module to grant secure access, whilst protecting our privileged credentials.

The challenge

We had a business need for our internal sales manager to access the Call Record Database of our AsteriskNow implementation. The private branch exchange (PBX) is located on its own network with a dedicated internet connection, and is separated from our corporate network by two firewalls.

We wouldn’t expect our internal sales manager to understand the AsteriskCDRDB format or even the tools needed to access it. We also wouldn’t want to dish out the privileged credentials or allow web access to the PBX system.

Looking at the AsteriskNow implementation, it has a well-protected implementation of MySQL. By default, it is limited to access from the local host only. Given that PBX systems are prime hacking targets, it would be prudent not to change this access.

As the creators of the PxM Platform, we recognised that our Privileged Task Management module is ideal for this kind of task.

What we did

We wrote a short Python Program to access the AsteriskCDRDB. The key here is that we didn’t want to embed the credentials in this code, since if the system was compromised any attacker could modify this database to conceal calls made from our PBX.

Here’s the code fragment that we use to connect to MySQL:

parser = argparse.ArgumentParser(
usage=”asteriskquery.py –caller-id=<extension> –output-filename=output_filename>”</extension>
)
parser.add_argument(‘-ext’, ‘–caller-id’, dest=’target_ext’, type=str, default=None)
parser.add_argument(‘-name’, ‘–caller-name’, dest=’target_name’, type=str, default=None)
parser.add_argument(‘-file’, ‘–output-filename’, dest=’output_filename’, type=str, default=’calls.csv’)
parser.add_argument(‘-user’, ‘–db-username’, dest=’dbuser’, type=str, default=’dogfood’)
parser.add_argument(‘-d’, ‘–delta’, dest=’days’, type=int, default=7)
args = parser.parse_args()

if args.target_ext and args.target_name:
raise Exception(
“Error – both extention and name were supplied. Please supply one.”
)

if args.target_name:
# If a name is supplied, only fetch calls from
# the extension mapped to that name.
if args.target_name not in name_map:
raise Exception(
“Error – name {0} not found in valid names.”.format(args.target_name)
)
if name_map[args.target_name] not in extension_map:
raise Exception(
“Error – name {0} has no mapped extension.”.format(args.target_name)
)
target_ext = name_map[args.target_name]

elif args.target_ext:
if args.target_ext not in extension_map:
raise Exception(
“Error – extension {0} not found in valid extensions.”.format(args.target_ext)
)
target_ext = args.target_ext

else:
raise Exception(“Error – no name or extension supplied.”)

password = getpass.getpass(‘Password:’)
try:
db = MySQLdb.connect(
“localhost”,
args.dbuser,
password,
“asteriskcdrdb”
)
except Exception as e:
raise Exception(“Error – failed to connect to MySQL database:\n{0}”.format(e))

password = None

The key things to note is that we are using ‘getpass’ to ensure that the passwords are neither echoed or stored in command line history. The other notable technique is that password is set to ‘None’ as soon as the database connection has been tried. This means that it’s not hanging around in memory to be scraped.

Here’s the code that executes the query:

start, finish = time_delta(args.days)
sql = “SELECT * FROM cdr WHERE CALLDATE between ‘{0}’ and ‘{1}’ “.format(
start, finish
)
results = execute_query(db, sql)

‘execute_query()’ formats the data the way we need it.

On the PxM Platform, we have a task template that is used to drive the Python on the PBX system. The key lines are here:

<task display_name=”Get Call Stats” name=”get_call_stats” type=”status”>
<input display_name=”Caller Name” name=”caller_name” type=”string”/>
<input display_name=”Number of days” name=”delta” type=”string”/>
<input display_name=”Filename” name=”filename” type=”string”/>
<commands>
<command shellprompt=”.*Password:”>python asteriskquery.py –caller-id=%(caller_name)s –output-filename=&quot;%(filename)s&quot; –delta=%(delta)s –db-username=%(sql_username)s
<success type=”default”/>
</command>
<command response_timeout=”30″>%(sql_password.0.password)s
<failed type=”ci_in” value=”Error – failed to connect” message=”Gathering call stats failed. Please see log for details.”/>
<failed type=”ci_in” value=”Error – target extension” message=”Caller ID %(caller_id)s not recognised in the sales list.”/>
<success type=”ci_in” value=”csv file written successfully” message=”Call stats generated.” />
</command>
<command type=”scp” action=”retrieve”>
<local_filename>%(local_backup_location)s/%(device_name)s/call_stats_%(filename)s.csv>
<remote_filename>/root/%(filename)s>
</command>
</commands>
</task>

You can see now the inputs are marshalled, and how timeouts and error conditions are handled. These are seen from the application perspective since it should not be possible for the user to enter a wrong value. The SysAdmin could configure the wrong values and hence these paths would get triggered.

The other notable action is that the remote file is retrieved to the PxM Platform environment, where it is well secured. This means that there’s no GDPR actionable data hanging around in files on the PBX system.

Here’s the PxM Platform’s SysAdmin interface:

In this case “Caller Names” is a list of our sales staff mapped to extension numbers. The business users interface looks like this (we’ve omitted the dropdown list from the screenshot above for GDPR compliance):

Here’s the results page:

The ‘disk’ icon is a direct link to the download.

Using our analytics module, we can see that our PBX task is very popular:

If you’d like to try this against your own implementation of AsteriskNow, or any other Asterisk based PBX, check out our PxM Express product. A ‘no cost’ offering, PxM Express is a scaled down version of the full PxM Platform, and includes task functionality.

‍