Convert ePO Web request output to a table

[kvasil]

Hi,

I have been doing some reports from McAfee ePO with great success when I export a report out of it and into PowerBI as an excel file. Instead of updating the source file every so often I am trying to get the PowerBI to go directly to the ePO. I am having some success by using the Get Data -> Other -> Web and providing it with the query number which I use inside the ePO. The issue I am running into is the way that i get the data however. When I import it as an excel file after exporting it out of ePO it comes in a form as a table with column headers being "System Name", "User Name", "OS Type", "Last Communication", etc. When I use the Web form the output comes in a slightly different format and I can’t for the life of me figure out how to fix it.

The Data comes out like this:

OK:
System Name: 204CNV2
User Name: testuser
OS Type: Windows 10
Last Communication: 1/2/20 4:55:43 AM

System Name: 201C4V8
User Name: testuser1
OS Type: Windows 10
Last Communication: 1/2/20 2:53:54 AM

System Name: 201C4V6
User Name: testuser2
OS Type: Windows 10
Last Communication: 1/2/20 5:13:29 AM

I was able to separate the headers from the data by adding a left-most-delimiter being a colon but that just separates what should be the headers from the data and I can’t for the life of me figure out how to convert it to the database format. As if I for example try to pivot the table after creating a reference table all I get is:

Column 1:                  Column 2:                  Column 2:    

System Name            System Name            System Name

204CNV2                  201C4V8                   201C4V6

Where as what i need is 

System Name                    User Name                         OS Type                               Last Communication:

204CNV2                              testuser                               Windows 10                        1/2/20 4:55:43 AM

201C4V8                              testuser1                             Windows 10                        1/2/20 2:53:54 AM

201C4V6                               testuser2                             Windows 10                        1/2/20 5:13:29 AM

[Anonymous]

Hey @MDahitule, were you able to find a solution ? 

I am very interested too :))

[MDahitule]

Hello @Anonymous haven't foound the solution,

I scheduled the csv reports from ePO to my mailbox (o365) and fetching the report through Powerbi / Powerquery from mailbox.

Hope this will helpful.

[v-joesh-msft · Accepted solution]

Hi @kvasil ,

Do you want output similar to the following:

You can try something like the following M Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nc0xC8IwEIbhv3JkFppcEtHbJKvWoVqH0iFohgxpxKRI/70lOAlF6HJwLx88XceaKWUXoLbBESBXpm6R9ZuOXZN7fXN2KY/zW/q5gcv0nOvND4/4TiB46UebMpgYwjj4u80+DgSiwgo5KNKalITDqSzL+XGFUe1uyRVrYSQtSat/8HYJxrWwJiEJ9wXuPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Column1] <> null and [Column1] <> ""),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Filtered Rows", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], ":"), type text),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Inserted Text Before Delimiter", "Text After Delimiter", each Text.AfterDelimiter([Column1], " ", 1), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Text After Delimiter",{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Text Before Delimiter"}, {{"Count", each _[Text After Delimiter], type list}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"System Name", type any}, {"User Name", type any}, {"OS Type", type any}, {"Last Communication", type any}}),
    Custom1 = List.Zip({#"Changed Type1"[System Name]{0}, #"Changed Type1"[User Name]{0}, #"Changed Type1"[OS Type]{0}, #"Changed Type1"[Last Communication]{0}}),
    Custom2 = Table.FromRows(Custom1),
    #"Renamed Columns" = Table.RenameColumns(Custom2,{{"Column1", "System Name"}, {"Column2", "User Name"}, {"Column3", "OS Type"}, {"Column4", "Last"}})
in
    #"Renamed Columns"

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERdXJIgbGtNAr3Res8...

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kvasil]

Thank you,

I ended up creating conditional column for each of the "headers" and filling it up and down but this is much more scalable in you have a ton of data points.

let
Source = Csv.Document(Web.Contents("https://localhost/remote/core.executeQuery?queryId=607"),[Delimiter="=", Columns=1, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Top Rows", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "DNS Name (ePO)", each if [Column1.1] = "System Name" then [Column1.2] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "User Name (ePO)", each if [Column1.1] = "User Name" then [Column1.2] else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Encryption State (ePO)", each if [Column1.1] = "State (System)" then [Column1.2] else null),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "OS Type (ePO)", each if [Column1.1] = "OS Type" then [Column1.2] else null),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Build Number (ePO)", each if [Column1.1] = "Custom 3" then [Column1.2] else null),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Last Communication (ePO)", each if [Column1.1] = "Last Communication" then [Column1.2] else null),
#"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "Free Drive Space (ePO)", each if [Column1.1] = "Free C Drive Space" then [Column1.2] else null),
#"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "IP Address (ePO)", each if [Column1.1] = "IP address" then [Column1.2] else null),
#"Added Conditional Column8" = Table.AddColumn(#"Added Conditional Column7", "Model (ePO)", each if [Column1.1] = "System Model" then [Column1.2] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column8",{"DNS Name (ePO)", "User Name (ePO)", "Encryption State (ePO)", "OS Type (ePO)", "Build Number (ePO)", "Last Communication (ePO)", "Free Drive Space (ePO)", "IP Address (ePO)", "Model (ePO)"}),
#"Filled Up" = Table.FillUp(#"Filled Down",{"DNS Name (ePO)", "User Name (ePO)", "Encryption State (ePO)", "OS Type (ePO)", "Build Number (ePO)", "Last Communication (ePO)", "Free Drive Space (ePO)", "IP Address (ePO)", "Model (ePO)"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Filled Up", "Last Communication (ePO)", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, true), {"Last Communication (ePO).1", "Last Communication (ePO).2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Last Communication (ePO).1", type datetime}, {"Last Communication (ePO).2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Last Communication (ePO).1", "Last Communication (ePO)"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Last Communication (ePO).2", "Column1.1", "Column1.2"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"DNS Name (ePO)"})
in
#"Removed Duplicates"

[MDahitule]

Hi kvasil,

The above pos is helpful, however I am not able to import data from ePO to Powerquery.

Can you please help with step by step process.

I am using McAfww ePO Version 5.10.0