Encorporate data in http POST request

Tejas Kore

Board Regular
Joined
Nov 2, 2017
Messages
72
Office Version
  1. 365
Platform
  1. Windows
Hello Friends,

I am trying to fetch data from one of our vendor's site(https://developer.tenable.com/reference#exports-vulns-request-export) using API. I saw the api documentation but there weren't any examples using VBA. Here is a code snippet from python:

Code:
import requests

url = "https://cloud.tenable.com/vulns/export"

payload = {"filters": {
        "tag.<category>": ["tag.CompanyName External Assets: IP"],
        "last_found": 1625570746
    }}
headers = {
    "Accept": "application/json",
    "Content-Type": "application/json",
    "X-ApiKeys": "" #Cannot disclose publicly
}

response = requests.request("POST", url, json=payload, headers=headers)

print(response.text)

Here is my VBA code:

VBA Code:
Sub authenticated_test()
    Dim req As MSXML2.ServerXMLHTTP60
    Dim JsonString As String
    Dim dic As Variant
    Dim liveURL As String, paperURL As String
    Dim acc_header_name, acc_header_value, secret_header_name, secret_key As String
    
    Set req = New MSXML2.ServerXMLHTTP60
    
    acc_header_name = "Accept"
    acc_header_value = "application/json"
    secret_header_name = "X-ApiKeys"
    secret_key = "" 'Cannot disclose the key on public
    
    liveURL = "https://cloud.tenable.com"
    paperURL = "https://cloud.tenable.com/vulns/export"
    
    req.Open "POST", paperURL, False
    req.setRequestHeader acc_header_name, acc_header_value
    req.setRequestHeader secret_header_name, secret_key
    req.send ["filters", "tag.<category>", ["tag.CompanyName External Assets: IP"], "last_found", 1625570746] '---> Need help here
    'Debug.Print req.responseText
    Set dic = ParseJSON(req.responseText)
    Debug.Print ListPaths(dic)
End Sub

I need help here '--->.
I tried to print the response. It says 'obj.error --> Unsupported Media Type'. Basically I am trying to apply filters and fetch data. It would be great if anyone could let me know the mistake I am making.
Notes: 1. Using ParseJSON function to parse the JSON string received ('Excel VBA: Parse JSON, Easily).
2. last_found is UNIX time.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try this:
VBA Code:
    Dim payload As String
    payload = """filters"": { ""tag.<category>"": [""tag.CompanyName External Assets: IP""], ""last_found"": 1625570746 }"
    With req
        .Open "POST", paperURL, False
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "X-ApiKeys", secret_key
        .send payload
        Debug.Print .responseText
    End With
 
Upvote 0
Try this:
VBA Code:
    Dim payload As String
    payload = """filters"": { ""tag.<category>"": [""tag.CompanyName External Assets: IP""], ""last_found"": 1625570746 }"
    With req
        .Open "POST", paperURL, False
        .setRequestHeader "Accept", "application/json"
        .setRequestHeader "Content-Type", "application/json"
        .setRequestHeader "X-ApiKeys", secret_key
        .send payload
        Debug.Print .responseText
    End With
Got this:
{"statusCode":400,"error":"Bad Request","message":"Invalid request payload JSON format"}
 
Upvote 0
I've just looked at your API link, and the Python example shows no payload data is sent:
Code:
response = requests.request("POST", url, headers=headers)
Therefore try one of these:
VBA Code:
.send
.send vbNullString
 
Upvote 0
I've just looked at your API link, and the Python example shows no payload data is sent:
Code:
response = requests.request("POST", url, headers=headers)
Therefore try one of these:
VBA Code:
.send
.send vbNullString
That's because no payload was selected. For e.g. .. Just below the python code .. You can observe *num_assets .. If you enter a value in the box next to it .. It will add the payload.
 

Attachments

  • Capture.PNG
    Capture.PNG
    79.7 KB · Views: 29
Upvote 0
OK, I see. I don't know what the syntax for Python strings is and omitted the first { and last } in your original post, assuming they are string delimiters. Try it with them added back:
VBA Code:
    payload = "{""filters"": { ""tag.<category>"": [""tag.CompanyName External Assets: IP""], ""last_found"": 1625570746 }}"
 
Upvote 0
Solution
OK, I see. I don't know what the syntax for Python strings is and omitted the first { and last } in your original post, assuming they are string delimiters. Try it with them added back:
VBA Code:
    payload = "{""filters"": { ""tag.<category>"": [""tag.CompanyName External Assets: IP""], ""last_found"": 1625570746 }}"
Yupps .. I tried that .. It work .. Thanks a lot .. Seems we need to add those curly brackets at the beginning and end to make it a complete JSON string .. I used ..
VBA Code:
payload = "{" & """filters"": { ""tag.<category>"": [""CompanyName External Assets""], ""last_found"": 1625570746 }" & "}"
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top