Dirt Readme

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.

Features

• An arbitrary number of dashboards
• A wiki for documentation
• Tabular reports
• Prettier than RT (IMO)
• … more to come (see TODO.textile)

Installation

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/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.
• Run cd db; ruby create_views.rb < mapping.json > create-views.sql
• Review create-views.sql — TL;DR It creates a few views so we have a schema that is ORM friendly and 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: RACK_ENV=<env> ruby application.rb. Ruby rack integration is currently broken.

Using Dirt

• Navigate to http://your.host.name
• Add a project
• Use textile syntax to add text content to the page.

Table Macro

• 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.

Aging Macro

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.

Simple Taskboard Macro

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 Macro

• 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.

Pie Chart Macro

• 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"
}
~>

Bar Chart Macro

• 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"
}
~>

Line Chart Macro

• 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"
}
~>

SQL Interpolation

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.

%DATE()

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.

%AVG_DAYS_SINCE()

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.

Bugs

1. When editing a new page, clicking ‘Cancel’ will return to the edit page.
2. Ruby Rack support is not working
3. Project Tabs aren’t working well

TODO List

See TODO.textile

Contributing

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

License

Dirt – A reporting frontend to Best Practical Request Tracker

Copyright © 2012 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

http://www.apache.org/licenses/LICENSE-2.0

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.