Zendesk API Programming

Zendesk API Programming

Zendesk is a terrific helpdesk ticketing system I've been using for the last year.  Last week I attended "Zendesk University" in Irvine, and was surprised to see over 200 participants!  It was an afternoon full of informative presentations, great networking discussions, and abundant Zendesk swag.

And it sparked my interest in their API.  In less than a day I was able to get command line access to Zendesk's API up and running.  Follow along, and you'll have your basic API interface running within an hour.

Step 1: You need access to Zendesk.  If you don't already have an account, sign up for one.  A single user account, with your own domain name, is only $12/year.  Go to:
http://www.zendesk.com.

Step 2: You need a command-line program called curl.  It provides very simple access to many types of data interfaces.  (Zendesk uses JSON, and decoding JSON is whole other article.)  Make sure you get both curl and the libcurl library; they're available at:
http://curl.haxx.se

Step 3: You're all set!  Now you just need to know the proper commands.  Two places to explore are:
http://developer.zendesk.com
http://developer.zendesk.com/documentation/rest_api/introduction.html

Here are a few queries to get you started (replace the email address, mydomain and mypassword with your own).  These all run inside your Windows command console:

To list all your Zendesk users:

   curl -k -u me@mydomain.com:mypassword https://mydomain.zendesk.com/api/v2/users.json

To list all your tickets:

   curl -k -u me@mydomain.com:mypassword https://mydomain.zendesk.com/api/v2/tickets.json

To list the contents of ticket #1234:

   curl -k -u me@mydomain.com:mypassword https://mydomain.zendesk.com/api/v2/tickets/1234.json

One of the biggest takeaways from Zendesk University was seeing how large the whole user-ecosystem is.  With over 30,000 business using Zendesk, and 200 million people (!), Zendesk has very active user and developer communities.  Check their website to find a conference or user group meeting near you.

My next step is to write a program to access the API which can both create and read tickets.  First I have to pick the language ... C#?  Java?  Or my old favorite VB.Net?  There are JSON libraries for all of them, as well as Python, PHP and Ruby.  What's your favorite?

iSpyPhone - Your iPhone Knows Where You've Been

You’ve heard how your iPhone keeps track of where you’ve been, right?  Would you like to see exactly which locations it has been logging? 

If you back up your iPhone to a Windows computer, here’s how to access that data in just three easy (well, slightly technical) steps.  Note: if you enabled backup encryption in iTunes, this isn’t going to work.  Encrypted files are secure, and no longer readable.

The paper that started the controvery can be found at http://petewarden.github.com/iPhoneTracker/  Though the discussion is Mac-based, it’s a great reference for Windows developers.  The authors guide you through finding the Manifest.mbdb file, referencing a Python script to read it, searching for a file named consolidated.db, then translating that file to the alphanumeric file names Apple uses to obfuscate the data.

Where they elegantly used a scalpel to find the location file, sometimes an ax is so much more efficient:

Step 1: Find the File that Stores the Locations

• Search for a folder called MobileSync (Start à Search à MobileSync).
• Open it (you’ll probably just see a folder named Backup).
• Search from here for files containing the text CellLocation

