GroupBy Query PowerShell Examples
Now that you understand the Intersight Telemetry API, you might want to extract inforamtion via Python. All the information from the previous articles is still relevant. If you want to learn more about the Telemetry APIs in general, please review the previous articles. This article will focus on applying that knowledge to the Intersight PowerShell Module.
Installation and Authentication
You will have to install the PowerShell Module on your machine for all examples below. Please have a look at the Intersight Developer Center Downloads for information on how to install the module.
Once you have the module installed, you will need to write some code to handle authentication. Authentication for the Telemetry APIs is handled like it is for any other API in Intersight. You will have to get an API Key and Secret Key from Intersight. If you are unfamiliar with this process, you can review the Intersight PowerShell Module documentation on authentication. All the examples in this section will assume that authentication is already created and will not include the code for it.
Request structure
For PowerShell, we can just use the JSON request structure from the API, thus there is no difference from a normal API request. We just need to wrap everything into the PowerShell structure of the Intersight module.
Basic query
Often you just want to query data for a certain instrument. This is actually quite simple and will look something like this:
$query = @"
{
"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"
}]
}
"@
Write-Output (New-IntersightManagedObject -ObjectType telemetry.TimeSerie -AdditionalProperties ($query | ConvertFrom-Json -AsHashTable) | ConvertFrom-Json)
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 timestamp event
------- --------- -----
v1 20.02.2024 12:00:00 @{hw-fan-speed_max-Max=19110}
v1 21.02.2024 00:00:00 @{hw-fan-speed_max-Max=19565}
ADDITIONAL RESULTS ARE HIDDEN
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:
$query = @"
{
"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"
}]
}
"@
Write-Output (New-IntersightManagedObject -ObjectType telemetry.TimeSerie -AdditionalProperties ($query | ConvertFrom-Json -AsHashTable) | ConvertFrom-Json)
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 timestamp event
------- --------- -----
v1 20.02.2024 12:00:00 @{hw-fan-speed_max-Max=19110}
v1 21.02.2024 00:00:00 @{hw-fan-speed_max-Max=19110}
ADDITIONAL RESULTS ARE HIDDEN
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:
$query = @"
{
"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"
}]
}
"@
Write-Output (New-IntersightManagedObject -ObjectType telemetry.TimeSerie -AdditionalProperties ($query | ConvertFrom-Json -AsHashTable) | ConvertFrom-Json)
This looks like a very minor change, but the result actually changes drastically based on this:
version timestamp event
------- --------- -----
v1 20.02.2024 12:00:00 @{host.name=Yosemite FI-A; hw-fan-speed_max-Max=7317}
v1 20.02.2024 12:00:00 @{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:
$query = @"
{
"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"
}]
}
"@
Write-Output (New-IntersightManagedObject -ObjectType telemetry.TimeSerie -AdditionalProperties ($query | ConvertFrom-Json -AsHashTable) | ConvertFrom-Json)
This query will retrieve the first two two results from the dataset, thus your result will look something like this:
version timestamp event
------- --------- -----
v1 20.02.2024 12:00:00 @{host.name=Yosemite FI-A; hw-fan-speed_max-Max=7317}
v1 20.02.2024 12:00:00 @{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:
$query = @"
{
"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\")"
}]
}
"@
Write-Output (New-IntersightManagedObject -ObjectType telemetry.TimeSerie -AdditionalProperties ($query | ConvertFrom-Json -AsHashTable) | ConvertFrom-Json)
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 timestamp event
------- --------- -----
v1 20.02.2024 12:00:00 @{hw-fan-speed-Avg=7509; hw.fan.speed-Sum=144815919; count=19284}
v1 21.02.2024 00:00:00 @{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.