Use SCCM Online Reports for Flow actions

[Dan47]

Hi There

We are using Configuration Manager / SCCM and can access SQL Reports from the Browser Portal.

Now I got the Idea to use these Informations in Power Automate.

What I want to do is make a flow that runs a sql software report with a search parameter and use the result for adding the Users to a mailing list.

How do I get the report in?

Many Thanks

[Dan47]

Just found this:

Power Query can read the atom file because it’s actually just a text file. You can open it in notepad and see it’s contents. It is an XML document that contains a URL. That URL can be used to download the report in your browser, but the encoding and formatting of it needs to be cleaned up a bit for Power Query to be able to use it. So save the atom file to your desktop, copy it’s file path, and paste it into the top line of the below query code. Run the query and BAM! The same data from the export to CSV option in SSRS should appear in Power Query.

//let
    FilePath = "C:\Users\Jeremy\SSRS_Example.atomsvc",
    Source = Xml.Tables(File.Contents(FilePath)),
    Table0 = Source{0}[Table],
    Table = Table0{1}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table, {{"Attribute:href", type text}}),
    #"Attribute:href" = #"Changed Type"{0}[#"Attribute:href"],
    #"Clean Ampersands" = Text.Replace(#"Attribute:href", "amp;", ""),
    #"Change to CSV Format" = Text.Replace(#"Clean Ampersands", "Format=ATOM", "Format=csv"),
    Request = Excel.Workbook(Web.Contents(#"Change to CSV Format"))
in
    //Request

Source: How To Scrape SSRS Reports with Power Query – Spreadsheets 'N Things (jjohnson.org)

Hope this helps to connect the dots.