Power BI , Dataflow , for transactional and histor...

Anonymous · ‎05-19-2022

Data source: Azure

Below query is based on Events which are registered and cleared after a while.

Heartbeat
// list records for last 30 days:
| where TimeGenerated > ago(30d)
| summarize LastCall = arg_max(TimeGenerated,*) by Computer
// retrieve machines that have not sent a heartbeat in the last 4 hours:
| where LastCall < ago(4h)
| project Computer, LastCall,
timestamp = format_datetime(LastCall, 'MM-dd-yyyy hh:mm'),
startofday = format_datetime(startofday(now()), 'MM-dd-yyyy')
,VMUUID,SourceComputerId,ComputerIP,ResourceId
| sort by LastCall

The above KustoQL query runs every day, so today it may return 10 Computers (10 events), tomorrow 7 computers (7 events), etc.
Want to keep records of all events (today, tomorrow, etc.) for 31 days.

How to use Power BI or Power BI Dataflow to achieve above ?

v-jingzhang · ‎05-26-2022

Hi @Anonymous

Power BI and Power BI Dataflow are not able to keep the historical queried data for you. They are not databases or data warehouses that are usually used to store historical data.

As you are using Kusto query, you can export data to somewhere, for example a SQL database in Azure. Power BI can access this kind of data source. Then if you want to create a report, you can connect Power BI to this database to get data for the report.

Reference:

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/management/data-export/

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/management/data-export/export-data-to-sql

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.