With the the help from suggestions in the community, I managed to pulled Qualys VM data. While since there are too many records, in some scenarios I always failed. There are two options I have in mind:
1. paginated report, pull certain numbers of asset vulnerability records at a time, and repeat till all are fetched.
2. incremental refresh.
Currently, I'm trying with #1, paginated report, while I tried and failed... Can someone please help? Thanks a lot.
let
// 设置参数
apiUrl = "https://qualysapi.qualys.com/api/2.0/fo/asset/host/vm/detection/?action=list&severities=3&show_resul...",
headers = [Authorization="Basic xx", #"X-Requested-With"="Test"],
// 定义获取数据的函数
GetData = (url) =>
let
source = Xml.Tables(Web.Contents(url, [Headers=headers])),
// 从XML数据中提取有用的信息，具体步骤取决于返回的XML结构
data = source{0}[HOST_LIST] // 使用正确的节点名称
in
data,
// 获取第一页数据
firstPageUrl = apiUrl,
firstPageData = GetData(firstPageUrl),
// 初始化变量
allPagesData = {firstPageData},
nextPageUrl = try Xml.Tables(Web.Contents(firstPageUrl, [Headers=headers])){0}[HOST_LIST]{0}[URL] otherwise null,
// 定义递归函数
GetNextPageData = (url as text, accumData as list) =>
let
pageData = GetData(url),
newAccumData = accumData & {pageData},
newNextPageUrl = try Xml.Tables(Web.Contents(url, [Headers=headers])){0}[HOST_LIST]{0}[URL] otherwise null
in
if newNextPageUrl <> null then
GetNextPageData(newNextPageUrl, newAccumData) // 此处使用了不同的变量名
else
newAccumData,
// 调用递归函数以获取剩余页数的数据
allPagesDataResult = GetNextPageData(nextPageUrl, allPagesData)
// 将所有数据转换为表格
// resultTable = Table.FromList(List.Combine(allPagesDataResult), Splitter.SplitByNothing())
in
allPagesDataResult
