h1. Dirt Readme
Dirt is a reporting frontend to Best Practical ["Request Tracker":http://bestpractical.com/rt/]. It was written to facilitate my work at ["Directi":http://www.directi.com], who kindly let me work on it during regular working hours.
h3. Features
* 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":docs/TODO.textile])
h3. Installation
Installation is still pretty clunky:
* Clone the source
* Install Ruby 1.9.3 if you don't already have it. Using ["rvm":https://rvm.io] 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
@. Dirt has only been tested with thin. I'd like to support unicorn, passenger and others as well. Patches for this would be welcome
h3. Using Dirt
* Navigate to http://your.host.name
* Login with your RT username and password
* Add a project
* Use ["textile":http://redcloth.org/textile] syntax to add text content to the page.
h4. 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"
}
~>
p. Any arbitrary SQL statement querying the 'expanded_tickets' view can be used to report on tickets.
h4. Aging Macro
Example:
<~
{
"type": "aging",
"queues":["linux-hosting", "linux-hosting-issues"],
"caption": "Ticket Aging"
}
~>
p. Note that queues *must* be passed an Array, even if it is a single item array.
h4. 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"
}
~>
h4. 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"
}
~>
p. Note that queues *must* be passed an Array, even if it is a single item array.
h4. 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"
}
~>
h4. 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"
}
~>
h4. 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"
}
~>
h4. 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.
h5. %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":https://github.com/mojombo/chronic] gem to parse these description. See ["Chronic's examples":https://github.com/mojombo/chronic#examples] for a complete listing.
h5. %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.
h3. Bugs
Check the bug tracker on ["GitHub":https://github.com/botsie/dirt/issues]
h3. TODO List
See ["TODO.textile":docs/TODO.textile]
h3. Contributing
Standard way: fork, modify and send pull request. User visible changes *must* have documentation patches.
h3. License
Dirt - A reporting frontend to ["Best Practical":http://bestpractical.com] ["Request Tracker":http://bestpractical.com/rt/]
Copyright (C) 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
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.