How do I: Monitor the Business Central Job Queue using Power BI?

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:

• blog/JobQueue at master · srenders/blog (github.com)

and feel free to leave your comments, suggestions, …