Pushing Log Analytics Workspace tables into SQL databases

%3CLINGO-SUB%20id%3D%22lingo-sub-1644274%22%20slang%3D%22en-US%22%3EPushing%20Log%20Analytics%20Workspace%20tables%20into%20SQL%20databases%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1644274%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20push%20Log%20Analytics%20data%20into%20PowerBI%20with%20a%20custom%20refresh%20schedule.%20Since%20this%20is%20not%20already%20supported%20by%20PowerBI%20I%20am%20trying%20to%20push%20it%20into%20a%20SQL%20database%20instead%20which%20is%20then%20connected%20to%20PowerBI.%20Is%20there%20a%20specific%20script%20I%20can%20run%20to%20achieve%20this%20and%20then%20use%20a%20task%20scheduler%20to%20push%20the%20data%20regularly%3F%20Suggestions%20for%20a%20different%20approach%20will%20also%20be%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1661315%22%20slang%3D%22en-US%22%3ERe%3A%20Pushing%20Log%20Analytics%20Workspace%20tables%20into%20SQL%20databases%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1661315%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F785650%22%20target%3D%22_blank%22%3E%40Mitali645%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELog%20Analytics%20and%20Power%20BI%20work%20really%20well%20together.%3C%2FP%3E%0A%3CP%3EAll%20you%20need%20to%20do%20to%20use%20LA%20data%20in%20Power%20BI%20is%3A%3C%2FP%3E%0A%3CP%3E1.%20Compose%20the%20query%20you%20want%20to%20use%20in%20Power%20BI%20in%20Log%20Analytics.%3C%2FP%3E%0A%3CP%3E2.%20After%20you%20are%20happy%20with%20your%20query%20click%20%22export%22%20and%20use%20the%20'export%20to%20Power%20BI%20(M%20query)%20option%20to%20create%20a%20Power%20BI%20friendly%20version%20of%20your%20query.%3C%2FP%3E%0A%3CP%3E3.%20Open%20Power%20BI%20desktop%3C%2FP%3E%0A%3CP%3E4.%20Click%20%22get%20data%22%3C%2FP%3E%0A%3CP%3E5.%20Select%20%22blank%20query%22%20from%20the%20Get%20data%20options%3C%2FP%3E%0A%3CP%3E6.%20Click%20%22Advanced%20Editor%22%20and%20paste%20the%20content%20exported%20from%20Log%20Analytics%20in%20the%20window%20that%20opens%3C%2FP%3E%0A%3CP%3E7.%20Click%20%22ok%22%20-%20note%20-%20you%20may%20have%20to%20authenticate%20with%20Azure%20at%20this%20point%3C%2FP%3E%0A%3CP%3EYou%20are%20all%20set!%20Power%20BI%20will%20now%20run%20the%20query%20and%20get%20the%20Log%20Analytics%20data%20into%20your%20report!%3CBR%20%2F%3EYou%20may%20publish%20the%20Power%20BI%20report%20to%20Powerbi.com%20and%20set%20a%20refresh%20schedule%20to%20your%20liking.%3C%2FP%3E%0A%3CP%3EHope%20this%20helped.%3C%2FP%3E%0A%3CP%3ER%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

I am trying to push Log Analytics data into PowerBI with a custom refresh schedule. Since this is not already supported by PowerBI I am trying to push it into a SQL database instead which is then connected to PowerBI. Is there a specific script I can run to achieve this and then use a task scheduler to push the data regularly? Suggestions for a different approach will also be helpful.

 

Thank You

1 Reply

Hi @Mitali645 

Log Analytics and Power BI work really well together.

All you need to do to use LA data in Power BI is:

1. Compose the query you want to use in Power BI in Log Analytics.

2. After you are happy with your query click "export" and use the 'export to Power BI (M query) option to create a Power BI friendly version of your query.

3. Open Power BI desktop

4. Click "get data"

5. Select "blank query" from the Get data options

6. Click "Advanced Editor" and paste the content exported from Log Analytics in the window that opens

7. Click "ok" - note - you may have to authenticate with Azure at this point

You are all set! Power BI will now run the query and get the Log Analytics data into your report!
You may publish the Power BI report to Powerbi.com and set a refresh schedule to your liking.

Hope this helped.

P.S

If you still want to - you can also integrate Log Analytics with SQL.

I usually use Microsoft Flow or Azure Logic app to create a flow that runs a query in LA and writes it to SQL - however, I think it will be overkill for what you are trying to achieve here.

R