4 Replies Latest reply on Jun 16, 2016 7:13 AM by vMan

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

    vMan Enthusiast
    vExpert

      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|costop_summation&statKey=cpu|usage_average&statKey=mem|demand&statKey=mem|usage_average&statKey=cpu|costopPct&rollUpType=AVG&intervalType=DAYS" -Credential $cred -ContentType $ContentType -Headers $header -OutFile 'Output.csv'

        • 1. Re: Help using Suite-API - getStatsOfResourcesCSV with Powershell Invoke-RestMethod
          vMan Enthusiast
          vExpert

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

          • 2. Re: Help using Suite-API - getStatsOfResourcesCSV with Powershell Invoke-RestMethod
            MichaelRyom Hot Shot
            vExpert

            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

            • 3. Re: Help using Suite-API - getStatsOfResourcesCSV with Powershell Invoke-RestMethod
              vMan Enthusiast
              vExpert

              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

              • 4. Re: Help using Suite-API - getStatsOfResourcesCSV with Powershell Invoke-RestMethod
                vMan Enthusiast
                vExpert

                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&resourceId=ef951a38-3063-477d-af32-baa6d2744357&statKey=cpu|costop_summation&statKey=cpu|usage_average&statKey=mem|demand&statKey=mem|usage_average&statKey=cpu|costopPct&rollUpType=$AVG&intervalType=$DAYS" -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"

                ...

                ..

                .