That’s it; you found it!  If there are multiple, use the most recent one.  If you didn’t find it, try searching for the text SQLite.  Apple stores data in SQLlite databases, where each file is an independent database; there should be several dozen.  If you don’t find any of these, you need to change search settings in your Content Indexing Service (see http://www.dslreports.com/faq/9500)

Step 2: Extract the Location Coordinates

The file name is a string of numbers and letters like 4096c9ec676f2847dc283405900e284a7c815836.  Copy it to a file with a friendlier name, like consolidated.db.

Next, extract the geo-location data.  You’ll need a SQLite database reader:

• Go to http://www.sqlite.org/download.html
• Download the command-line shell program: sqlite-shell-win32-x86-3070600.zip
• Unzip it, and you’ll have the file sqlite3.exe (no installer needed)

• From a DOS Command Prompt, enter:
  sqlite3 consolidated.db "select * from CellLocation;" > CellLocation.txt

Now you have all your information in a pipebar-delimited text file.  There are no column headers, so:

• Open CellLocation.txt and add this string as the first line:

MCC|MNC|LAC|CI|Timestamp|Latitude|Longitude|HorizontalAccuracy|Altitude|VerticalAccuracy|Speed|Course|Confidence|datetime|kml

• Import it into Excel, and save it as CellLocation.xls

You should have 13 columns of iPhone data, and two empty columns: datetime and kml.

datetime

SQLite stores the Timestamp (Column E) as ‘seconds since 1/1/2001’.  Excel stores date and time as ‘days since 1/1/1900’.  To convert, change seconds to days (divide by 86400), set to the same start dates (add 36892), and adjust for your timezone (I’m 8 hours behind UTC so I’ll subtract 8/24^th of a day).

• Copy this string into cell N2 (datetime):
    =(E2/86400)+36892-(8/24)
• Format the cell to display the date and time
• Copy this cell down the entire column

Step 3: Display the Location Coordinates

The final step is to create a kml file that can be displayed in Google Earth.  (You do have Google Earth installed, right?  If not, go get it.)

• Copy this string into cell O2 (kml):
    ="<Placemark><name></name><description>"&TEXT(N2, "dddd mm/dd/yyyy hh:mm AM/PM")& "</description><Point><coordinates>" &G2&","&F2&"</coordinates></Point></Placemark>"

• Copy this cell down the entire column

Now to make a file that Google Earth can read, in KML format:

• Create and open a text file called CellLocation.kml
• Insert these two lines into CellLocation.kml:

<kml xmlns="http://earth.google.com/kml/2.2"><Document>

</Document></kml>

• Copy all your kml statements from the kml column (Column 0)
• Paste them between these two statements

Double-click CellLocation.kml and Google Earth should start up, with yellow pushpins showing each position your iPhone has logged.  Click on a pushpin to see the date and time it was recorded.

Note how the locations can vary drastically  I have points over ten miles apart with identical timestamps.  This may be related to Apple geolocating WiFi networks in the area, rather than determining exactly where I was standing … so take all results as approximations only.

If you’re concerned about someone accessing your PC to determine where you’ve been, set your backup security to Encrypted in iTunes.  Apple will still know, but no one else will be able to snoop on you.

You now have the tools to find, extract and process the iPhone’s SQLite files.  There are dozens of other ones on your system - go explore, and discover more ways your iPhone is keeping tabs on you!

11,000 points collected in the last 6 months

 

VISUAL BASIC 2010 EXPRESS & SQL SERVER 2008 EXPRESS

Two great tools that go great together 

VB.Net and SQL Server are powerful, industry-standard development tools from Microsoft.  It’s sometimes surprising to realize that anyone can download and use these programs for free.

Getting them installed and working together took longer than I expected.  Google searches showed that many people were even questioning whether a VB Express program could could access a SQL Server Express database.  It can, but there’s a trick to it.

So if you’re trying to get started with your first VB+SQL program, here’s a small app to start you off.  I hope it saves you a couple hours!

______________________________________________

First, download and install Visual Basic 2010 Express and Sql Server 2008 Express from www.microsoft.com/express. 

SQL Server

Next, start Sql Server Management Studio, and log in.

 (Start à All Programs à Microsoft SQL Server 2008 à SQL Server 2008)

Create a new database by right-clicking Databases and selecting New Database

Name the new database MyTestDB and click OK.

Expand the Databases folder to find your new database:

Click New Query and run this script to create an empty table named tblTest:

CREATE TABLE [MyTestDB].[dbo].[tblTest] (

   [tstNum_PK] [int] IDENTITY(1,1) NOT NULL ,

   [tstData] [varchar] (100) NOT NULL

) ON [PRIMARY]

Visual Basic 

Now start Visual Basic and create a new Windows Form Application named DatabaseTest. 

(Start à All Programs à Microsoft Visual Studio 2010 Express à Visual Basic 2010)

On your VB Form, add two TextBoxes and two Buttons.  For simplicity, we’ll leave the their properties at their default settings and change them in the code.





à Here’s the trick: with Visual Basic 2010 Express, you have to connect directly to the database file.  You may be used to connecting to remote database servers – you can’t do that here.  When you created MyTestDB above, SQL Server created a file on your PC named MyTestDB.mdf.  Find it, and if the full path name is different from:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MyTestDB.mdf

Then you’ll need to change two lines in this sample code to match your location.

Add the code below to your program.  Now you’re ready to run.

Start the program and type something into the first text box.  Then click Add to DB. 

Unless you see an error message, a new record was just written to your database.

To read and display all the records in this table, click Show DB.

The ‘1’ at the beginning is the Primary Key for your record – a field you generally want to include, but would rarely display.

That’s it.  If you got all the way through, you now have a working (though bare-bones) program to begin developing your own applications.  Good luck!

 

Imports System.Data.SqlClient 

Public Class Form1

   'at program startup, set the control properties

   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

      Button1.Text = "Add to DB"

      Button2.Text = "Show DB"

      TextBox2.Multiline = True

   End Sub 

   'write a record to the database table

   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

      Dim sqCon As New SqlClient.SqlConnection("Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MyTestDB.mdf;Database=MyTestDB; Trusted_Connection=Yes;")

      Dim sqCmd As New SqlClient.SqlCommand

      sqCmd.Connection = sqCon            'create the DB connection

      sqCmd.CommandText = "INSERT INTO [MyTestDB].[dbo].[tblTest] VALUES ('" & TextBox1.Text & "')"

      sqCon.Open()                        'open the connection

      sqCmd.ExecuteNonQuery()             'execute the SQL command

      sqCon.Close()                       'close the connection

   End Sub 

   'read and display all records from the database table

   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

      Dim sqCon As New SqlClient.SqlConnection("Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MyTestDB.mdf;Database=MyTestDB; Trusted_Connection=Yes;")

      Dim sqCmd As New SqlClient.SqlCommand

      Dim sdrRow As SqlClient.SqlDataReader

      'define the DB connection and search string

      sqCmd.Connection = sqCon

      sqCmd.CommandText = "SELECT * FROM tblTest"

      'open the DB connection

      sqCon.Open()                        'open the DB connection

      sdrRow = sqCmd.ExecuteReader()      'read the entire table

      'extract and display each field

      TextBox2.Text = ""                  'clear the text box

      Do While sdrRow.Read()

         TextBox2.Text = TextBox2.Text & sdrRow.GetValue(0) & vbTab     'get the primary key

         TextBox2.Text = TextBox2.Text & sdrRow.GetValue(1) & vbCrLf    'get the string

      Loop

      'close up and return

      sdrRow.Close()

      sqCon.Close()

   End Sub

End Class

Duplicated work in Healthcare Credentialing?

According to a 2005 MGMA survey, physicians submit an average of 18 credentialing applications every year, while non-physician providers submit an average of 8: http://www.mgma.com/WorkArea/mgma_downloadasset.aspx?id=19248

Think about that – a physician must submit a detailed application to a medical center or insurance company about every 3 weeks, for the rest of his career.  And unless delegating, 18 different people will perform the same credentialing activities: query the NPDB, check OIG, verify licenses, etc.

Is Aetna’s credentialing substantially different from CIGNA’s?  Does City of Hope credential better than UCLA Medical Center?  Probably not; best practices in credentialing are freely shared by Medical Staff Services professionals, encouraged by TJC and NCQA, and promoted by companies like HC Pro.

With over 700,000 physicians in the US, we’re creating millions of medical credentialing reports every year that are essentially duplicates.

MGMA’s survey didn’t separate new credentialing applications from re-credentialings, nor did it mention delegated credentialing – all of which greatly affect the amount of work performed.  Does anyone have estimates for these that can be shared?

CAQH has been instrumental in collecting and standardizing provider data, generally simplifying the application process – but they don’t address credentialing.  What do you think the next step should be to reduce the time and money spent on duplicated effort (or is the current way actually the best way)?