Recently I had a situation where a job queue failed in Business Central. Actually, it had been failing for a few days, and in Business Central, as far as I now, there’s no way, out of the box, to get a notification about that.

So, I got thinking, could I be able to ‘create something’, that sends out a warning when a Job Queue fails?

Another requirement is that I did not want to develop anything in AL. So, the question became, is it possible for a non-AL-programmer to create a warning system, whenever a Job queue fails?

Business Central has APIs, and you can use these APIs in the Power Platform. So, my first idea was to create a Flow (Power Automate) that would send out a warning, when a job queue fails.

Unfortunately, there are no out of the box APIs in Business Central about the job queue. That is a tip for Microsoft, for the idea list 😉

 

Job Queues and entries are stored in the:

  • table(s): Job Queue Entry, Job Queue Log Entry
  • pages(s): Job Queue Entries, Job Queue Log Entries

 

Normally I would then create a small extension, with some new APIs for these tables, so I can use them in Power Automate, but that was not an option here.

Maybe I could use Business Central web services? So, I simply created 2 web services, in the web services page:

However, in Power Automate I did not see the web services, apparently only APIs:

Then I got thinking, Power BI is able to import data from Business Central APIs and also web services.

 

Would that be an option?

 

Of course, and I then developed a Power BI report, based upon these 2 web services only:

In the data model of the report, I simply imported all fields from both web services, and I did it in a multi-company way. There’s also a parameter, that lets you select your environment (production, sandboxes), and then based on the environment lists the companies of that environment.

I then figured out a way to link both tables, because the job id is not available in both tables in the web services, because it’s not present on the Job Queue Log Entries page. So, I created a custom key, in Power Query, in both tables:

Then it was time to create some measures:

And then the layout:

It shows how many jobs are currently in error, on hold, the last error date, and some details at the bottom.

There’s also a drill through:

Voila, that’s it.

 

Then I published the report to the Power BI service, and created a dashboard:

On the Job Queue Status dashboard there are the tiles of interest: # Jobs in Error and on Hold, Last Error and a Business Central tile that links to the Job Queue list.

Interesting is that on a dashboard, on a tile, you can set a Power BI alert:

Next, I also implemented a scheduled refresh for the dataset of the report. With a Power BI Pro license that can be up to 8 times per day and with a Power BI Premium 48 times per day.

 

And voila, Power BI now sends warnings whenever there’s a Job Queue in error:

 

On top of that, a Power BI alert can trigger a Power Automate (Flow):

And with Power Automate you can then take more in-depth action, like for example: send a message in Teams, send a text, …

 

How cool is that!

 

You can download a copy of the Power BI report here:

and feel free to leave your comments, suggestions, …

 

 

 

 

 

5 Thoughts on “How do I: Monitor the Business Central Job Queue using Power BI?

  1. Hi Steven!

    It’s cool, it’s working on our own tenants. Thank you!

    But is it possible to make it work on our client tenant too? So we are delegated admins on a couple of client tenants, and I want to monitor some of them, whether some Job generated an error. I’m looking for some “TenantID” parameter, currently only EnvironmentID and CompanyID supported.

    Thank you again!

  2. Abdullah Zafar on January 19, 2023 at 09:19 said:

    Hi Steven,
    I have exposed an API (Page Type API) for Job queue Entry from AL. But I am not able to find the entity under Business Central connector default triggers. Do you know why we are not able find it,
    I have asked on Dynamics community as well,
    https://community.dynamics.com/business/f/dynamics-365-business-central-forum/476847/consuming-job-queue-entry-in-power-automate
    Do you know any particular reason why I am facing this?

    • Hi,

      Is it for BC Saas or BC On Prem? Power Automate and Power Apps are currently not able to connect to any custom APIs in BC On Prem. Power BI has no problem with that.
      So that might be the reason?

      • Abdullah Zafar on January 19, 2023 at 11:30 said:

        Hi
        Thanks for the reply,
        I am trying on SaaS version. I have used the same API in Canvas app as data source and it’s working moreover, I am able to use functions like (Get records) but not able to use trigger for Job query entry and customer Bank Account table.

Leave a Reply to DodoCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Post Navigation