GroupBy Query API Examples
Now that you understand how you can structure queries, this final section will show a few example queries:
- Basic data retrieval
- Query with a filter
- Query with a grouping
- Query with TopN limit
- Query with secondary aggregation
Just as a brief reminder, all of these API requests are going to use the /api/v1/telemetry/TimeSeries endpoint. All of the requests below are just examples. Please make sure to adjust the time interval to a time where you know that data will be available. If your response is empty, please verify that there is actually data available by checking the metrics tab of your devices using the UI.
Basic query
Often you just want to query data for a certain instrument. This is actually quite simple and will look something like this:
{
"queryType": "groupBy",
"dataSource": "PhysicalEntities",
"granularity": {
"type": "period",
"period": "PT12H",
"timeZone": "Europe/Vienna"
},
"intervals": ["2024-02-20T19:47:00.000Z/2024-02-28T19:47:00.000Z"],
"dimensions": [],
"filter": {
"type": "and",
"fields": [{
"type": "selector",
"dimension": "instrument.name",
"value": "hw.fan"
}]
},
"aggregations": [{
"type": "longMax",
"name": "hw-fan-speed_max-Max",
"fieldName": "hw.fan.speed_max"
}]
}
This query retrieves results related to the Fan - the attribute "instrument.name" filters for "hw.fan". To get an output value, the aggregation is added to calculate the maximum across all entries for a time period. The value used for this calculation is "hw.fan.speed_max". This means that for the 12 hour time period used in the query, Intersight will return the highest value that can be found in the "hw.fan.speed_max" field. That means that within the 7 day time period defined in the interval there should be 14 results returned, which will result in an output like this:
[
{
"version": "v1",
"timestamp": "2024-02-20T12:00:00.000+01:00",
"event": {
"hw-fan-speed_max-Max": 19110
}
},
{
"version": "v1",
"timestamp": "2024-02-21T00:00:00.000+01:00",
"event": {
"hw-fan-speed_max-Max": 19565
}
},
ADDITIONAL RESULTS ARE HIDDEN
]
As you can see, there is only a single output per period even if the Intersight account has a lot of devices reporting data. The first timestamp might not line up exactly with the start of the interval you provided, as the data needs to be aggregated based on what is available in the backend. With this query as a template, you could easily modify time intervals, periods, selected metrics, and attributes that you want to query.
Filters
The previous example retrieves the maximum value for all results in a time period. If we wanted to only retrieve the maximum value for one device type, we would have to add an additional filter. Let's say we want to filter for one specific model of server. This would result in a query like this:
{
"queryType": "groupBy",
"dataSource": "PhysicalEntities",
"granularity": {
"type": "period",
"period": "PT12H",
"timeZone": "Europe/Vienna"
},
"intervals": ["2024-02-20T19:47:00.000Z/2024-02-28T19:47:00.000Z"],
"dimensions": [],
"filter":{
"type":"and",
"fields":[
{
"type": "selector",
"dimension": "instrument.name",
"value": "hw.fan"
},
{
"type": "selector",
"dimension": "model",
"value": "UCSC-C220-M6S"
}
]
},
"aggregations": [{
"type": "longMax",
"name": "hw-fan-speed_max-Max",
"fieldName": "hw.fan.speed_max"
}]
}
In addition to the basic query, this also adds a filter that reduces the amount of rows that will be retrieved. Thus the maximum is only calculated on rows of data that have been retrieved from "UCSC-C220-M6S" servers. Thus the result will look slightly different even though the query was performed on the same account:
[
{
"version": "v1",
"timestamp": "2024-02-20T12:00:00.000+01:00",
"event": {
"hw-fan-speed_max-Max": 19110
}
},
{
"version": "v1",
"timestamp": "2024-02-21T00:00:00.000+01:00",
"event": {
"hw-fan-speed_max-Max": 19110
}
},
ADDITIONAL RESULTS ARE HIDDEN
]
You could add additional filters or modify this filter by adding more attributes to it based on your requirements. This will allow you to retrieve data only for those elements that you really need information for.
GroupBy clauses
The basic query retrieves one result for all endpoints. If you wanted to get results for certain groups of endpoints, or even individual endpoints, you would have to adjust the query. This can be achieved by adding "dimensions". Let's say we want to get the maximum fan speed for each host. The query for this would look something like this:
{
"queryType": "groupBy",
"dataSource": "PhysicalEntities",
"granularity": {
"type": "period",
"period": "PT12H",
"timeZone": "Europe/Vienna"
},
"intervals": ["2024-02-20T19:47:00.000Z/2024-02-28T19:47:00.000Z"],
"dimensions": ["host.name"],
"filter": {
"type": "and",
"fields": [{
"type": "selector",
"dimension": "instrument.name",
"value": "hw.fan"
}]
},
"aggregations": [{
"type": "longMax",
"name": "hw-fan-speed_max-Max",
"fieldName": "hw.fan.speed_max"
}]
}
This looks like a very minor change, but the result actually changes drastically based on this:
[
{
"version": "v1",
"timestamp": "2024-02-20T12:00:00.000+01:00",
"event": {
"host.name": "Yosemite FI-A",
"hw-fan-speed_max-Max": 7317
}
},
{
"version": "v1",
"timestamp": "2024-02-20T12:00:00.000+01:00",
"event": {
"host.name": "Yosemite FI-B",
"hw-fan-speed_max-Max": 8244
}
},
ADDITIONAL RESULTS ARE HIDDEN
]
As you can see, we now have multiple results for the same timestamp. All unique combinations of dimensions will result in one result per timestamp. In our case we chose the Hostname as a dimension, thus we get one result per host and timestamp. You could use any type of grouping to get the output that is most helpful for you.
TopN requests
Sometimes you might want to understand if there are any outliers or problems in your environment. For that purpose, the highest or lowest values are usually most interesting. If you ran a request that retrieved one datapoint per server and timestamp, you could have results with 1000s of lines. To make the whole thing more manageable, we can limit the result set to only the top few datapoints. This often makes sense if you already have a GroupBy clause in the query. You can then limit the number of output rows like this:
{
"queryType": "groupBy",
"dataSource": "PhysicalEntities",
"granularity": {
"type": "period",
"period": "PT12H",
"timeZone": "Europe/Vienna"
},
"intervals": ["2024-02-20T19:47:00.000Z/2024-02-28T19:47:00.000Z"],
"dimensions": ["host.name"],
"limitSpec": {
"type": "default",
"limit": 2,
"offset": 0,
"columns": []
},
"filter": {
"type": "and",
"fields": [{
"type": "selector",
"dimension": "instrument.name",
"value": "hw.fan"
}]
},
"aggregations": [{
"type": "longMax",
"name": "hw-fan-speed_max-Max",
"fieldName": "hw.fan.speed_max"
}]
}
This query will retrieve the first two two results from the dataset, thus your result will look something like this:
[
{
"version": "v1",
"timestamp": "2024-02-20T12:00:00.000+01:00",
"event": {
"host.name": "Yosemite FI-A",
"hw-fan-speed_max-Max": 7317
}
},
{
"version": "v1",
"timestamp": "2024-02-20T12:00:00.000+01:00",
"event": {
"host.name": "Yosemite FI-B",
"hw-fan-speed_max-Max": 8244
}
}
]
Notice that there are only two results and no further datapoints. This does also not select the maximum, it just selects the first N requests from the regular results. Ordering would normally be available via the "columns" parameter. This is currently not supported in Intersight.
Post-processing of results
Sometimes you might want to calculate a value from multiple values you retrieved through the request. This can be achived by an additional level of processing through "postAggregations". A typical use case for this would be calculating an average value by dividing the sum of all datapoints by the count of all elements. This could look something like below:
{
"queryType": "groupBy",
"dataSource": "PhysicalEntities",
"granularity": {
"type": "period",
"period": "PT12H",
"timeZone": "Europe/Vienna"
},
"intervals": ["2024-02-20T19:47:00.000Z/2024-02-28T19:47:00.000Z"],
"dimensions": [],
"filter": {
"type": "and",
"fields": [{
"type": "selector",
"dimension": "instrument.name",
"value": "hw.fan"
}]
},
"aggregations": [
{
"type": "longSum",
"name": "count",
"fieldName": "hw.fan.speed_count"
},
{
"type": "longSum",
"name": "hw.fan.speed-Sum",
"fieldName": "hw.fan.speed"
}
],
"postAggregations": [{
"type": "expression",
"name": "hw-fan-speed-Avg",
"expression": "(\"hw.fan.speed-Sum\" / \"count\")"
}]
}
As you can see in this example, we are retrieving two values via aggregations: the sum and the count. Then we use the post aggregation to calculate a new result by dividing the sum with the count. This results in an output like this:
[
{
"version":"v1",
"timestamp": "2024-02-20T12:00:00.000+01:00",
"event":{
"hw-fan-speed-Avg": 7509,
"hw.fan.speed-Sum": 144815919,
"count": 19284
}
},
{
"version":"v1",
"timestamp": "2024-02-21T00:00:00.000+01:00",
"event":{
"hw-fan-speed-Avg": 7576,
"hw.fan.speed-Sum": 545500500,
"count": 72000
}
},
ADDITIONAL RESULTS ARE HIDDEN
]
This output contains the two values we requested as well as the value computed from them. There are a lot of computation options available if you want to achieve a different goal.