Dirt is a reporting frontend to Best Practical Request Tracker. It was written to facilitate my work at Directi, who kindly let me work on it during regular working hours.

• Kanban Board
• An arbitrary number of dashboards
• A wiki for documentation
• Pie Chart, Bar chart and Tabular reports
• Prettier than RT (IMO)
• … more to come (see TODO.textile)

Installation is still pretty clunky:

• Clone the source
• Install Ruby 1.9.3 if you don’t already have it. Using rvm for this is highly recommended.
• Run bundle install to fetch dependencies. Using rvm’s gemsets feature to isolate these dependencies is a good idea.
• Copy config/config.yml.sample to config/config.yml and edit.
• Copy config/database.yml.sample to config/database.yml and edit. Note that there are two database configurations:
  • rt : Your existing Request Tracker database. With the current featureset this can be read-only access. This will change with later releases.
  • dirt : A new empty database that you have created for dirt.
• Run RACK_ENV='production' rake db:migrate to create the dirt schema. Ensure the RACK_ENV environment has been defined in database.yml and config.yml.
• Review create_views.sql — TL;DR It creates a view in the RT database to make report queries more intuitive.
• Run create-views.sql against your RT database. Consult your RDBMS documentation to find out how to do this. Note: create-views.sql uses MySQL’s dialect of SQL.
• Run: rackup -E <env>. Dirt has only been tested with thin. I’d like to support unicorn, passenger and others as well. Patches for this would be welcome

• Navigate to http://your.host.name
• Login with your RT username and password
• Add a project
• Use textile syntax to add text content to the page.

• Add a query using the following syntax:

<~
{
  "type": "table",
  "sql":"SELECT COUNT(id) AS Count, Owner AS Name FROM expanded_tickets WHERE Queue = 'linux-hosting' AND Status IN('new', 'open', 'stalled') GROUP BY Owner",
  "caption": "Ticket Ownership"
}
~>

Any arbitrary SQL statement querying the ‘expanded_tickets’ view can be used to report on tickets.

Example:

<~
{
  "type": "aging",
  "queues":["linux-hosting", "linux-hosting-issues"],
  "caption": "Ticket Aging"
}
~>

Note that queues must be passed an Array, even if it is a single item array.

Example:

<~
{
  "type":"simple_task_board",
  "caption":"Taskboard",
  "group_by":"Status",
  "group_sequence":["new","open","stalled"],
  "ticket_selector":"Queue = 'linux-hosting'",
  "resolved_after":"1 week ago"
}
~>

• Kanban taskboard can be configured on the project settings page
• Each column in a row must be added as an array element
• To add sub columns pass an array of array elements with the parent column name
• Add a query using the following syntax:

Example:

<~
{
"group_sequence":[
  [ ["spec1"] ],
  [ ["spec2"], ["spec3"] ],
  [ ["spec4"], ["spec5"], ["spec6"] ],
  [ ["spec7", [ ["subspec1"], ["subspec2"], ["subspec3"] ] ] ],
  [ ["spec8"], ["spec9", [ ["subspec1"], ["subspec2"], ["subspec3"] ] ], ["spec10"] ]
  ],
"queues":["linux-hosting","linux-hosting-issues","vps-hosting", "vps-hosting-issues"],
"resolved_after":"last week monday"
}
~>

Note that queues must be passed an Array, even if it is a single item array.

• The group by column comes first in the SQL statement
• The data column follows the group column
• If more than one data column in received, only the 1st data column is used to generate the graph

Example:

<~
{
"type":"pie_chart",
"caption":"Piechart Caption",
"sql": "SELECT Owner AS Name, COUNT(id) AS Count  FROM expanded_tickets WHERE Queue IN('linux-hosting', 'linux-hosting-issues', 'vps-hosting', 'vps-hosting-issues') AND Status IN('new','open','stalled') GROUP BY Owner"
}
~>

• The group by column comes first in the SQL statement
• The data column follows the group column
• More than one data column can be plotted if ‘group-sequence’ is defined

Example:

<~
{
"type":"bar_chart",
"direction":"vertical",
"caption":"Barchart Caption",
"sql":"SELECT Owner AS Name, COUNT(id) AS Count, %AVG_DAYS_SINCE(Created), %AVG_DAYS_SINCE(LastUpdated) FROM expanded_tickets WHERE Queue IN('linux-hosting', 'linux-hosting-issues', 'vps-hosting', 'vps-hosting-issues') AND Status IN('new','open','stalled') GROUP BY Owner",
"source":"source of the data",
"group-sequence": [ "count" , "avg days (created)", "avg days (updated)" ] ,
"y-text":"some y-text"
}
~>

• The group by column comes first in the SQL statement
• The data column follows the group column
• If more than one data column in received, only the 1st data column is used to generate the graph
• Tip: Frequence graph can obtained by ordering according to the data column

Example:

<~
{
"type":"line_chart",
"caption":"Linechart Caption",
"sql":"SELECT COUNT(id) AS Count, %AVG_DAYS_SINCE(Created)  FROM expanded_tickets WHERE Queue IN('linux-hosting', 'linux-hosting-issues', 'vps-hosting', 'vps-hosting-issues') AND Status IN('new','open','stalled') GROUP BY Owner ORDER BY AVG_DAYS_SINCE_CREATED",
"group-sequence": [ "count" ] ,
"y-text":"some y-text"
}
~>

In most places where you provide an complete or partial SQL statement as a parameter to a macro you can use the following macros to simplify things. Note that the macros are case sensitive.

SQL Statements typically expect static dates:

SELECT * FROM expanded_tickets WHERE LastUpdated > '2012-08-15'

But for a dashboard you’ll typically need a relative date. To answer the question, “show me a list of tickets updated in the last week” you can do this:

SELECT * FROM expanded_tickets WHERE LastUpdated > %DATE(last week)

You can write stuff like:

• last week
• yesterday
• last week thursday
• 4 o’clock last monday

Dirt uses the Chronic gem to parse these description. See Chronic’s examples for a complete listing.

Calculate the average number of days since a date field. Example:

SELECT Owner, %AVG_DAYS_SINCE(Created) FROM expanded_tickets GROUP BY Owner

to tabulate the average age of tickets per user.

Check the bug tracker on GitHub

See TODO.textile

Standard way: fork, modify and send pull request. User visible changes must have documentation patches.

Dirt – A reporting frontend to Best Practical Request Tracker

Copyright © 2012-2014 Biju Philip Chacko

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.