.TH "deltadb_query" 1 "" "CCTools 7.1.2 FINAL" "Cooperative Computing Tools"

.SH NAME
.LP
\fBdeltadb_query\fP - query historical data stored by the catalog server.

.SH SYNOPSIS
.LP
\FC\fBdeltadb_query --db [source_directory] --from [starttime] --to [endtime] [--filter [expr]] [--where [expr]] [--output [expr]]\fP\FT

.SH DESCRIPTION
.LP

\fBdeltadb_query\fP is a tool that examines and displays historical data of the catalog server.
(If given the -H option, the catalog server will record historical data in a format
known as DeltaDB, hence the name of this tool.)
The results can be displayed as a stream of time-ordered updates
or as summaries of properties across all records over time.
This is useful for reporting, for example, the total resources and clients
served by a large collection of servers over the course of a year.

A paper entitled DeltaDB describes the operation of the tools in detail (see reference below).

.SH ARGUMENTS
.LP
.LP
.TP
.B \ --db path
.
 Query this database directory.
.TP
.B \ --file path
.
 Query the data stream in this file.
.TP
.B \ --from time
.
 (required) The starting date and time of the query in an absolute time like "YYYY-MM-DD HH:MM:SS" or "YYYY-MM-DD" or a relative time like 5s, 5m, 5h, 5d to indicate five seconds, minutes, hours, or days ago, respectively.
.TP
.B \ --to time
.
 The ending time of the query, in the same format as the --from option.  If omitted, the current time is assumed.
.TP
.B \ --every interval
.
 The intervals at which output should be produced, like 5s, 5m, 5h, 5d to indicate five seconds, minutes, hours, or days ago, respectively.
.TP
.B \ --epoch
.
 Causes the output to be expressed in integer Unix epoch time, instead of a formatted time.
.TP
.B \ --filter expr
.
 (multiple) If given, only records matching this expression will be processed.  Use --filter to apply expressions that do not change over time, such as the name or type of a record.
.TP
.B \ --where expr
.
  (multiple) If given, only records matching this expression will be displayed.  Use --where to apply expressions that may change over time, such as load average or storage space consumed.
.TP
.B \ --output expr
.
 (multiple) Display this expression on the output.


.SH EXAMPLES
.LP

To show 1 week worth of history starting on 15 April 2013:

.fam C
.nf
.nh
.IP "" 8
% deltadb_query --db /data/catalog.history --from 2013-04-15 --to 2015-04-22
.fi
.hy
.fam
.P

To show all history after 1 March 2013:

.fam C
.nf
.nh
.IP "" 8
% deltadb_query --db /data/catalog.history --from 2013-03-01
.fi
.hy
.fam
.P

To show the names of fred's servers where load5 exceeds 2.0:

.fam C
.nf
.nh
.IP "" 8
% deltadb_query --db /data/catalog.history --from 2013-03-01 --filter 'owner=="fred"' --where 'load5>2.0' --output name --output load5
.fi
.hy
.fam
.P

To show the average load of all servers owned by fred at one hour intervals:

.fam C
.nf
.nh
.IP "" 8
% deltadb_query --db /data/catalog.history --from 2013-03-01 --filter 'owner=="fred"' --output 'AVERAGE(load5)' --every 1h
.fi
.hy
.fam
.P

The raw event output of a query can be saved to a file, and then queried using the --file option, which can accelerate operations on reduced data.  For example:

.fam C
.nf
.nh
.IP "" 8
% deltadb_query --db /data/catalog.history --from 2014-01-01 --to 2015-01-01 --filter 'type=="wq_master"' > wq.data
% deltadb_query --file wq.data --from 2014-01-01 --output 'COUNT(name)' --output 'MAX(tasks_running)'
.fi
.hy
.fam
.P

.SH COPYRIGHT
.LP

The Cooperative Computing Tools are Copyright (C) 2005-2019 The University of Notre Dame.  This software is distributed under the GNU General Public License.  See the file COPYING for details.

.SH SEE ALSO
.LP

.IP \(bu 4
\fBCooperative Computing Tools Documentation\fP
.IP \(bu 4
\fBcatalog_server(1)\fP  \fBcatalog_update(1)\fP  \fBcatalog_query(1)\fP  \fBchirp_status(1)\fP  \fBwork_queue_status(1)\fP   \fBdeltadb_query(1)\fP