Spiceworks Tickets in SharePoint

Spiceworks is a free community Help Desk Management System. It's used for logging tickets. So, organizations can see statistics of tickets logged in the system and resolved.

We can use SharePoint as a platform for displaying Spiceworks statistics. Spiceworks uses a SQLite Database as a backend. All the data related to tickets is stored in the Spiceworks_prod.db file.
For accessing a Spiceworks database we need to install SQLite 3 ODBC, it can be downloaded from http://www.sqlite.org/download.html.

Step 1: Download and installation

Download and install the SQLite3 ODBC driver. Create a DSN using the SQLite3 ODBC Driver.

1. Go to Administrative Tools > Data Sources (ODBC)
2. Select the System DSN tab and click on the Add button.
3. Select “SQLite3 ODBC Driver” and click Finish.
4. Provide the following entries and click on OK.
   • DSN: Spiceworks
   • Database Name: <Spiceworks db Location>
     
     
5. The Spiceworks DSN appears in the System DSN tab.

Step 2: Creating BDC

Create the BDC for accessing data from the Spiceworks' DSN as in the following:

1. Start Visual Studio 2010, create a new SharePoint Solution using the Business Data Connectivity Model template under the SharePoint 2010 category.
2. This Template will generate a BDC Model (XML defining BDC), Entity class and Service for fetching data from the LOB application.
3. Create all the necessary columns that you want to display in SharePoint in the BDC model and entity class. For details of creating the BDC Model, refer to: http://msdn.microsoft.com/en-us/vstudio/ff623022.aspx.
4. You are ready with your BDC model and Entity class. Now we will access Spiceworks data in our code and initialize our entity. Now we will use the Spiceworks DSN that we created in the previous step.
   We will use an ODBC connection to read the DSN.
   
   
   Create an ODBC Connection using this connection string:
   
   
   Now we will use an ODBC Command to read data into datatable.
   
   
   
5. You have data in the datatable, use that data and populate the entity.
6. Compile and deploy the solution to the server.

Step 3: Configure BCS Administrator

1. Go to Central Administration > Manage Service Applications > Business Connectivity Service.
2. Select your BCS from External Content Types. Then click on Set Object Permission from the Ribbon.
3. Add an Administrator account and select all permissions.

Step 4: Create External List

1. Open the SharePoint Site and click on Site Actions> More Options.
2. Select External List from Installed Templates.
3. Click on the Create button and provide a name and select your BCS in External Content Type.
4. And then click the create button.

An external list will display all the data fetched from Spiceworks. Since we have fetched data into SharePoint we can use various SharePoint KPI tools to display statistics. BCS services will return real-time data in our external list.

Summary

In this article we saw how to create a DSN for the Spiceworks database and then used that DSN in the custom BCS solution and deployed it on the server. Then we assigned the BCS administrator and created the External List using that external content type.