powershell - 带有 Powershell 的 Power BI 推送数据集;如何在单个 HTTP 调用中推送多个值/对象?
问题描述
我有一个 powershell 脚本,它查询数据库以检索具有多个不同列的单行。我需要在每次添加到数组的多个服务器上运行此查询,并最终构建一个 json 对象,其中 json 中的每个对象都包含 4 列:
- 日期
- 时间
- 总阻塞时间
- 服务器名称
在每个循环中为单个服务器调用 rest 方法时,我可以成功地让它工作,但我需要先获取每个服务器的所有数据,然后通过调用 Invoke-RestMethod 推送 JSON 对象数组。
$endpoint = "https://api.powerbi.com/MyEndpoint"
$ServerList = ('localhost', 'dev')
while($true)
{
foreach ($Server in $ServerList)
{
$QueryResults = Invoke-DbaQuery -SqlInstance $Server -Database "Master" -Query "
SELECT
SUM(wt.wait_duration_ms) as TotalBlockTime, @@SERVERNAME as ServerName
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
"
Write-Host 'Query Executed'
$TotalBlockTime = $QueryResults.TotalBlockTime
$ServerName = $QueryResults.ServerName
$Date = Get-Date -DisplayHint Date -Format MM/dd/yyyy
$Time = Get-Date -DisplayHint Time -Format HH:mm:ss
}
Write-Host 'Building Payload'
$payload = @{
"Date" = $Date
"Time" = $Time
"TotalBlockTime" = $TotalBlockTime
"ServerName" = $ServerName
}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))
write-host $payload.TotalBlockTime $payload.ServerName
Write-Host "Date: " $Date " Time: " $Time " TotalBlockTime: " $TotalBlockTime " ServerName: " $ServerName
sleep 2
}
解决方案
您必须将来自每个服务器的查询结果累积在某个东西(例如数组)中,然后按照文档中的指定构造一个 JSON,其中包含一个属性rows
,它是一个值数组:
{
"rows": [
{
"ProductID": 1,
"Name": "Adjustable Race",
"Category": "Components",
"IsCompete": true,
"ManufacturedOn": "07/30/2014"
},
{
"ProductID": 2,
"Name": "LL Crankarm",
"Category": "Components",
"IsCompete": true,
"ManufacturedOn": "07/30/2014"
},
{
"ProductID": 3,
"Name": "HL Mountain Frame - Silver",
"Category": "Bikes",
"IsCompete": true,
"ManufacturedOn": "07/30/2014"
}
]
}
如果我修改你的代码,它可能是这样的:
$endpoint = "https://api.powerbi.com/MyEndpoint"
$ServerList = ('localhost', 'dev')
while($true)
{
$ServerResults = @() # Results from each of the servers will be stored here
foreach ($Server in $ServerList)
{
$QueryResults = Invoke-DbaQuery -SqlInstance $Server -Database "Master" -Query "
SELECT
SUM(wt.wait_duration_ms) as TotalBlockTime, @@SERVERNAME as ServerName
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
"
Write-Host 'Query Executed'
$TotalBlockTime = $QueryResults.TotalBlockTime
$ServerName = $QueryResults.ServerName
$Date = Get-Date -DisplayHint Date -Format MM/dd/yyyy
$Time = Get-Date -DisplayHint Time -Format HH:mm:ss
Write-Host 'Building Payload'
$row = @{
"Date" = $Date
"Time" = $Time
"TotalBlockTime" = $TotalBlockTime
"ServerName" = $ServerName
}
Write-Host $row.TotalBlockTime $row.ServerName
Write-Host "Date: " $Date " Time: " $Time " TotalBlockTime: " $TotalBlockTime " ServerName: " $ServerName
$ServerResults += $row
}
$payload = @{ rows = $ServerResults }
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json $payload)
Write-Host "Date: " $Date " Time: " $Time " TotalBlockTime: " $TotalBlockTime " ServerName: " $ServerName
sleep 2
}
推荐阅读
- sql-server - 如何在使用 Logstash 从 SQL 迁移到 Elasticsearch 时过滤数据以获取 JSON 对象中的嵌套字符串数组
- r - 如何在面板数据集中包含定义总观察值的变量?
- javascript - 如何使用javascript过滤和获取限制数量的数据
- python - 在 python 脚本中使用 PyInstaller 或其他 python 命令
- java - 在没有ffmpeg的.mov视频中提取透明png
- powershell - 如何使用 PowerShell 替换/更新 LDAP 目录中的属性值?
- c++ - C ++如何将输出保存到文件中
- r - UseMethod(“required_pkgs”)中的错误:没有适用于“required_pkgs”的方法应用于“工作流”类的对象
- powershell - Powershell 到 .vbs
- typescript - 如何使用联合类型深度展平 Typescript 接口并将完整的对象路径保留在键中