Osticket: API Get Tickets

[csf30816]

Hello. Is there any way to use the osTicket API (I know it exists) to get all the tickets and reply to a ticket? Thanks, I am trying to make a mobile app for my agent's that they can use to respond to tickets.

Thanks,
CSF

osTicket v1.10.1

[ntozier]

No. The ticket open API opens tickets.

[JediKev]

There is no way to GET with the osTicket API unless you add it yourself. At this time the API is very basic to where you can only _create_ Tickets or _create_ Tasks. A full API revamp is on our development roadmap so stay tuned.

[csf30816]

OK Thanks.

[fdellwing]

This is not really hard to do. For anyone looking into this, here our implementation in JavaEE.

Create user:

try {

    String sqlMail = "INSERT INTO ost_user_email (user_id, flags, address) VALUES (" + id + ",0,'" + user.getEmailAdress() + "')";
    String sqlUser = "INSERT INTO ost_user (id, org_id, default_email_id, status, name, created, updated) VALUES ("
        + id + ", 0, (SELECT id from ost_user_email WHERE user_id = " + id + "), 0 ,'" + user.getFirstname() + " " + user.getLastname() + "', NOW(), NOW())";
    String sqlAccount = "INSERT INTO ost_user_account (user_id, status, timezone, username, passwd, registered) VALUES ("
        + id + ", 9, 'Europe/Berlin','" + user.getUserName() + "','" + pw + "', NOW())";
    String sqlCdata = "INSERT INTO ost_user__cdata (user_id) VALUES (" + id + ")";
    Class.forName(Constants.DBMYSQLDRIVER);

    Connection c = null;
    try {
    c = DriverManager.getConnection(Constants.DBCONNECTION_OST, Constants.DBOSUSER, Constants.DBOSPW);
    c.setAutoCommit(false);
    Statement querymy = c.createStatement();

    if (!checkLocal()) {
        querymy.execute(sqlMail);
        querymy.execute(sqlUser);
        querymy.execute(sqlAccount);
        querymy.execute(sqlCdata);
        c.commit();
    }
    querymy.close();
    c.close();

    con.commit();
    con.close();
    } catch (SQLException ex) {
    Logger.getLogger(RbacUserFacade.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
    c.close();
    }

} catch (ClassNotFoundException ex) {
    Logger.getLogger(RbacUserFacade.class.getName()).log(Level.SEVERE, null, ex);
}

Get all open tickets for the specific user:

public static List<Messages> loadTickets(long userid) throws ClassNotFoundException, SQLException {
      Class.forName(DBDRIVER);
      Connection con = DriverManager.getConnection(Constants.DBCONNECTION_OST, Constants.DBOSUSER, Constants.DBOSPW);
      Statement query = con.createStatement();

      String sql = "SELECT * "
          + "FROM (SELECT thread_entry.id, ticket.ticket_id, ticket.number, cdata.subject, thread_entry.body, thread_entry.poster, thread_entry.created, thread_entry.updated "
          + "FROM ost_ticket ticket "
          + "JOIN ost_ticket__cdata cdata ON ticket.ticket_id = cdata.ticket_id "
          + "JOIN ost_thread thread ON ticket.ticket_id = thread.object_id "
          + "JOIN ost_thread_entry thread_entry ON thread.id = thread_entry.thread_id "
          + "WHERE ticket.status_id IN (SELECT id FROM ost_ticket_status WHERE state = 'open') "
          + "AND ticket.user_id = " + userid + " "
          + "AND thread.object_type = 'T' "
          + "ORDER BY thread_entry.id DESC) AS result "
          + "GROUP BY result.number";

      ResultSet result = query.executeQuery(sql);

      List<Messages> data = new ArrayList<>();

      while (result.next()) {

      Timestamp date;
      if (result.getString("updated") == null) {
          date = result.getTimestamp("created");
      } else {
          date = result.getTimestamp("updated");
      }

      Messages msg = new Messages();
      msg.id = result.getInt("ticket_id");
      msg.number = result.getString("number");
      msg.subject = result.getString("subject");
      msg.date = date;
      msg.poster = result.getString("poster").replace(" (Vordefinierte Antwort)", "");
      msg.body = result.getString("body");
      msg.type = "ticket";

      data.add(msg);
      }

      Collections.sort(data);

      con.close();

      return data;
  }

We also use osTicket to deliver news to our customers. The news are posted with a special user and have a custom form field (multiselect) to choose on which server the news should be displayed:

public static List<Messages> loadNews(int newsUser, int newsFieldId, String server) throws ClassNotFoundException, SQLException {
        Class.forName(DBDRIVER);
        Connection con = DriverManager.getConnection(Constants.DBCONNECTION_OST, Constants.DBOSUSER, Constants.DBOSPW);
        Statement query = con.createStatement();

        String sql = "SELECT thread_entry.id, ticket.ticket_id, cdata.subject, form_entry_values.value, thread_entry.poster, thread_entry.body, thread_entry.created, thread_entry.updated "
                + "FROM ost_ticket as ticket "
                + "JOIN ost_ticket__cdata cdata ON ticket.ticket_id = cdata.ticket_id "
                + "JOIN ost_thread thread ON ticket.ticket_id = thread.object_id "
                + "JOIN ost_thread_entry thread_entry ON thread.id = thread_entry.thread_id "
                + "JOIN ost_form_entry form_entry ON ticket.ticket_id = form_entry.object_id "
                + "JOIN ost_form_entry_values form_entry_values ON form_entry.id = form_entry_values.entry_id "
                + "WHERE ticket.user_id =  " + newsUser + " "
                + "AND ticket.status_id IN (SELECT id FROM ost_ticket_status WHERE state = 'open') "
                + "AND form_entry_values.field_id = " + newsFieldId + " "
                + "AND form_entry_values.value LIKE '%" + server + "%'";

        ResultSet result = query.executeQuery(sql);

        List<Messages> data = new ArrayList();

        while (result.next()) {
            Timestamp date;
            if (result.getString("updated") == null) {
                date = result.getTimestamp("created");
            } else {
                date = result.getTimestamp("updated");
            }

            Messages msg = new Messages();
            msg.subject = result.getString("subject");
            msg.date = date;
            msg.poster = result.getString("poster");
            msg.body = result.getString("body");
            msg.type = "news";

            data.add(msg);
        }

        Collections.sort(data);

        con.close();

        return data;
    }

Have fun!

[Jarvvski]

@JediKev if someone was to work on something like this, and create a PR...

Would it be reviewed? Or if you're planning something in the future are you just going to wait for that

EDIT: nevermind, I see this repo isn't the dev area anymore

[JediKev]

@Jarvvski We have a _complete_ API revamp on our development roadmap and it's actually requested a lot so it's high up on the list. I can't give you an exact date, just know it's coming in the future.

[Jarvvski]

@JediKev great news! I'm looking forward to it

[ghost]

@JediKev I know you can't talk about the time when the revamp of the api will be released.
But I have seen in the past that osticket members "revealed" some details of new feature's that did come before they were released.

You mentioned only "complete revamp", can you say a little bit more about that? Like will there be a webhook integration or what rough will be possible? I'm curious about that.

[ntozier]

It's my understanding that this is something that we aren't allowed to go into any details about at this time.

[ghost]

Ok, I understand. Thanks anyway for the quick answer.

[JediKev]

@ntozier is right in that this is something we can't discuss openly because it's relates to some other things that are planned ahead. I think after this next release, 1.11 (which will be pretty big actually), we will be able to discuss it more but as of now all we can say is that a complete API revamp is planned. I do know that the more something is requested the higher of a chance it will be implemented because we do work off of what this community thinks/wants/needs as long as it fits our vision. So I'm sure that if "API Webhook Integration" is highly requested it might make it in the initial release of the API Revamp or in later patches of it. Or possibly we can leave it up to the community to make a plugin for it after the Revamp is released. Who knows? All I know is that there is so much exciting things coming and I can't wait for the community to get all of it. Cheers.

[ntozier]

That was a much more eloquent response than mine... and I echo that I'm excited to be able to talk about what I know. :)

[JediKev]

@ntozier I've been taking "eloquent response" classes in my free time. 😜 (lol just kidding)

[ghost]

That sounds great 👏 More and more I can understand why you communicate not so much and it is better to do so. You should more communicate about why you communicate so little 😉

I try to be patient and watch in the meantime a few times "The Big Lebowski" or "Rick and Morty"..

[JediKev]

@mfelber

You should more communicate about why you communicate so little 😉

I feel you. I will start communicating that better lol

I try to be patient and watch in the meantime a few times "The Big Lebowski" or "Rick and Morty"..

You got me at Rick and Morty...My Favorite. Show. Ever. Period.

[ntozier]

But @JediKev what about Star Wars Rebels?!?!?
/pretends to have a betrayed look on his face

[JediKev]

@ntozier
Tbh, I really didn't like that series too much...
/force speeds before legs get chopped off like Anakin

[ghost]

@JediKev Yeah, Rick and Morty is totally crazy, funny, sad, profound and socially critical. The way television was supposed to be! By the way nearly the same is true for "The Big Lebowski". But there's only one of them..

[koichirose]

Is anyone working on this? We could collaborate, I'm trying to implement the required api methods to get all tickets of a user, get a single ticket, reply to it...

[Jarvvski]

@koichirose I've replied to your other issue.

[amalmagdy]

osTicket more API functions are here (https://github.com/osTicket/osTicket/pull/4361/files)