Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good Evening,
I was wondering if someone couild point me in the right direction on how to transform the result of a McAffee Scan to somehting that I can use to display the results in a table. Essentially, I am trying to create a "status" dashboard that will show the results of scans ran on the computer. Eventually, I will want to import multiple files and combine but I am so new to PowerBI that this part is proving troublesome for me.
Here is a sample output of one of the files I am trying to import. What I would like to see if a way to drill down into the individual scans and the result of that file. I know that the Preamble is universal data for the scan, but so is the Date_Time and options as far as I can tell. I just dont really have any idea how to get started. I have read about xml.document vs xml.file but I am still lost. I know that i have to do the transformation in the advanced query editor and also read about i may have to create PK and FK relationship. If there is some resource that I am missing in my search, please point me there and I can figure this out...just not sure how to start.
<?xml version="1.0" encoding="utf-8"?> <!-- Scan Results --> <Scan> <Preamble> <Product_name value="McAfee VirusScan Command Line for Win32" /> <Version value="6.0.6.6" /> <License_info value="LICENSED COPY - April 13 2018" /> <AV_Engine_version value="5800.750" /> <Dat_set_version value="882" /> </Preamble> <Date_Time value="2018-Apr-13 15:17:32" /> <Options value="C:\PKI SW /SECURE /NOBREAK /threads 200 /move c:\temp\virus /clean /showcomp /rptall /rpterr /rptcor /streams /xmlpath Fri04-13-2018@15-17-29.03-Externaldrive.xml /report Fri04-13-2018@15-17-29.03-Externaldrive.log " /> <File name="C:\PKI SW\CAPolicy-inf (pol).txt" status="ok" /> <File name="C:\PKI SW\CAPolicy-inf (ROOT).txt" status="ok" /> <File name="C:\PKI SW\activclient_windows_latest (2).zip" status="ok" /> <Summary On-Path="C:\PKI SW" Total-files="3" Clean="3" Not-Scanned="0" Possibly-Infected="0" Cleaned="0" Moved="0" Deleted="0" /> <Time value="00:01.22" /> </Scan>
Hi @jstugs
When I directly copy the above data to .xml file and connect to it in Power BI Desktop, I get the following table.
What expected result do you want to get?
what does “drill down into the individual scans and the result of that file” mean?
Which table contains the actual data? I noticed that one of them listed some file names, do you want to obtain the information of these files, which performs like a drill down action?
Best Regards
Maggie
Thank you @v-juanli-msft,
I would like to transform that whole file into two flattened relational tables. This file is one execution output of the McAfee tool. Each run of the tool will produce a similar file and the "files" scanned could change depending on the location scanned. The issue that I am having is that most of the data is in the attributes of the elements. I manually transformed the data and was able to get a table that worked but I am trying to automate that exercise. I would like to use one tool to do this and not have to write a script in another program if possible. My idea is to use PowerBI desktop advanced query to pull in all the files (<6 months), transform, and then display the results on a report. Essentially, I would like the data to tranform to something like this:
Read element "value" attribute and transfer to column
Table 1 (main table) - PK (newly generated), Date_time, Options, Summary (each attribute would have own column), Time, Product_name, Version, License_info, AV_Engine_Version, Data_set_version
Read element "name" and "status" attribute and transfer to their own columns
Table 2 (file table) FK to main table, File name, File status
Any help in the right direction would be greatly appreciated!
**edit** Adding ERD as example
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |