Get VMs from VCenter via API

[ferwe]

Hello

I try to geht a list of virtual machines from VMWare VCenter.

I need a session ID to connect. If i already have the session id generated, I'm able to connect.

I got a code snippet for getting the ID first, but I can't get it to work.

Any suggestions?

let
   MyToken = let
      body = Text.ToBinary(""),
      url = "https://vcenter.url/rest/com/vmware/cis/session",

      options = [
      Headers =[#"Content-type"="application/json"],
      Content=body
      ],
      result = Web.Contents(url, options),
      #"Converted to Table" = Xml.Tables(result,null,65001),
      access_token = #"Converted to Table"{0}[SessionToken]
   in
      access_token,
Source = Json.Document(Web.Contents("https://vcenter.url/rest/vcenter/vm", [Headers=[#"vmware-api-session-id"="MyToken", Accept="application/json"]])),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"memory_size_MiB", "vm", "name", "power_state", "cpu_count"}, {"Column1.memory_size_MiB", "Column1.vm", "Column1.name", "Column1.power_state", "Column1.cpu_count"})
in
#"Expanded Column1"

[ferwe · Accepted solution]

I found the solution.

For everyone who need to get vm data from the VCenter API.

let
MyToken = let
body = Text.ToBinary(""),
url = "https://%VCenter%/rest/com/vmware/cis/session",
options = [
Headers =
[#"Content-type"="application/json",
Authorization="Basic %BASE64 encoded username password%",
Accept="application/json"
],
Content=body
],
result = Json.Document(Web.Contents(url, options)),
#"Converted to Table" = Record.ToTable(result),
#"Removed Columns" = Table.RemoveColumns(#"Converted to Table",{"Name"}),
Value = #"Removed Columns"{0}[Value]
in
Value,
Source = Json.Document(Web.Contents("https://%VCenter%/rest/vcenter/vm", [Headers=[#"vmware-api-session-id"=MyToken, Accept="application/json"]])),
value = Source[value],
#"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"memory_size_MiB", "vm", "name", "power_state", "cpu_count"}, {"Column1.memory_size_MiB", "Column1.vm", "Column1.name", "Column1.power_state", "Column1.cpu_count"})
in
#"Expanded Column1"

[ocalaf-unitenic]

I'm quite new to PowerBi and PowerBi Desktop, could you please explain a bit how this should be implemented? Is the text you sent the content of a blank query? How the %VCenter% and credentials variables or parameters are managed? Could this work using an on-premise data gateway to connect to an on-premise vCenter?

Thank you in advance for the tips!

Òscar

[v-xicai]

Hi @ferwe ,

You may refer to the links as below:

Power BI REST APIs

What can developers do with the Power BI API?

Pull REST api data from VMWare vCenter

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ferwe]

Hi @v-xicai 

Thank you for you answer.

The first two posts are about the power bi API.

But I need a solution to connect to the vmware api.

The third post was the first i found and this is about a problem with the ssl certificate.

My actual problem is different.

I want to get the "session id" from vmware.

This can be done with a post request and "basic authentication" against the url "https://%vcenter%/rest/com/vmware/cis/session"

I can do this with postman and powershell as well.

But with Power BI i got only "404 Page not found"

I'm pretty sure, that the authentication doesn't work. But unfortunately I don't know how to setup authentication inside the "Web.Contents" command for a correct authentication.

What i know:

1. I need a Post reques to get the session id.

2. To do a post request i need to add the "content" variable as described in this Link

3. If i add "content" to the "web.contents" part, i have to use the "anonymous" authentication for the data source

So my problem ist now. How to authenticate against the api with basic authentication inside the request.

Referring to this Link i should use it this was:

let
  response = Web.Contents("https://%vcenter%/rest/com/vmware/cis/session",

  [Headers=[Authorization="Basic %encoded username password%", content="" ]])

in
response

But this doesn't work.

Final question.

How to send a "post" request with basic authentication and no content inside the web.contents part?

Or with other words.

Does anyone has a code example for PowerBI to connect to the VCenter API?

Regards