| Table of Contents |
|---|
...
| Code Block |
|---|
{
"parameters" : {
"time_period" : "time_difference",
"end_date" : "7-Mar-2014 14:03:01",
"start_date" : "7-Mar-2014 13:48:01"
},
"datasets" : [
{
"parameters" : {
"aggregation_function" : 1,
"lineType" : "column",
"query" : "select * from nodes",
"value_column" : "sum",
"axis" : "0",
"groupby" : [
"group_column"
]
},
"data_source" : "local_mysql",
"options" : {
"datasetTitle" : "Groups"
},
"name" : "sqlquery_dataset",
"typemodel" : "sql_query"
}
],
"options" : {
"titleText" : ""
},
"name" : "SQL Test",
"typemodel_view" : "non-time-chart"
}, |
Dataset:
...
| Code Block |
|---|
{
"parameters" : {
"query" : "select * from nodes",
"groupby" : [
"group_column"
],
"aggregation_function" : 1,
"value_column" : "unused",
"lineType" : "column",
"axis" : "0",
},
"data_source" : "local_mysql",
"options" : {
"datasetTitle" : "Groups"
},
"name" : "sqlquery_dataset",
"typemodel" : "sql_query"
} |
Breaking this down:
...
The SQL to run. This SQL can contain almost anything you would like.
NOTES:
- do not use single quotes, please use escaped double quotes, eg: \"value\" instead of 'value'
- do not use SELECT *, specify the columns you need, * will not work.
query subsitutions
Currently supported subsitutions are:
...
Example: SELECT * FROM interface WHERE UNIX_TIMESTAMP(lastUpdate) >= time.start AND UNIX_TIMESTAMP(lastUpdate) < time.end AND customerName = user.customer
groupby:
If the data returned from the SQL statement needs to be grouped (for summing or counting, works much like SQL GROUP BY) use this field to specify the group, as an array, order matters. This works in tandem with the aggregation function to produce results. Just like in an SQL GROUP BY each column requires a function to aggregate it's result.
...
this is of little consequence, functionally not used at all right now but a way for you to name the dataset for later recognition.
model (formerly 'type'):
must be sql_query for the above parameters to work.
...
The name is the unique name that identifies this chart when using it elsewhere (like creating a dashboard). What is type model_view for?
model_view (also formerly 'type')
"typemodel_view" : "non-time-chart"
"typemodel_view" : "graph"
2 options exist for this. "non-time-chart" means the data will not be an "over time" graph, but a snapshot of the data at a specific time. This is the most likely candidate for an SQL chart. "graph" is a data over time view, the time base is in unix epoc, the SQL query must return the time column in this format and "time_column" must be specified in the dataset telling it what the column is that holds the time value.
...