{"id":4303,"date":"2016-06-10T09:22:55","date_gmt":"2016-06-10T07:22:55","guid":{"rendered":"http:\/\/contoso.se\/blog\/?p=4303"},"modified":"2017-06-06T19:22:20","modified_gmt":"2017-06-06T17:22:20","slug":"review-azure-automation-jobs-with-powerbi","status":"publish","type":"post","link":"https:\/\/contoso.se\/blog\/?p=4303","title":{"rendered":"Review Azure Automation jobs with PowerBI"},"content":{"rendered":"

In the Azure Portal, under a Azure Automation account, we can review automation jobs for example number of successfully jobs last seven days.\u00c2\u00a0 This is all good, but let’s say we need to know which service do we spend most automation minutes on? What source start most runbook jobs? How many minutes did each job take? Which jobs ran on a specific hybrid worker group? In this blogpost I will show an example of how this can be accomplished with PowerBI (PowerBI is an analytics service\u00c2\u00a0from Microsoft), a Azure SQL\u00c2\u00a0database\u00c2\u00a0and a runbook \ud83d\ude42<\/p>\n

\"AutomationJob01\"<\/a><\/p>\n

All automation job data can be read with PowerShell, including some information we don’t see in the portal. This information can then be written, with PowerShell,\u00c2\u00a0to an Azure SQL database that\u00c2\u00a0PowerBI reads. We can then\u00c2\u00a0user PowerBI to drill down into the\u00c2\u00a0automation job data. This example includes two major steps<\/p>\n

    \n
  1. A scheduled\u00c2\u00a0PowerShell based Azure Automation runbook gets all Azure Automation job data from the Azure Automation account. The data is in some case modified by PowerShell, for example some characters are replaced before they are stored in the database. PowerShell also calculate minutes spent per runbook job\u00c2\u00a0based on Start and End time from the runbook job data. The last part of the runbook writes the job data to\u00c2\u00a0the Azure SQL database<\/li>\n
  2. PowerBI is configured to use the Azure SQL database as data source. PowerBI\u00c2\u00a0reads the data and present it in a web based dashboard that you can configure\/design any way you want. In the figure below you can see an example of the PowerBI dashboard. On the right side of the figure you can see different parameters that can be used to filter the data and drill deeper into it.<\/li>\n<\/ol>\n

    \"AutomationJob02\"<\/a><\/p>\n

    In the figure below I have selected OMS as technology and all the other fields are adapted to only show OMS related information,\u00c2\u00a0for example only workers that have run a OMS related runbook.<\/p>\n

    \"AutomationJob04\"<\/a><\/p>\n

    If you look at the PowerBI figure you can see that we can filter on technology\u00c2\u00a0and service. This is based on tags configured on each runbook. The runbook in step one export this data too and writes it to the Azure SQL database. With these tags we can group runbooks together, based on owner, technology, service, integration or any other way we need to group them. In the figure below you can see how tags are configured on each runbook. If you run the example runbook with no tags on your runbooks the data export will work anyway, just that in PowerBI you will see “Not Configured” as service, technology and type of runbook.<\/p>\n

    \"AutomationJob03\"<\/a><\/p>\n

     <\/p>\n

    Summary: We use a Azure Automation runbook to write automation job data to a Azure SQL database. PowerBI then reads the Azure SQL database and present the data in a easy way. You can then use PowerBI to drill down into the data<\/p>\n

     <\/p>\n

    Note that this is provided \u00e2\u20ac\u0153AS-IS\u00e2\u20ac\u009d with no warranties at all. This is not a production ready\u00c2\u00a0solution for your production environment, just an idea and an example.<\/p>\n

    Download the example runbook here<\/a>. Download SQL script to setup the database here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"

    In the Azure Portal, under a Azure Automation account, we can review automation jobs for example number of successfully jobs last seven days.\u00c2\u00a0 This is all good, but let’s say we need to know which service do we spend most automation minutes on? What source start most runbook jobs? How many minutes did each job … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":4306,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[64],"tags":[],"_links":{"self":[{"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4303"}],"collection":[{"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4303"}],"version-history":[{"count":9,"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4303\/revisions"}],"predecessor-version":[{"id":4318,"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/posts\/4303\/revisions\/4318"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=\/wp\/v2\/media\/4306"}],"wp:attachment":[{"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4303"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/contoso.se\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}