Use SquaredUp's Unified data query API to request tile data

Jelly · April 5, 2024, 11:47am

SquaredUp Cloud’s Enterprise tier has a built-in Unified Data Query API which allows you to use the platform as a gateway to data stored in other tools.

A common request is to be able to use this API to pull data from SquaredUp’s tiles.

The PowerShell script below does exactly this!

You will need the following:

An API token generated from Settings > Advanced > API
The IDs for the Workspace and Dashboard the tile belongs in, as well as the tile itself and the datastream that pulls the data in the tile. These can be gained via the API (docs here: SquaredUp API Docs), but I’ve placed some manual steps at the end of this post

In the script, you’ll also need to specify the startTime. AddHours is used in the sample, but AddDays will also work.

The Script

## API URI - Select correct URI for your SquaredUp region 
## US Region
$baseUrl = 'https://api.squaredup.com/api'
## EU Region
# $baseUrl = 'https://eu.api.squaredup.com/api'


## Define token generated in SquaredUp
# Format: apiKey=xxx
$token = 'apiKey=xxx'


## Define our workspace, dashboard, tile, and datastream IDs
# workspaceID - Format: space-xxx
# dashboardID - Format: dash-xxx
# datastreamID - Format: datastream-xxx
# tileID - Format: 456e2419-8a40-492f-9fde-b0bf386bc4c2 (guid)
$workspaceId = 'space-xxx'
$dashboardId = 'dash-xxx' 
$datastreamId = 'datastream-xxx'
$tileId = 'guid'



## Define timeframe
# Change startTime AddHours(-xx) as required
$startTime = (Get-Date).AddHours(-12).ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.fffK") 
$endTime = (Get-Date).ToUniversalTime().ToString("yyyy-MM-ddTHH:mm:ss.fffK")


## Get the dashboard JSON
$dashboardUri = "$($baseUrl)/dashboards/$($dashboardId)?$($token)"
$dashboard = Invoke-RestMethod -Method Get -Uri $dashboardUri
$json = $dashboard.content.contents


# Pick out the other necessary values from the Tile
$tile = $json | Where-Object { $_.i -eq $tileId }


## Build the data stream request body
$dataStreamBody = [PSCustomObject]@{
    "dataStreamId"   = $datastreamId;
    "pluginConfigId" = $tile.config.dataStream.pluginConfigId;
    "scope"          = $tile.config.scope;
    "timeframe"      = @{
        "start" = $startTime;
        "end"   = $endTime
    };
    "options" = @{
        "group" = @{
            "keyColumns"   = @();
            "aggregations" = @();
        };
        "sort"  = @{
            "criteria" = @();
        };
    };
} | ConvertTo-Json -Depth 10


## Call the data stream API
$dataStreamUri = "$($baseUrl)/datastreams/requests?workspaceId=$($workspaceId)&$($token)"
$dataStream = Invoke-RestMethod -Method Post -Uri $dataStreamURI -Body $dataStreamBody -ContentType "application/json"


## Output the raw data
# $dataStream.rows


## Find the indices of the necessary columns within $dataStream.metadata.columns
$columnIndices = @{}
$i = 0
while ($i -lt $dataStream.metadata.columns.Count) {
    if ($dataStream.metadata.columns[$i].role -eq "label" -or $dataStream.metadata.columns[$i].shapeName -eq "shape_string") {
        $columnIndices["label"] = $i
    }
    if ($dataStream.metadata.columns[$i].role -eq "timestamp" -or $dataStream.metadata.columns[$i].shapeName -eq "shape_date") {
        $columnIndices["timestamp"] = $i
    }
    if ($dataStream.metadata.columns[$i].role -eq "value" -or $dataStream.metadata.columns[$i].valueShapeName -eq "shape_number") {
        $columnIndices["value"] = $i
    }
    if ($null -ne $columnIndices["label"] -and $null -ne $columnIndices["timestamp"] -and $null -ne $columnIndices["value"]) {
        break
    }
    $i++
}


## Map the data based on the column indices
$result = $dataStream.rows | ForEach-Object {
    $row = $_
    $mappedData = [PSCustomObject]@{
        label     = $row[$columnIndices["label"]].value
        timestamp = [System.DateTime]::Parse($row[$columnIndices["timestamp"]].value)
        value     = [decimal]::Parse($row[$columnIndices["value"]].value)
    }
    $mappedData
}


## Output the clean result
$result

There are two sections at the end of the script (Find the indices and Map the data) that aren’t required. SquaredUp formats the result for use in the dashboards, whereas the Raw data is as it comes from the underlying tool. The two blocks present the data in a user-friendly table using the formatted values, rather than the raw data. I’ve commented out a line to output the raw data once it’s been retrieved.

An example of the clean output from a Pingdom Response Time data stream:

And the same raw data:

Grabbing all the IDs:

You’ll need to grab the IDs for your Workspace by first clicking on the Workspace in the nav (1) which takes you to the workspace home, and grabbing the space-xxx from the URL (2):

The Dashboard ID can be found by viewing a dashboard and grabbing dash-xxx from the URL (2) :

The tile and datastream IDs can be pulled from the json editor in a dashboard, which can be found in the top right of any dashboard under the … button:

Copying this out to a text editor, search for the tile name you’re interested in pulling data from, and you can find all of the relevant information for the tile. The tile ID sits under i (1) and the data stream ID can be found in the dataStream block (2):

The first tile added to a dashboard is simply “1” and every tile added afterwards is a GUID, so don’t be surprised if you see this!

Let us know if this is useful and what you are connecting to!