Getting Incident Email Addresses Programmatically from the Axosoft API

We use Axosoft for our Support Ticket management for over 10 years now. Although we had been diligently filling in the appropriate Project with each ticket, we had not been keeping up with our Contacts. Well, I got a request from management they wanted a break down of our support tickets based on the requestor.

In the main Help Desk grid (which you can export), I could display a “Email (Customer Contact)” field BUT that was blank unless the person was already an established Contact in Axosoft. Unfortunately, it looked like creating a new contact did not retroactively update tickets originating from the same email address. Adding the “Created By Email” field to the grid was also a dead end. I knew the information I needed was somewhere because I could see the emails with each ticket. If I could just get a list of the from addresses involved with a ticket, I would be able to get management the information they needed.

AxoSoft Web API to the rescue! Axosoft has a very easy to use RESTful API that allowed me to develop a quick app to download the information I needed.

Prerequisite – Get Access Code

First, you need to get an Access Code to use in all your URLs. That’s under Tools->System Settings in the Axosoft API Settings section.

My installation already had a API defined under Manage API Keys, so all I had to do was click on Manage Tokens and hit Create Non Expiring Token.

NOTE – COPY AND SAVE YOUR TOKEN ONCE IT IS GENERATED. After the initial generation, I haven’t found a way to see the entire token (key portions are Xed out).

Prerequisite – Create Filter

When you pull the incidents through the API, you can pull them by a filter. I recommend that, particularly if you have a decade’s worth of data like us. If necessary, first create the filter in Axosoft under Tools->Other Settings->Filters

You do need the Filter ID. I couldn’t find that in the interface, but you can find that through the awesome API too with the following call in your web browser.

https://yoursubdomain.axosoft.com/api/v5/filters?access_token=yourtoken

In the resulting JSON, find your filter and note the id

Customizing Columns for the Result JSON

Axosoft’s API allows you to pick which columns are returned for both incidents and emails. For that, I pulled down some initial JSON in the web browser, examined the results and then picked what I wanted for the future. My columns parameter for the Incidents ended up being:

columns=id,number,name,reported_date,reported_by_customer_contact,project,customer,workflow_step,has_emails

and my columns for the emails ended up being:

columns=sent_date,subject,from

My Hacky Code!

Now I was ready to make a little app to pull the incidents and then go through each incident and get the related emails. I ended up just saving the incident details and email info into a local database. I put very little effort into my application. It was just a Windows form with a single button. I didn’t even bother to rename the button. 🙂

My Hacky Code – Supporting Classes

To speed this process up, I was just going to deserialize the JSON into classes that matched the info I wanted. My classes ended up:

using System;
using System.Collections.Generic;
using System.Text;

namespace AxoSoftHack
{
    /*
     * {"id":90376,"number":"i27041","name":"Important: An Azure Backup failure alert has been activated for desktop-77184vs.",
     * "reported_date":"2021-01-01T05:00:00Z","reported_by_customer_contact":{"name":"","first_name":null,"last_name":null,"id":0},
     * "project":{"name":"Support Incidents","path":"Support Incidents","id":1},
     * "customer":{"id":null,"company_name":null},
     * "workflow_step":{"name":"Closed - Repeat","order":10,"id":26},
     * "has_emails":true,"item_type":"incidents","has_attachments":false,"subitems":{"count":0},"parent":{"id":0},"has_related_items":false},
     */
    public class IncidentData
    {
        public List<Incident> data;
    }

    public class EmailData
    {
        public List<Email> data;
    }

    public class Email
    {
        public DateTime? sent_date;
        public string subject;
        public string from;
    }
    public class Incident
    {
        public int id;
        public string number;
        public string name;
        public DateTime? reported_date;
        public Reported_By_Customer_Contact reported_by_customer_contact;
        public Project project;
        public Customer customer;
        public Workflow_Step workflow_step;
        public bool has_emails;
    }

    public class Workflow_Step
    {
        public string name;
    }
    public class Customer
    {
        public string company_name;
    }

    public class Reported_By_Customer_Contact
    {
        public string name;
    }

    public class Project
    {
        public string name;
    }
}

My Hacky Code – Looping Through Incidents

Then I just had a simple logic on my onClick for my button. I downloaded the Incidents from the API. I deserialized them into a list of “Incident” objects. Then I looped through each incident. If it had emails, I made an API call to pull the emails for that incident. In my case, I decided to save everything to a local database, but you could do something else like save the data to Excel or CSV.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Net;
using Newtonsoft.Json;
using AxosoftHack;

namespace AxoSoftHack
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string json = "";
            using (WebClient wc = new WebClient())
            {
                json = wc.DownloadString("https://yoursubdomain.axosoft.com/api/v5/incidents/?filter_id=44&access_token=yourtoken&columns=id,number,name,reported_date,reported_by_customer_contact,project,customer,workflow_step,has_emails");
            }

            IncidentData allincidents = JsonConvert.DeserializeObject<IncidentData>(json);

            foreach (Incident incident in allincidents.data)
            {
                string sql = String.Format("INSERT INTO [Incident] ([id],[number],[name],[reported_date],[reported_by_customer_contact_name],[project_name],[customer_name],[workflow_step_name],[has_emails]) " +
                    " VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}',{8})", incident.id, incident.number,incident.name.Replace("'", "''"), incident.reported_date, incident.reported_by_customer_contact.name, incident.project.name.Replace("'", "''"), incident.customer.company_name, incident.workflow_step.name, (incident.has_emails)? 1:0);
                SqlClientAccessor.ExecuteNonQuery(sql);

                string emailJSON = "";

                if (incident.has_emails)  // && (incident.customer.company_name==null || incident.customer.company_name=="")
                {
                    //Let's check the emails.
                    string emailURL = String.Format("https://yoursubdomain.axosoft.com/api/v5/incidents/{0}/emails?&access_token=yourtoken&columns=sent_date,subject,from", incident.id);

                    using (WebClient wc = new WebClient())
                    {
                        emailJSON = wc.DownloadString(emailURL);
                    }

                    EmailData allEmails = JsonConvert.DeserializeObject<EmailData>(emailJSON);

                    foreach (Email myEmail in allEmails.data)
                    {
                        
                      
                        string emailSQL = String.Format("INSERT INTO [Email] ([sent_date],[subject],[from],[incident_id]) " +
                                            "VALUES ('{0}', '{1}', '{2}', {3})", myEmail.sent_date, myEmail.subject.Replace("'", "''"), myEmail.from.Replace("'", "''"), incident.id );

                        SqlClientAccessor.ExecuteNonQuery(emailSQL);

                    }
                }

            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}

Heads Up – Limits on Calls

As a heads up, there is a limit of 1000 API calls a day. I did hit that (some of it had to do with all the testing and experiments I did along the way). In my case, I was able to grab most of the data the first day and then the second day, I grabbed the rest. You can find out where you stand and if you are close to the limit in the Axosoft interface by:

1. Go to Tools->System Settings.
2. Click on Axosoft API Settings.
3. Click the edit icon next to your key
4. Note the API calls made today setting.

With the exception of hitting my API call limit, this process went incredibly fast and smooth. Soon I had key Incident and Email data that I was able to use to get the metrics I needed. I am so thrilled Axosoft had this API available. That said, I did learn my lesson. I’m diligently making sure the Contact is filled in for each Support Incident now. 🙂

You find out more information about the Axosoft API at http://developer.axosoft.com/