VMware Cloud Community
carvaled
Enthusiast
Enthusiast
Jump to solution

Help using Suite-API - getStatsOfResourcesCSV with Powershell Invoke-RestMethod

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'

Reply
0 Kudos
1 Solution

Accepted Solutions
MichaelRyom
Hot Shot
Hot Shot
Jump to solution

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

Blogging at https://MichaelRyom.dk

View solution in original post

Reply
0 Kudos
4 Replies
carvaled
Enthusiast
Enthusiast
Jump to solution

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')

Reply
0 Kudos
MichaelRyom
Hot Shot
Hot Shot
Jump to solution

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

Blogging at https://MichaelRyom.dk
Reply
0 Kudos
carvaled
Enthusiast
Enthusiast
Jump to solution

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

Reply
0 Kudos
carvaled
Enthusiast
Enthusiast
Jump to solution

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"

...

..

.

Reply
0 Kudos