Transcript Generating Reports with REQADM 1.2 Steve Willoughby PMD Engineering Computing Overview • • • • • • • • The Basic Idea REQADM’s Reporting Tool Output Formats Automatic Web Page Generation Simple Queries Advanced Queries Raw Data Dumps Documentation.

Generating Reports
with REQADM 1.2
Steve Willoughby
PMD Engineering Computing
Overview
•
•
•
•
•
•
•
•
The Basic Idea
REQADM’s Reporting Tool
Output Formats
Automatic Web Page Generation
Simple Queries
Advanced Queries
Raw Data Dumps
Documentation Available
The Basic Idea
• REQADM’s database contains your history
–
–
–
–
Issues and complaints from customers
Incidents affecting your systems
Time required to resolve different issues
Employee performance metrics
• Caution...
The Basic Idea
• Raw data can be analyzed into meaningful
reports
–
–
–
–
–
Number of tickets submitted by week
Top 10 categories of issues seen
Mean time to repair by problem type
System failure history by host or platform type
Many others...
REQADM’s Reporting Tool
• Used to have to be done with Perl scripts
• REQADM 1.2 introduced “reqadm-report”
tool
–
–
–
–
High-level query language
Standardize your reports
Generate color graphs for printing or web pages
Raw ticket data exported to other tools
REQADM’s Reporting Tool
• Invoke with query file already prepared:
reqadm-report -file filename
• Report sent to standard output
• When running interactively, -verbose
option is useful for watching progress
Output Formats
• Reqadm-report’s default output format is
“structured” ASCII
– Multiple graphs possible per file
– All data necessary to plot graph included
– Easy to write scripts to parse or translate to
other formats
Structured ASCII Output
CHART (Tickets) (Q1 2000) (REQADM) 28 18
LEGEND
(Tickets submitted per week) 1 0 0 BAR
(Tickets closed per week)
0 1 0 BAR
(Tickets closed late)
0 0 1 LINE
AXES
(Work Week) 12 1 0 13 14 (WW13) 8 X
(Tickets)
12 0 200 10 (X200) 8 Y
TICKS
tick labels are listed here...
Structured ASCII Output
DATA 1 0 0 BAR
0 100 Y
1 110 Y
…
DATA 0 1 0 BAR
0 98 Y
1 109 Y
…
ENDCHART
®
PostScript
Output
• Alternative output format to drive printers
directly
– Actually just programs printer to understand
structured ASCII format
• Hard-copy reports
• Use other software tools to convert
PostScript to other graphics formats
CSV Output
• Alternative output format for exporting
“raw” ticket data to spreadsheets or other
report generators
• No data analysis other than ticket selection
#Comments delimit each dataset in file...
#Fields: queue number subject
request,123456,Help with Printer problem
request,123457,”Need software, hardware”
request,123458,E-Mail seems to be borken
Automatic Web Page Generation
• Build request center metrics pages
automatically via cron
• Build your query files and generate PNG
files (or JPEG or GIF or whatever…):
reqadm-report -ps -file weekly \
| gs -sDEVICE=png256 -r72 \
-sOutputFile=graph%d.png
Simple Queries
• Going from idea to finished graph is as
simple as these steps:
–
–
–
–
–
Specify report titles and axis styles
Select a set of tickets
Filter the selection further (temporarily)
Plot the filtered data onto the graph
Repeat, if desired, with different data
Titles and Styles
Titles
y2 Axis
EC Made Commits
(REQUEST QUEUE ONLY)
820
90%
738
80%
656
70%
574
60%
492
50%
410
40%
328
30%
246
20%
164
10%
82
Made Commits
y Axis
0%
WW45 WW46 WW47 WW48 WW49 WW50 WW51 WW52 WW01 WW02 WW03 WW04 WW05 WW06
Number of Tickets
100%
Axis
Label
Tick
Label
0
Work Week
x Axis
TICKETS RESOLVED
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 14:10:23
% Made Commits
Goal
Legend
Titles and Styles
• Set the overall chart titles:
title title-string [size]
subtitle title-string [size]
• Examples:
title ”Tickets resolved”
title ”Top Categories” 48
subtitle ”Q1 2000”
Titles and Styles
• You can also change paper size and which
fonts to use (see reqadm-report
documentation for full details).
Chart Axes
• Provide axis and tick labels
• The major (sample) axis is always “x”
– ...but you can “flip” the chart so the x axis runs
vertically and the y axis runs horizontally
– Controls how data are sampled for plotting on
the chart
x sample-type [options]
Chart Axes (x)
•
•
•
•
•
daily
hourly
iweekly
monthly
mweekly
24 hour samples (midnight to midnight)
1 hour samples
7 day samples, by Intel work week number
by calendar month
7 day samples, by Monday-centric week no.
• quarterly 3-month samples (Jan, Apr, Jul, Oct)
• weekly 7 day samples, by Sunday-centric week no.
• yearly by calendar year
• values sample by value, not by date
Chart Axes (x)
• Sample dates may be skewed forward or
back to adjust for time zones, or nonstandard reporting frames (Wed-Tue, etc.)
– (-adjust option)
• Custom sample criteria may be specified
rather than these pre-made ones
– (-custom option)
x Axis Options
• Other options available to control the
appearance of the data samples:
-sort [options]
• Sorts data points. Options provide control over sort order
(ascending, descending, by y-axis value, numeric, etc.)
-top n
(or… -bottom n)
• Selects only the n highest (or lowest) data points
-flip
• Flip chart 90º so x runs vertically
x Axis Options
-label string
-ticklabel spec
• These control the overall axis label and the labels
placed on each “tick” mark on the x axis.
• The tick label spec parameter may include sample
“bucket” identifiers such as dates, times, week
numbers, or custom generated labels.
• Normally, you don’t need -ticklabel — an
appropriate one is automatically chosen for you
based on axis sample type.
y Axis Options
• y (data) axis runs vertically, containing the
actual data tabulated in each sample bucket
• Specify y axis options as:
y [options]
• Options:
%
-label string
Graph as percentages
Specify axis label
y2 Axis Options
• y2 (data) axis runs vertically on the right
side of the graph
• Used when you need two scales:
– E.g., number of tickets vs. % done on time
• Specify y2 axis options as:
y2 [options]
• Options are the same as with the y axis
Example Graph
EC Made Commits
(REQUEST QUEUE ONLY)
90%
738
80%
656
70%
574
60%
492
50%
410
40%
328
30%
246
20%
164
10%
82
Made Commits
820
0%
WW45 WW46 WW47 WW48 WW49 WW50 WW51 WW52 WW01 WW02 WW03 WW04 WW05 WW06
Work Week
TICKETS RESOLVED
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 14:10:23
% Made Commits
Goal
0
Number of Tickets
100%
Example Graph
title ”EC Made Commits”
subtitle ”(Request queue only)”
x iweekly -label ”Work Week”
y % -label ”Made Commits”
y2
-label ”Number of Tickets”
Example Graph
Top 10 Root-Cause Categories by Volume
TOP-LEVEL PROBLEM TYPES
Fileserver/Disk Management
Account Administration
Logistics
Applications/Commands
Microsoft Windows
UNIX Workstation/Xterminal
Request Administration
Mail/cc:Mail
Mobile Computing/Remote Access
Network/Internet
0
Tickets Resolved WW05 2000
TICKETS RESOLVED WW02 2000
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 15:21:01
50
100
150
200
Tickets Resolved WW06 2000
Tickets Resolved WW03 2000
250
300
350
400
450
Tickets Resolved WW04 2000
500
Example Graph
title ”Top 10 Root-Cause Categories
by Volume”
subtitle ”top-level problem types”
x values -top 10 -flip \
-sort -values
Example Graph
Currently-Open Request Priorities
ALL QUEUES
URGENT (41)
high (104)
medium (439)
low (62)
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 15:32:46
Example Graph
title ”Currently-Open Request
Priorities”
subtitle ”All Queues”
x values -sort -exec prioritySort
Simple Queries
• Going from idea to finished graph is as
simple as these steps:
–
–
–
–
–
Specify report titles and axis styles
Select a set of tickets
Filter the selection further (temporarily)
Plot the filtered data onto the graph
Repeat, if desired, with different data
Selecting Tickets
• Reqadm-report’s first action is to retrieve a
working set of tickets from the REQADM
server
– Not all ticket data loaded at first (for speed)
– Don’t select more tickets (or more detail) than
you really need (to conserve memory and
download time)
• These tickets may be used for multiple plots
Selecting Tickets
select [criteria…]
• Grabs all tickets in entire database which
satisfy the given criteria
– So be careful with your criteria :)
• Specify any number of criteria in any order
– Only tickets matching all specified criteria will
be selected.
Selection by Dates
• Select by date ranges:
closed datespec
due datespec
resolved datespec
submitted datespec
updated datespec
Selection by Dates
• Date specifiers are any of the following:
from date
before date
between date1 [and] date2
• Dates may be in a wide variety of formats:
2/13/00
February 13, 2000
4 weeks ago
Selection by Dates
• N.B.: The date portion of the date specifiers
must be enclosed in double quotes or curly
braces if it contains spaces:
closed from 2/1/00
closed from ”4 weeks ago”
due between {1 week ago} and today
Selection by Dates
• Also, if no time is specified, it defaults to
midnight at the start of that date, so this does
not include 29 Feb itself:
closed between 2/1/00 and 2/29/00
• Whereas, this will include all of February
closed between 2/1/00 and \
”2/29/00 23:59:59”
Selection by Owner/Submitter
• Select by submitter or owner: all tickets
from (owned by) any of the people in the
list of user ID numbers:
from [not] idlist
owner [not] idlist
• The idlist is a list of REQADM user ID
numbers...
Selection by Owner/Submitter
• These ID numbers are formatted thus:
Gdigits
Pdigits
Tdigits
Xdigits
No user (e.g., not assigned an owner)
Rotation group ID number
Person’s own ID number (WWID)
Temporary ID number
Invalid number
Selection by Owner/Submitter
• The idlist may be a single number:
select owner P12345678
• Or, it may contain shell-style wildcards *, ?
and […]
select owner P100*
• Or, it may be a list of ID numbers in braces:
select from {G10[0-4] P1234 P1238}
Selection by Attributes
• Select by ticket attributes:
flags [not] flag-list
importance [not] priority-list
ontime
overdue
priority [not] priority-list
queue [not] queue-list
status [not] status-list
Selection by Flag Attributes
• Flags indicate historical status:
amended
categorized
merged
reopened
requeued
“Read-only” attrib changed
Category assigned to ticket
Others merged into this one
Ever re-opened
Ever re-queued
Selection by Flag Attributes
• The flaglist may be a single flag:
select flags reopened
• Or, it may be a list of flags in braces:
select flags {merged requeued}
– This will select all tickets for which any (not
necessarily all) of the flags are true!
• Flag names may be abbreviated:
select flags not {m req}
Selection by Priority
• Priorities have their usual names:
urgent high medium low
• The same rules apply to selecting with lists
of priorities as we just discussed about flags:
select priority {high urgent}
select importance not l
Selection by Timeliness
• Two simple boolean keywords are provided
to select tickets based on timeliness:
ontime
overdue
• They simply compare the tickets’ due date
vs. their resolution date.
Selection by Queue
• Selecting based on queue works just like
priorities, except you use queue names as
defined in your system:
select queue request
• The same rules apply to selecting with lists
of queues as with the others:
select queue {restore project}
select queue not {req* res* proj*}
Selection by Status
• Status values work like the flags, but
describe the ticket’s current state:
active
closed
due
merged
new
open
Open, not stale or due
Resolved and approved
Open, due within 1 hour
Merged into another ticket
Never assigned to an owner
Active/due/new/pending/stale
Selection by Status
pending
resolved
requeued
stale
“On hold” pending external action
Resolved but not approved yet
Moved to a different queue
Open, no updates in too long
• These behave like the flags described
previously with respect to lists, negation,
abbreviation, etc.
Selection by System
• Select by affected system:
platform [not] list
system [not] list
sysloc [not] list
• The items in these lists are as defined in
your “systems.conf” file:
select platform Intel::Linux
select system {fred barney wilma}
Selection by System
• An incomplete platform with trailing “::”
includes all sub-platform types in the query:
select platform Intel::
• Wildcards may also be used in these names.
Selection by Subject
• Select by ticket subject:
subject [not] [-case] [-regexp] pattern
• The pattern may contain wildcards or a regular
expression. Don’t forget to put quotes or
braces around the pattern if it contains spaces.
select subject *mail*
• Use -case option to match with exact case
(default is case-insensitive matching)
Selection by Category/Type
• In REQADM 1.2, the following criteria
require loading extra data from the server
and will significantly slow your query:
category [not] list
type [not] list
• They select based on root-cause category
and problem type, respectively.
Selection by Category/Type
• Wildcards are allowed, as are multi-item lists.
• However, if any item contains spaces, it must
be quoted inside the list, too. Best to use
braces here rather than quotes:
select category not unix/*
select type {{Printer Issue}
Network}
• Use the short category codes, not the full
English descriptions, for category
Selection Criteria
• Each select or filter starts over with a
fresh set of tickets, completely overriding the
previously-selected (or filtered) ticket list.
• Including the add keyword to the criteria will
cause this select or filter to add the
tickets matching its criteria to the set already
selected by the previous one(s).
Selection Criteria
# get all tickets open now, and
# all those resolved since the
# start of 2000.
select resolved from 1/1/00
select add open
Selection Criteria
• Each select or filter criterion may only
be specified once per selection.
• Multiple select or filter criteria imply
an “and” between them: only tickets matching
all criteria will be selected.
• An “or” operation is performed by following
the first select or filter statement with
another, and including the “add” keyword.
Simple Queries
• Going from idea to finished graph is as
simple as these steps:
–
–
–
–
–
Specify report titles and axis styles
Select a set of tickets
Filter the selection further (temporarily)
Plot the filtered data onto the graph
Repeat, if desired, with different data
Selection Filtering
• Apply filters to refine the selected ticket set
even further
• Temporarily narrow the criteria for a portion
of the graph, perhaps
• Same criteria keywords and options as for
the select statement
filter [criteria…]
Selection Filtering
select resolved from 1/1/00
# now graph all resolved ...
filter overdue
# now graph the set of those
# which were overdue ...
Selection Filtering
• You can “reset” the filter at any time, giving
you back the full set of selected tickets, by
leaving out all criteria (setting the filter to
allow everything):
filter
Example Graph
EC Made Commits
(REQUEST QUEUE ONLY)
90%
738
80%
656
70%
574
60%
492
50%
410
40%
328
30%
246
20%
164
10%
82
Made Commits
820
0%
WW45 WW46 WW47 WW48 WW49 WW50 WW51 WW52 WW01 WW02 WW03 WW04 WW05 WW06
Work Week
TICKETS RESOLVED
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 14:10:23
% Made Commits
Goal
0
Number of Tickets
100%
Example Graph
select resolved from \
”13 weeks ago” queue request
Example Graph
Top 10 Root-Cause Categories by Volume
TOP-LEVEL PROBLEM TYPES
Fileserver/Disk Management
Account Administration
Logistics
Applications/Commands
Microsoft Windows
UNIX Workstation/Xterminal
Request Administration
Mail/cc:Mail
Mobile Computing/Remote Access
Network/Internet
0
Tickets Resolved WW05 2000
TICKETS RESOLVED WW02 2000
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 15:21:01
50
100
150
200
Tickets Resolved WW06 2000
Tickets Resolved WW03 2000
250
300
350
400
450
Tickets Resolved WW04 2000
500
Example Graph
select resolved from ”4 weeks ago”
filter queue request
Example Graph
Currently-Open Request Priorities
ALL QUEUES
URGENT (41)
high (104)
medium (439)
low (62)
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 15:32:46
Example Graph
select status open
Simple Queries
• Going from idea to finished graph is as
simple as these steps:
–
–
–
–
–
Specify report titles and axis styles
Select a set of tickets
Filter the selection further (temporarily)
Plot the filtered data onto the graph
Repeat, if desired, with different data
Graphing Data Points
• Now the selected (and filtered) tickets can
be plotted on a graph
• Each “tick” on the x axis is a “sample” or
“bucket” in which a set of qualifying tickets
will be gathered, and the value of tickets in
the sample bucket will be plotted to the y
axis.
Example Graph
WW46
Sample
Bucket
(REQUEST QUEUE ONLY)
820
90%
738
80%
656
70%
574
60%
492
50%
410
40%
328
30%
246
20%
164
10%
82
Made Commits
100%
0%
WW45 WW46 WW47 WW48 WW49 WW50 WW51 WW52 WW01 WW02 WW03 WW04 WW05 WW06
Line
Number of Tickets
WW45
Sample
Bucket
Constant
EC Made Commits
0
Work Week
TICKETS RESOLVED
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 14:10:23
% Made Commits
Goal
Legend
Bar
x Axis
Stacked Bar
Example Graph
Top 10 Root-Cause Categories by Volume
TOP-LEVEL PROBLEM TYPES
Fileserver/Disk Management
Mail
Sample
Bucket
Account Administration
WW06
Sub-Sample
Logistics
Applications/Commands
Microsoft Windows
Network
Sample
Bucket
WW05
Sub-Sample
UNIX Workstation/Xterminal
Request Administration
Mail/cc:Mail
Mobile Computing/Remote Access
Network/Internet
0
Tickets Resolved WW05 2000
TICKETS RESOLVED WW02 2000
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 15:21:01
50
100
150
200
Tickets Resolved WW06 2000
Tickets Resolved WW03 2000
250
300
350
400
450
Tickets Resolved WW04 2000
500
Example
Graph
Pie
Legend
Currently-Open Request Priorities
“medium”
Sample
Bucket
ALL QUEUES
URGENT (41)
high (104)
medium (439)
low (62)
“low”
Sample
Bucket
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 15:32:46
Graphing Data Points
graph style type attribute [options]
graph const value [options]
• Tabulates ticket data by scanning selected
tickets and adding their attributes to the
sample buckets
• Plots the resulting values of the samples on
the graph
Graph Styles
• The style parameter determines the type of
graph to be plotted:
bar
const
line
pie
stacked
• Note that “constant” line graphs have a
different syntax (just value and options)
Graph Types
• The type parameter specifies the type of
data to collect in each sample:
count all
• Total number of tickets in sample
count attrlist
• Total number of tickets in sample which had all of
the attributes in attrlist true during the sample period
– reopened requeued merged ontime overdue
active closed due new open pending
resolved stale
Graph Types
ratio attrlist
• Ratio of the number of tickets for which all the
attributes in attrlist were true, vs. the total number
of tickets in the sample.
• Same values for attrlist as with count
average sattr
• Average of the sattr values for all tickets in sample
sum sattr
• Sum of the sattr values for all tickets in sample
Graph Attributes
• The attribute parameter specifies the
attribute which assigns each ticket to a
sample bucket.
• If your x axis type is date-related, you need
to use a date attribute.
graph line count all resolved
Graph Options
-color list
• List of colors to use for graph elements.
• Use X color names or #rrggbb.
-label string
• Set the graph element label for the legend area.
-y2
• Plot to the y2 axis.
Graphing Multiple Elements
• Place one graph statement per element
you wish to appear on the same graph.
• When the graph is complete, end it:
end [-keep]
• Use the -keep option to keep the selected set of
tickets from the previous graph to use on the next
one. This avoids selecting the same tickets all over
again.
Example Graph
EC Made Commits
(REQUEST QUEUE ONLY)
90%
738
80%
656
70%
574
60%
492
50%
410
40%
328
30%
246
20%
164
10%
82
Made Commits
820
0%
WW45 WW46 WW47 WW48 WW49 WW50 WW51 WW52 WW01 WW02 WW03 WW04 WW05 WW06
Work Week
TICKETS RESOLVED
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 14:10:23
% Made Commits
Goal
0
Number of Tickets
100%
Example Graph
select resolved from \
”13 weeks ago” queue request
graph bar count all resolved \
-y2 -label ”TICKETS RESOLVED”
graph line ratio ontime \
resolved -label ”% Made Commits”
graph const .95 -label Goal
end
Example Graph
Currently-Open Request Priorities
ALL QUEUES
URGENT (41)
high (104)
medium (439)
low (62)
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 15:32:46
Example Graph
x values -sort -exec prioritySort
select status open
graph pie count all priority \
-color {red yellow green blue}
end
Hints
• Elements are plotted in the order specified.
– If you plot lines and bars together, plot the bars
first, so the lines will go “on top” of the bars
(otherwise they’re hard to see).
Stacked Bar Elements
• Mostly like bar elements, but each bar
contains “subsamples” showing proportions
of components making up the full bar
• For example, change the bar graph of
tickets resolved per week
– Each week’s bar broken into request priorities
– See how many of each priority were resolved
Stacked Bar Graph Options
-colormap {sampID color sampID color…}
• Map specific colors to specific subsamples in the
bars.
• Use X color names or #rrggbb.
-subattr attr
• Specify attribute used for subsamples in each bar.
-subvalues
• Use actual value of attr as the sample ID, rather than
a date-based translation of it.
Stacked Bar Graph Options
-sublabel labelspec
• Use this instead of -label to set legend keys
%h
%d
%H
%k
%K
%Q
%W
%U
%%
Month from sample ID (“Jan”, “Feb”, etc.)
Date from sample ID (“01”, “02”, etc.)
Hour from sample ID (“00”, “01”, etc.)
Intel work week from sample ID (“01”, “02”, etc.)
Intel work year from sample ID (“Jan”, “Feb”, etc.)
Quarter from sample ID (“1”, “2”, etc.)
Week number from sample ID (Monday-based)
Week number from sample ID (Sunday-based)
Percent sign (“%”)
Example Graph
select resolved from \
”13 weeks ago” queue request
graph stacked count all resolved \
-y2 -subattr priority -subvalues
graph line ratio ontime \
resolved -label ”% Made Commits”
graph const .95 -label Goal
end
Example Graph
EC Made Commits
(REQUEST QUEUE ONLY)
90%
738
80%
656
70%
574
60%
492
50%
410
40%
328
30%
246
20%
164
10%
82
Made Commits
820
0%
WW46 WW47 WW48 WW49 WW50 WW51 WW52 WW01 WW02 WW03 WW04 WW05 WW06
Work Week
urgent
% Made Commits
Goal
HIGH
low
medium
REQADM 1.2_BETA_P10 / GENERATED 05 FEB 2000 20:32:46
0
Number of Tickets
100%
Performance Hints
• Avoid selection criteria category and
type
• Unless you exclude closed requests, or ask
for at least one date-related criterion, or a
single non-wild-carded owner or submitter,
the client will have to select a larger number
of tickets to be downloaded and filtered at
the client side, which may take longer.
Performance Hints
• Avoid using the following ticket attributes:
body
cclist
fromname
owneruser
type
catdesc
eventlist
fromuser
requestedBy
category
full-eventlist
ownername
timescore
• Try to group similar charts together and
keep a common selection for all of them.
Advanced Queries
• Query language actually much more
advanced and flexible than we’ve shown
yet.
• Many more sophisticated kinds of charts
and queries possible, but require a bit more
work to get.
• Once working set is selected, you can
analyze any attributes in any manner, even
using external databases or services.
Query Syntax
• But first, let’s cover some ground rules
about what you can put into a query.
• Query is a series of statements.
• Statements are a series of words separated
by semicolons (“;”) or newlines.
• Words are separated from each other by
spaces and/or tabs.
Query Syntax
• If the first character of the first word in a
statement is an octothorpe1 (“#”), the
remainder of the line is considered to be a
comment and is ignored.
– Anyplace else they have no special meaning.
– To add a comment after a statement, separate
them with a semicolon:
title ”Q1 2000”; #set title for chart
1A.k.a.
“pound sign”, “sharp”, “pound”, “hash”, “tic-tac-toe”, etc. Betcha didn’t know it had an official name,
eh? It’s the only character name sillier than “ampersand” if you ask me...
Query Syntax
• Long statements may be continued on
multiple lines
• If the last character on a line is a backslash
(“\”), the newline doesn’t count as a
statement separator (just a normal word
delimiter).
• Beware:
– Must really be the last character (no spaces)
– Even comment lines are continued this way
Query Syntax
• Text strings (titles, attribute names, dates,
etc.) do not need to be in quotes unless they
contain special characters or spaces).
• Two kinds of quotes are available:
– ”Double quotes”
• Interpolation allowed
– {Curly braces}
• Literal text, no interpolation
• Great for list parameters (they can nest)
Query Syntax
• “Interpolation” allows substitution of
certain special symbols in a statement
before it is executed.
– $name interpolates the value of a variable
– [statement] executes the statement,
substituting it with the result it produces.
– \char is replaced by a special character:
• \n \b \r \t \[ \$ \\ \” etc.
Variables
• You may use variables to hold data
temporarily while building a query and
analyzing data.
set user ”steve”
set month 1
set tickets {12345 12370 12398}
• Read the value of a variable by interpolation:
set x $y
Conditionals
• Conditional execution via the “if” statement:
if expression statement [else statement]
if {$var < 200} {
# do something…
} else {
# do something else…
}
Other Flow Control
• All the standard programming features:
while expression statements
foreach var list statements
for setup expression increment statements
proc varlist statements
Naming Rules
• To avoid colliding with internal values used
by reqadm-report, don’t name your
procedures or variables:
– Anything starting with RA__
– TicketData
– TicketList
– date_adj
– Any other pre-defined name mentioned in the
reqadm-report documentation.
Advanced Axis Configuration
• You can customize the sampling of tickets
for the x axis by using these options to the x
statement:
-custom formula
• Alternative to the standard samples (“weekly”, etc)
• Formula is a series of characters indicating
conversions between the significant dates of the
tickets and sample bucket ID codes.
• Specific conversion codes detailed in documentation
Advanced Axis Configuration
-sort -exec statements
• Custom sorting specification. Statements (generally
the name of a defined procedure) executed with two
arguments; return value determines sort order of
those two arguments.
-sort -exec prioritySort
• Pre-defined routine to sort priority codes in order
from urgent to low.
Advanced Axis Configuration
-ticklabel -exec statements
• The statements are executed to determine the sample
label to be placed on each ticket.
• The following codes are expanded in the statements:
%# Replaced with the ticket number.
%S Replaced with the sample ID for this set of tickets.
Advanced Graphing
graph style type -sample statements
• The statements are executed to determine the sample
in which to place each ticket, rather than dropping
into sample buckets by date attribute.
• Return a list of sample IDs to which the ticket
should belong.
• The following code is expanded in the statements:
%# Replaced with the ticket number.
Advanced Graphing
sampleRange attrlist ticketnumber [options]
• Pre-defined statement to return a list of samples
during which the specified attributes in attrlist are
true.
• Options:
-all
-min dt
-max dt
All samples from submission to now.
The date dt is the earliest to consider.
The date dt is the latest to consider.
Advanced Graphing
-if statements
• Consider a ticket to belong to the sample only if the
statements return true.
• The following symbols are substituted in the
statements:
%# The ticket number being examined.
%S The current sample ID.
-value statements
• Rather than sample the actual attribute value,
execute the statements and use their return value as
the sampled value for the graph.
Advanced Graphing
-subsample statements
• For stacked bar elements, execute the statements to
determine which tickets belong in each subsample.
Built-In Procedures
requestAttr ticket attr [-date]
• Return the value of the specified attribute from the named
ticket.
• Attributes include:
body
catdesc [level]
category
cclist
closed
due
eventlist
full-eventlist
flags
from fromname fromuser
importance
is...
newnumber newqueue
number
ontime overdue
owner ownername owneruser
Built-In Procedures
prioritySort a b
• Used as a sort procedure.
• Returns a negative number if a is a priority name of
greater urgency than b, positive if a is lower than b, or
zero if they are equal.
Built-In Procedures
expandTokens pattern date
• Expand %-tokens in the pattern string based on the
supplied date value.
event attr list
• Extract the specified event attribute from the list of
values, such as those returned by [requestAttr
eventlist]
priorityLabel ticket attr
• Convert ticket’s priority to form suitable for use
with the -subsample graphing option.
Built-In Procedures
CONFIG tag
• Retrieve the value of the given configuration tag
from the reqadm.conf file on the server.
getSystemTypeList
firstSystemName type
nextSystemName
• Get lists of known platform types and hostnames
username id
fullname id
Convert ID number to username or full name.
Built-In Procedures
getOwnerList
getRotationList
getQueueList
• Get lists of known request owners, groups and
queues.
Example Graph
Top 10 Root-Cause Categories by Volume
TOP-LEVEL PROBLEM TYPES
Fileserver/Disk Management
Account Administration
Logistics
Applications/Commands
Microsoft Windows
UNIX Workstation/Xterminal
Request Administration
Mail/cc:Mail
Mobile Computing/Remote Access
Network/Internet
0
Tickets Resolved WW05 2000
TICKETS RESOLVED WW02 2000
REQADM 1.2_BETA_P10 / GENERATED 04 FEB 2000 15:21:01
50
100
150
200
Tickets Resolved WW06 2000
Tickets Resolved WW03 2000
250
300
350
400
450
Tickets Resolved WW04 2000
500
Example Graph
x values -top 10 -flip -sort -values
graph stacked count all -sample {
list [list [requestAttr %# {catdesc 1}]]
} -subcustom Kk -sublabel \
”Tickets Resolved WW%k %K” \
-subattr resolved
end
Another Example
• Measure “first touch” resolution: how many
tickes were only assigned once?
• For each ticket, retrieve its event history
and count how many assignments you find.
First Touch Resolution
title ”First Touch Resolution”
subtitle ”(Assigned to only 1 non-group)”
proc 1st_touch ticket {
set count 0
foreach e [requestAttr $ticket eventlist] {
if {[event type $e] == ”assignment”
&& [string range [event towhom $e] 0 0] != ”G”} {
incr count
}
}
return [expr $count < 2]
}
First Touch Resolution
x iweekly -label ”Work Week”
y % -label ”First Touch”
y2 -label ”Request Tickets”
select resolved from {13 weeks ago}
graph line ratio all resolved -if {1st_touch $#} \
-label ”% First Touch”
graph line count all resolved \
-label ”Total Resolved Tickets” -y2
graph const .9 -label Goal
end
First Touch Resolution
First Touch Resolution
(ASSIGNED TO ONLY 1 NON-GROUP)
90%
846
80%
752
70%
658
60%
564
50%
470
40%
376
30%
282
20%
188
10%
94
First Touch
940
0%
WW46
WW47
WW48
WW49
WW50
WW51
WW52
WW01
WW02
WW03
Work Week
% FIRST TOUCH
REQADM 1.2_BETA_P10 / GENERATED 05 FEB 2000 20:43:05
Total Resolved Tickets
Goal
WW04
WW05
0
WW06
Request Tickets
100%
Employee Statistics
• For each system administrator, what is the
frequency of tickets they resolve on time?
• Set up a loop of each request owner, and
plot a new graph for each, showing total
tickets resolved by them and how many
were on time.
Employee Statistics
Select resolved between 1/1/99 and 1/1/00
foreach owner [getOwnerList] {
set wwid [lindex $owner 0]
title ”1999 Request Statistics”
subtitle [fullname $wwid]
x iweekly -label {Work Week}
y -label {Tickets Resolved}
filter owner $wwid
graph bar count all resolved -color blue \
-label {Total tickets resolved}
graph line count overdue resolved -color red \
-label {Tickets resolved late}
end -keep
}
Employee Statistics
1999 Request Statistics
J. RANDOM PENCILPUSHER
130
117
104
Tickets Resolved
91
78
65
52
39
26
13
0
WW02 WW03 WW04 WW05 WW06 WW07 WW08 WW09 WW10 WW11 WW12 WW13 WW14 WW15 WW16 WW17 WW18 WW19 WW20 WW21 WW22 WW23 WW24 WW25 WW26 WW27 WW28 WW29 WW30 WW31 WW32 WW33 WW34 WW35 WW36 WW37 WW38 WW39 WW40 WW41 WW42 WW43 WW44 WW45 WW46 WW47 WW48 WW49 WW50 WW51 WW52 WW01
Work Week
TOTAL TICKETS RESOLVED
REQADM 1.2_BETA_P10 / GENERATED 24 JAN 2000 18:49:35
Tickets resolved late
Raw Data Dumps
• Rather than analyzing the ticket data, you
can simply “dump” fields of your selected
tickets to a CSV file.
• Import to spreadsheets or other reporting
tools for further analysis
• First, select a working set of tickets in the
usual way.
Raw Data Dumps
• Then, rather than graphing anything, simply
use the dump statement:
dump reportname [options]
-comments
– Include comments in output file describing contents
-events
– Include the ticket event history in the CSV file
-fields list
– Specify which fields to export for each ticket
-text
– Include full request body text in CSV file
Raw Data Dumps
• No sampling or analysis is done, so no axis
setup statements.
• Example:
select status open
dump rep1 -fields {queue number
priority owner due} -comments
end
Raw Data Dumps
##########################################################
#GENERATED-BY: REQADM 1.2_beta_p10 / Generated 05 Feb 2000
#TITLE:
#SUBTITLE:
#DATASET: rep1
#FIELDS: queue number priority owner due
##########################################################
request,126456,medium,P10072347,02/03/2000 14:14:55
request,126142,high,G2027,01/27/2000 16:13:45
request,108636,medium,P10580084,10/05/1999 23:59:59
request,119806,low,P10566404,11/20/1999 11:45:37
project,31628,medium,P10503190,09/08/1997 18:21:54
Documentation Available
• Manpages
– reqadm-report(1)
• Full description of the tool and query language
– reqadm-report(5)
• Description of file formats produced by reqadmreport.
– Also available under “manpages” in the
REQADM web help area.
• Tcl language reference books.