Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
BI_Analyticz
Helper V
Helper V

Power BI Integration to Qualys

Anyone successfully integrated to Qualys using Power BI? 

14 REPLIES 14
BuddyB
Frequent Visitor

As a Qualys user, I have been working on this as well.  I will try to come up with a series of guides that are a bit more polished, but for a basic API connectivity scenario like yours, which seems to be the most common, I'll show you how that's done.  Qualys isn't great about showing the step-by-step process for API connectivity, or telling folks that their data is a bunch of nested tables. It won't be a flat CSV report download via the Qualys UI. 

 

Instead of a screenshot to show you, I made a quick YouTube video to show you how I connect vai API and drill into the nested tables. 

Here is the video:  Pull Qualys VM Data via API using PowerBI - https://youtu.be/6J-AES7L5eA

 

camples79
Frequent Visitor

Did anyone get this work? I can't get thru authenticating without an error. 

See my other reply with a YouTube video link that shows you how to connect.

 

-Buddy

powerful_user
Frequent Visitor
amitchandak
Super User
Super User

@BI_Analyticz , refer if this doc can help

https://qualys-secure.force.com/discussions/s/article/000003446

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

I followed the same link but I am unable to see the data. Please see below screenshot. Any ideas why???

 

BI_Analyticz_0-1616755205560.png

let
Source = Web.Page(Web.Contents("https://qualysapi.qualys.eu/api/2.0/fo/asset/host/?action=list", [Headers=[Authorization="xxxxxxxxxxxxxxxxxxxxxxxxxxxxx", #"X-Requested-Width"="Detection"]])),
#"Changed Type" = Table.TransformColumnTypes(Source,{})
in
#"Changed Type"

Maybe try something like: 

 

let
Url = "https://qualysapi.qualys.eu/api/2.0/fo/asset/host/",
AuthString = "Basic ########################",
Source = Xml.Tables(
Web.Contents (
Url,
[
Headers = [ #"Authorization" = AuthString,
#"X-Requested-With" = "Power BI"],
Query = [action = "list"],
Content = Text.ToBinary("")
]

)
),
Table = Source{0}[Table],
HOST_LIST = Table{0}[HOST_LIST],
HOST = HOST_LIST{0}[HOST],
#"Changed Type" = Table.TransformColumnTypes(HOST,{{"ID", Int64.Type}, {"IP", type text}, {"TRACKING_METHOD", type text}, {"DNS", type text}, {"NETBIOS", type text}, {"OS", type text}, {"QG_HOSTID", type text}, {"CLOUD_PROVIDER", type text}, {"CLOUD_SERVICE", type text}, {"CLOUD_RESOURCE_ID", type text}, {"EC2_INSTANCE_ID", type text}})
in
#"Changed Type"

 

Hi, 

I try it, but i receive only the first 1000 lines. how should I complete the request to make pagination. 

 

Many thanks for your help.

hinin
Frequent Visitor

By default the Host query is limited to 1000 rows, so you have two options. 

 

By far the easiest is to set the truncation_limit query parameter higher, allows you to retrieve up to 10,000 records (ref Qualys API (VM, PC) User Guide page 481). Precisely "how big" is up to your use case.

 

The alternative is to adapt to a pagination mode query (although I haven't tried this as I haven't needed to) per Handling paging for Power Query connectors - Power Query | Microsoft Learn. If there are more records, the Qualys API will return a warning and the new URL (ref ref Qualys API (VM, PC) User Guide page 493) that looks something like https://qualysapi.qualys.com/api/2.0/fo/asset/host/?
action=list&id_min=2400356 which has the host id to start the next query from.

HR_BI
Frequent Visitor

I tried the above code but changes the API url with https://qualysapi.qualys.com/api/2.0/fo/asset/host/. However I just got the table with Datetime and no columns with the Host_LIST.

I am looking for Assets data from Qualys API that I can bring in Power BI using M query. 

Any help is appreciated.

hinin
Frequent Visitor

Very hard to tell since this is little to go on, but this is more likely a Qualys permissions thing, as it is returning something - do you have the "Manage compliance" permission set for the account you're using to access?

 

I'd take this up with whoever manages your Qualys access.

This is the exact same thing I am getting.  There are no other columns.

My Authentication part is working , here is the code and it will fetch first 300 records.

 

However, I am stuck on the iteration part to get all the records.

 

let
api_url = "https://gateway.qg1.apps.qualys.com/auth",
body = "{ ""username"": ""******"", ""password"": ""***""}",
Parsed_JSON = Json.Document(body),
BuildQueryString = Uri.BuildQueryString(Parsed_JSON),
Response = (Web.Contents(api_url,
[
Headers = [#"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(BuildQueryString)
]
)
),
#"Imported CSV" = Csv.Document(Response,[Delimiter=",", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None]),
TokenKey = #"Imported CSV"[Column1]{0},
AccessTokenHeader = "Bearer "&TokenKey,
data_url = "your URL(gateway)",
headers = [#"Authorization" = AccessTokenHeader, Accept = "*/*", #"Content-Type"="application/json"],
Response_new = Json.Document(Web.Contents(data_url, [Headers = headers , Content=Text.ToBinary("")])),

in
Response_new

 

 

Hello, I tried the above Mcode but i got stuck at authentication. can i know how did you pass through?!

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.