List.Generate - Qualys API - Issue fetching all records

There are a couple of things you may want to consider.  Usually List.Generate is a rather inefficient way of collecting the resulting data as you would need to lug all of the results along.

List.Generate(
() => [ Result = Json.Document(Web.Contents(data_url, [Headers = headers , Content=Text.ToBinary("")]))],

each [Result][hasMore] = 1,

each Result = Json.Document(Web.Contents(data_url  & "&lastSeenId=" [Result][lastSeenID], [Headers = headers , Content=Text.ToBinary("")])),

each [Result][Payload] & Json.Document(Web.Contents(data_url  & "&lastSeenId=" [Result][lastSeenID], [Headers = headers , Content=Text.ToBinary("")]))[Payload]
)

Usually it is easier to use List.Generate only to create the list of required URLs,  then to add a custom column with the URL contents, and finally to combine the data in that column.  Usually the data is cached so it's not really a big issue if you fetch it twice.

Looks like you are missing an &, and I agree you need to convert it to text (if it isn't text already).

Source = List.Generate(
() => [ Result = Json.Document(Web.Contents(data_url, [Headers = headers , Content=Text.ToBinary("")]))],

each [Result][hasMore] = 1,

each [Result = Json.Document(Web.Contents(data_url  & "&lastSeenId=" & Number.ToText([Result][lastSeenID]), [Headers = headers , Content=Text.ToBinary("")]))],

each [Result])
in
Source

Pat

I am also learning M for Qualys API integration and was wondering if you ever managed to get a working script.

I am struggling with pagination I have built a function with a manual loop with manual iterations just to make sure I had the code right but I am finding functional language fairly hard to wrap my head around!

I think i need to create a function and call it whilst the variable hasMoreRecords = true but this is day 4 and my brain is literally melting.. 🙂

Anyway whilst the code is crude and ugly I thought it may help someone that was struggling to create a power Query integration with Qualys.

Any advice on how should be done in M is very welcome 🙂

let

url = "https://qualysapi.qualys.eu/qps/rest/2.0/search/am/hostasset/",
authKey = "xxxxxxxxxxxxxxxxxxxxx",
Headers = [
#"Content-Type" = "text/xml",
#"Accept" = "application/xml",
#"Authorization" = "Basic " & authKey,
#"X-Requested-With" = "AHARP_BI"
//#"Cache-Control" = "no-cache",
//#"lastID" = lastId,
//#"lastID1" = lastId1,
//#"hasMoreRecords" = hasMoreRecords
],
//lastId1 = jsonResponse{0}[lastId],
//Recursive Loop Here while hasMoreRecords = true

//Manual test Loop
postData = "<ServiceRequest><preferences><limitResults>10</limitResults></preferences><filters><Criteria field=""tagName"" operator=""EQUALS"">Cloud Agent</Criteria><Criteria field=""id"" operator=""GREATER"">0</Criteria></filters></ServiceRequest>",
jsonResponse = Xml.Tables(Web.Contents(url, [Headers = Headers, Content = Text.ToBinary(postData)])),
lastId1 = jsonResponse{0}[lastId],
data = jsonResponse{0}[data],

postData1 = "<ServiceRequest><preferences><limitResults>10</limitResults></preferences><filters><Criteria field=""tagName"" operator=""EQUALS"">Cloud Agent</Criteria><Criteria field=""id"" operator=""GREATER"">"&lastId1&"</Criteria></filters></ServiceRequest>",
jsonResponse1 = Xml.Tables(Web.Contents(url, [Headers = Headers, Content = Text.ToBinary(postData1)])),
lastId2 = jsonResponse1{0}[lastId],
data1 = jsonResponse1{0}[data],

postData2 = "<ServiceRequest><preferences><limitResults>10</limitResults></preferences><filters><Criteria field=""tagName"" operator=""EQUALS"">Cloud Agent</Criteria><Criteria field=""id"" operator=""GREATER"">"&lastId2&"</Criteria></filters></ServiceRequest>",
jsonResponse2 = Xml.Tables(Web.Contents(url, [Headers = Headers, Content = Text.ToBinary(postData1)])),
lastId3 = jsonResponse2{0}[lastId],
data2 = jsonResponse2{0}[data],

postData3 = "<ServiceRequest><preferences><limitResults>10</limitResults></preferences><filters><Criteria field=""tagName"" operator=""EQUALS"">Cloud Agent</Criteria><Criteria field=""id"" operator=""GREATER"">"&lastId3&"</Criteria></filters></ServiceRequest>",
jsonResponse3 = Xml.Tables(Web.Contents(url, [Headers = Headers, Content = Text.ToBinary(postData1)])),
lastIda4 = jsonResponse3{0}[lastId],
data3 = jsonResponse3{0}[data],

postData4 = "<ServiceRequest><preferences><limitResults>10</limitResults></preferences><filters><Criteria field=""tagName"" operator=""EQUALS"">Cloud Agent</Criteria><Criteria field=""id"" operator=""GREATER"">"&lastId4&"</Criteria></filters></ServiceRequest>",
jsonResponse4 = Xml.Tables(Web.Contents(url, [Headers = Headers, Content = Text.ToBinary(postData1)])),
lastId5 = jsonResponse4{0}[lastId],
data4 = jsonResponse4{0}[data],

postData5 = "<ServiceRequest><preferences><limitResults>10</limitResults></preferences><filters><Criteria field=""tagName"" operator=""EQUALS"">Cloud Agent</Criteria><Criteria field=""id"" operator=""GREATER"">"&lastId5&"</Criteria></filters></ServiceRequest>",
jsonResponse5 = Xml.Tables(Web.Contents(url, [Headers = Headers, Content = Text.ToBinary(postData1)])),
lastId6 = jsonResponse5{0}[lastId],
data5 = jsonResponse5{0}[data],

HostAsset = data{0}[HostAsset] & data1{0}[HostAsset] & data2{0}[HostAsset] & data3{0}[HostAsset] & data4{0}[HostAsset] & data5{0}[HostAsset],
#"Changed Type" = Table.TransformColumnTypes(HostAsset,{{"id", Int64.Type}, {"name", type text}, {"created", type datetime}, {"modified", type datetime}, {"type", type text}, {"qwebHostId", Int64.Type}, {"lastVulnScan", type datetime}, {"lastSystemBoot", type datetime}, {"lastLoggedOnUser", type text}, {"domain", type text}, {"fqdn", type text}, {"os", type text}, {"dnsHostName", type text}, {"netbiosName", type text}, {"networkGuid", type text}, {"address", type text}, {"trackingMethod", type text}, {"manufacturer", type text}, {"model", type text}, {"totalMemory", Int64.Type}, {"timezone", type text}, {"biosDescription", type text}, {"isDockerHost", type logical}, {"cloudProvider", type text}})
in
#"Changed Type"