Real Time log analytics data in PowerBI

[fvl]

Hi,

I am trying to build a real time connection between log analytics and powerBI. A couple minutes delay at most. 

I know there is an option to export your log analytics query and import it in PowerBI. However, the scheduled refresh is maximum 8 times a day.

Could anyone guide me in the right direction on how to achieve this? I've read that PowerBI has streaming datasets, is it maybe an option to retrieve the data from log analytics programatically and push it to powerBI as a streaming dataset? 

Thank you

[Mitali]

Hi,

I'm facing a similar problem with log analytics. If you have found a solution for the same can you please guide me.

Thank You

[Michal_cwiok]

It is feasible, but will hugely depend on your infrastructure.

I have never worked with log analitycs, but my bet would be to go with Azure Functions to schedule data retrieval and then push this data into Power BI using Streaming Analytics. However, it can be a simple python script running on your server.

I have played with streaming datasets last week - it is super straight forward, but also quite limited. Perhaps if you provide more details, I will be of help.

Thanks

[fvl]

Hi Michal, 

Thanks for taking the time to reply!

I have thought of the same thing, but I do not know if querying log analytics every 5 mintues and pushing all that data to powerBI streaming is a good option.

What do you mean when you say streaming datasets are limited? I haven't worked enough with powerBI, sorry if this is a stupid question. I know that streaming datasets only holds the data in a temporary cache, but apparenly a push dataset can also be used for real time datasets. 


We have various resources running in Azure, such as a Service Fabric cluster, Kubernetes cluster, hive cluster, spark streaming, ... We want to gather data from these resources into powerBI to show their activity and also to troubleshoot problems. The people who would use this do not have access to the portal so we can't use Azure's built in monitoring solutions. 

[Michal_cwiok]

No problem, always happy to help.

You have mentioned the limitations that I was thinking of. There is no underlying database, hence no filtering possibilites. As mentioned by Microsoft: "With a streaming dataset, there is no underlying database, so you cannot build report visuals using the data that flows in from the stream. As such, you cannot make use of report functionality such as filtering, custom visuals, and other report functions." Real-time streaming will be an overkill here.

In your case simplest Direct Query will do the job. Try setting up a small Azure SQL Database. Then schedule a job of pulling log data and pushing it into the DB - I do not know how to do it at the moment, but there are probably multiple ways. Later connect to the DB using Direct Query in PowerBI. This way the data will always be up-to-date, as PowerBI constantly queries the DB for the data. The only limitation here is the volume of the data - a report might not be as responsive, as we would like. In your case with 1000s of rows, this should not be a problem.

Let me know, what you think of this. In 2-3 posts, we might have a whole architecture ready 😄

[ross_lambert]

You can use a "push dataset" and the Power BI back-end will keep up to 5 million rows, and yes, you can filter on it, too, and create visuals.

I recently wrote an article with source code to a sample Azure Function and submitted to my company's Blog Keepers. When it gets posted I will let you know.

I might be able to send you a copy of the Word doc if you're interested. PM me.

Thanks,

== Ross ==

[Mitali]

Is the article available now?

[vadinug]

Hi Ross,

I'm interested for the sample Azure Function.

I don't really understand how to pull the log analytics data using Functions, since it's quite a new thing to me.

I came across this thread when looking for pushing real time data of log analytics to PowerBI and interested to try it with Functions and Stream Analytics.