Forum Discussion

Tribunal's avatar
Tribunal
Regular Visitor
2 years ago
Solved

Weekly metric snapshots

Cheers all!

 

I have some OData feeds that are continuously updated from MS vulnerability feeds.  This is a new way we are tracking.  When I do this manually, I have a spreadsheet and document the numbers of Critical, High, etc. every week and then create a graph.

 

Example:

DateCriticalHighMediumLow
5/6/247142128
5/13/245101520

 

Now that I am using PowerBI with constant feeds, how can I create the above in a visual?  There is nothing in the tables that organizes them like this since it's always updating.  There is a "First seen" and "Last seen" field that I have been trying to key off of but I've quickly learned those dates can't be trusted in any way.

 

Is there a way to take like a point-in-time snapshot at each week interval and have it record the numbers in a visual, so that it updates automatically every week by adding the new week's date and numbers?  Hope this makes sense.

 

Thank you!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Tribunal ,

    Based on the description, you might be able to use power bi helper to get snapshots.

     

    You can view the following documents to learn more information.

    Scheduled Snapshot of the Power BI Data and Report - RADACAD

    Solved: "Snapshot" of Power BI Report data - Microsoft Fabric Community

    Solved: Create measure or table with weekly trends from a ... - Microsoft Fabric Community

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Tribunal's avatar
    Tribunal
    Regular Visitor
    2 years ago

    Thank you, those links really helped me learn the logic behind it which allowed me to frankenstein my own solution. 

     

    For future reference to others, I created a workflow via Power Automate that updated an Excel table in Sharepoint based on the source table (easy to find tutorials on YouTube) .  Then created measures for each severity and added them to a separate table, then created a graph visual.  So when I click on the Power Automate button every Monday, it adds the date and number of Criticals, Highs, etc. in a new row of the Excel file, which then updates the table in PowerBI. 

     

    I'm sure this is a very amateur way of doing it but it got the job done.

     

    Breakdown:

    Here is the source table (direct feeds from the vuln scanner):

     

    Here is the table of measurements, which are all measures from the table above in the vulnerabilitySeverityLevel, which has one of the four severity labels in each row (which is why I had to create a measure).  Note:  I did not use the Date field since the workflow gives you an option to add that, so you can ignore it):

     

    Here is what the Power Automate workflow looks like (The severities all point to each individual measure from the Metrics table above):

     

    Two things to always remember...1. When creating a Power Automate button in PowerBI, make sure you have already added the fields/measurements you want to use in the "Power Automate data" box, I had to delete a workflow and button and redo it multiple times because I kept forgetting that step and it doesn't seem to automatically refresh if you add/remove and 2) Make sure your Excel file is a table (Insert > Table)...or it will not give you the right headings within the workflow.

     

    One issue I can't figure out is how to make the workflow add a timestamp that is only the date and not the date, time, etc.

     

    I'd love to find a way to make this fully automated on a schedule but the more I try to create it, the more confused I get and things keep breaking.