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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.