Hello Everyone,
I want to pull metric data out of vRops in CSV, I found getStatsOfResourcesCSV but cannot get it to work... the file comes out in either JSON or XML but never in CSV.
The documentation isn't really clear to me... does anyone know how I can CALL it to output the data in CSV?
Part of the script.
$ContentType = "application/json;charset=utf-8"
$header = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
#For JSON output
$header.Add("Accept", 'application/json')
#For XML output
#$header.Add("Accept", 'application/xml')
Invoke-RestMethod -Method GET -uri "https://192.168.0.3/suite-api/api/resources/stats?resourceId=UUID1&resourceId=UUID2&statKey=cpu|cost..." -Credential $cred -ContentType $ContentType -Headers $header -OutFile 'Output.csv'
Hi
Did a blog post around vRops API in general vRops API consumed with Powershell - Michael Ryom
I haven't played with getStatsOfResourcesCSV and haven't had time to do it - But in general in vRops API you have to specify format - If you look at my blog post you can see I used "&format=csv" to get data out as csv insted of xml (I belive you can also do json this way).
Hope this helps
yes i also tried the Accept Header 'text/csv'... but it didnt give me a CSV format I would expect...
$header.Add("Accept", 'text/csv')
Hi
Did a blog post around vRops API in general vRops API consumed with Powershell - Michael Ryom
I haven't played with getStatsOfResourcesCSV and haven't had time to do it - But in general in vRops API you have to specify format - If you look at my blog post you can see I used "&format=csv" to get data out as csv insted of xml (I belive you can also do json this way).
Hope this helps
Thanks for replying.
Yep that works but gives me the same CSV format as using '$header.Add("Accept", 'text/csv')' which means I need to post process it for the typical format i like to consume CSV's in...
Thanks
Ed
So i wrote this little script to collect the data and output it to CSV I'm my desired format... any suggestions to split the timestamp and value fields further and marry up the values correctly?
#Take all certs.
add-type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
public bool CheckValidationResult(
ServicePoint srvPoint, X509Certificate certificate,
WebRequest request, int certificateProblem) {
return true;
}
}
"@
[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy
#Variables
$username = "admin"
$password = "password"
$secPw = ConvertTo-SecureString $password -AsPlainText -Force
$cred = New-Object PSCredential -ArgumentList $username,$secPw
$ContentType = "application/xml;charset=utf-8"
$header = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$header.Add("Accept", 'application/xml')
Invoke-RestMethod -Method GET -uri "https://192.168.0.3/suite-api/api/resources/stats?resourceId=1ef459e5-789e-446b-9852-3dc92c43e74a&re..." -Credential $cred -ContentType $ContentType -Headers $header -OutFile 'd:\xml.xml'
[xml]$Config = Get-Content d:\xml.xml
$report = @()
$MetricNode = $Config.'stats-of-resources'.'stats-of-resource'.'stat-list'
$UUIDS = $Config.'stats-of-resources'.'stats-of-resource'
Foreach ($UUID in $UUIDS){
$VROPSUUID = $UUID.resourceId
foreach ($node in $MetricNode.stat)
{
#Collection Date, not run time
$MetricName = $node.statKey.Key
$Timestamps = $node.timestamps
$Values = $node.data
$report += New-Object psobject -Property @{METRIC=$MetricName;resourceId=$VROPSUUID;Timestamp=$Timestamps;value=$Values}
$report | Export-csv reprop.csv -NoTypeInformation
}
}
Current output:
"resourceId","Timestamp","METRIC","value"
"ef951a38-3063-477d-af32-baa6d2744357","1466085599999 1466171999999","cpu:1|costop_summation","4.6296298710836307E-4 0.0 4.5836298710836307E-4 0.0"
"ef951a38-3063-477d-af32-baa6d2744357","1466085599999 1466171999999","mem|usage_average","12.678446789582571 15.390000343322754"
...
..
.
Desired output
"resourceId","Timestamp","METRIC","value"
"ef951a38-3063-477d-af32-baa6d2744357","1466085599999","cpu:1|costop_summation","4.6296298710836307E-4 0.0"
"ef951a38-3063-477d-af32-baa6d2744357","1466171999999","cpu:1|costop_summation","4.5836298710836307E-4 0.0"
"ef951a38-3063-477d-af32-baa6d2744357","1466085599999","mem|usage_average","12.678446789582571"
"ef951a38-3063-477d-af32-baa6d2744357","1466171999999","mem|usage_average","15.390000343322754"
...
..
.