Building a HaloPSA runbook that pulls Microsoft 365 groups from CIPP

Robbie: Hello, as you may notice I'm not Connor. I'm Robbie and I work here with a lot of our clients on what Connor likes to call the fun stuff in HaloPSA. Today I want to talk to you a little bit about runbooks. We're going to be setting up a runbook which features a lot of the main things which runbooks have to offer. We're going to be looking at creating an automation which runs on a daily schedule to import all of your Microsoft 365 groups from all of your customers using CIPP and store them at the customer level using a custom table. We can then utilise this data inside HaloPSA and for example a new user form, so let's get into it.

So this is our sandbox HaloPSA environment and I'm going to head over to configuration and custom integrations. We're going to start off by creating a custom integration for both our HaloPSA instance and for CIPP. So we're going to go to custom integrations, we're going to create a new one. I'm going to call it Renada YouTube HaloPSA. In the base URL we're going to grab our Halo instance and we're going to use /API because that's the API endpoint or the base URL for all of our custom, for our Halo instance, and we're going to set the authorisation to OAuth 2.0. We're going to head over to integrations again and we're going to go to HaloPSA API and we're going to create ourselves an application to connect to our own Halo. So we're going to call this YouTube Robbie.

We're going to set the authentication method as client ID and secret, and we're going to copy down the client ID and the secret. You may want to create an API only agent to log in as. For the moment I'm just going to use myself. Once we copy down this data, what you may well find is that the client ID changes. So I usually copy the client secret, wait until I've saved it, and then copy the client ID. For the moment I'm going to set permissions as all. I believe in order to do all of the things that we need to do we're going to need it to be all, but we can review that at a later date and hit save.

Now we're going to copy down that client ID. We're going to head back to our integration and we're going to add in our client ID, our client secret, and our scope of all. And the authorisation access token URL which is /auth/token. Perfect. All of that information can be grabbed from the HaloPSA API details here, authorisation server and the resource URL. What that allows us to do is now connect and query any information inside our HaloPSA instance. So just as an example, if I was to get tickets, I could do /tickets, hit test, and it's going to respond with a bunch of tickets and a bunch of information about tickets in our instance. So we now know that our connection to HaloPSA is working.

Next we're going to create a connection to CIPP. YouTube. To do that we need some different credentials which you can get from your CIPP instance. If you are hosted you will need to request access to CIPP. But we're going to add these in. So this is my base URL. Again we're going to use our OAuth 2.0, our token client ID, secret, and the scope. The scope uses the Client ID default. The login URL is going to be your tenant URL, or if you're hosted it will be your hosted URL. Again, all of this information can be got from the CIPP help desk. If you are hosted, customer again, we can test this by doing a command against the CIPP API. So in this case we're going to do list logs, hit test, and we're going to get a bunch of logs return to us. So again we know that's working. Perfect.

Next we're going to head over to custom objects and custom tables, and we're going to create a table which is going to store all of this information at the customer level, call it Microsoft 365 groups and give it a name. And then we're going to link it to another entity, and in this case we need to link it to the client entity because we want to show it at the customer level and we want to link it to all of our different customers and hit save.

Once we do that we can then edit the table and we can go to the field list and add in some fields. We're going to add in two fields. We're going to add in the group ID which will be the GUID of the group, and we're going to add in the group name.

Once we save those, what we'll see is that we can't actually view this table at the customer level yet. In order to do that we need to create a custom field which contains the table and create that at the customer level. So we need to go to custom fields, select the entity of customer, then create a new custom field which we'll call our Microsoft 365 groups table, going to select the type of field of a table, and we're going to link it to our new table that we just created, hit save.

Now if we go to a customer and select the customer and go to our custom fields tab, we have our table that we just created. We can move the table to a new group by changing the tab that it appears on, and we can move it to our customer information tab or even the details tab of the customer. So if I go back to my customer list, select my customer, at the bottom of the details tab, which is not a very good place for it, but at the bottom of that tab I can then see the table.

Now this is great in itself because we could start adding information into the table and storing data against that customer which we can later pull into a custom field. However, what we really want to do is pull that information via CIPP so that we don't have to maintain this list manually. And this is where we're going to get into the runbook. So to do that, we're going to head over to configuration, integrations and custom integrations. We're then going to create a new runbook and give it a name, so Microsoft 365 groups import, and then head to the flowchart.

When we click into the flowchart it will open up the first step of the runbook, and in this case the first step of the runbook is going to be get all clients. And what we're going to be doing is we're going to be getting all of our clients in our HaloPSA instance and pulling through their Azure tenant IDs which we're saving using the CIPP integration.

So we're going to add an action that action is going to execute an integration method. And for the method we're going to select new method. At the top here we need to select an integration and we're going to select that YouTube HaloPSA integration that we just created. We're going to create a method which we're going to give it a name so get all clients, and we're going to hit the client endpoint. So we need to do /client, and we've got a bunch of additional parameters that we can add to the client endpoint. Those can be viewed inside of the Swagger JSON. But one of those is to include ID only, which allows us to retrieve all of our customers in one list without having to paginate through. And if we include Azure tenants it will then present us with the Azure tenant IDs for those customers.

So if we hit test on this you can see that we get a response, and that response has our clients and has all of the IDs of the customer and their Azure tenant IDs. If we grab all of this JSON and head to the website JSON viewer, we can format that so that we can view it in a nice easy way. Right, and we can see that in this case I've just got one client that has one Azure tenant. We're going to build this in a way that will handle multiple customers and also multiple Azure tenants if you have those for each customer as well.

So once we've done this, we need to capture all of the clients which are in this JSON object. To do that we need to get this clients array here. So we're going to go back to our integration, we're going to go to output variables, and we're going to output an array called YouTube clients array. To get that we will need to do our response, and our response is everything that we see here, and then to get the clients we need to get the clients value. So we're going to do response^clients. If we save that, test again, we should see that we now get our YouTube clients array variable, output variable, and in there is a slightly different response which just contains our clients. So that's perfect. We've got all of our clients and inside each client we've got their Azure tenants.

So going to click save and then we're going to select that method and hit save. So once we get all of our clients, what do we want to do next? Well, next we want to iterate through all of our clients. So we want to iterate the clients array. And what I mean by that is we want to selectively go through every single one of our clients and do the next action or actions that we're going to do for each one. So it's a bit like in PowerShell we're doing it for each loop.

So that is going to be an action and the action type here is going to be array iteration start. The array that we want to iterate is going to be our YouTube clients array. And inside of here we also have output variables. In here we need to add an output variable. And what do we want to collect or use for each one of our clients? Right, so if we look at our client, we have an ID and then we also have another array of their Azure tenants. So we want to capture ID. Sorry, so we're going to use response ID and I'm going to add an exclamation mark on that so it just gives us the value. It doesn't include the quote marks around it. Just because it's a little bit easier to work with. We're going to capture that as an integer and we call this client ID.

All of your variables have to be unique, so I'm just prefixing these to make sure that they are. And then we also want to capture the Azure tenants. We're going to capture that in array again. And if we go back to JSON view we can see that we have this object called Azure underscore tenants. So we're going to get response.azure_tenants. Now we can't test these unfortunately, you just have to rely that they're correct, and you have to work through them if they're not. But we can see that we're looking inside of the clients object or array, we're getting the ID, and we're getting the Azure tenants.

So if we save that, that will now start our clients array, and we have two options here: has elements or has no elements. Now if it has elements, we then want to iterate through all of the Azure tenants for that customer. So we're going to iterate Azure tenants, and we're going to do another action which is going to be an iteration start, and that's going to be our Azure tenants array.

And inside the Azure tenants array, what information do we want to capture? We want to capture the Azure tenant ID because that's what we'll be using later to query CIPP. So in our output variables we're going to create a new one. I'm going to call this Azure tenant ID. We're going to capture that as a string which will be our Azure tenant ID. I'm going to again add the exclamation mark on the end. That will just trim off these quote marks at the end. We usually end up adding these back in, but I find it a lot easier and a lot more controlling that I can add them in at my own will rather than always being there. In cases where I want to remove them, so I'm going to hit save and save.

So now we're getting all of our customers. We're iterating through all of our customers, and then we're iterating through every Azure tenant for that customer. If that Azure tenant exists, the next thing that we want to do is go to CIPP and get groups. Right, we want to get all the groups that exist for that tenant.

To do that, we need to create an action and then we're going to create a new method, and then we're going to find our CIPP integration that we created earlier. To do this, we're going to hit the endpoint in CIPP called list groups. In CIPP we can add a tenant filter to specify which tenant it is that we want to get groups from.

And to do that, we're going to need to save this and come back to it. We need to save our get groups for tenant, hit save, and then find our method and hit save.

Now at this point I'm going to save the runbook. I'm going to refresh the page. I'm going to go back into my flowchart, and I'm going to edit this get groups action again. We're going to edit the method. And you'll see now that some new variables have appeared.

Now, if I remembered what the name was, it wouldn't matter, but I want to add in that YouTube Azure tenant ID into the tenant filter. Right, and that's because we've captured the tenant ID from here. So it's this that we want to get.

Now when I went to this the first time, that value did not appear here. So I needed to save it, come out, come back in, so we can grab that and specify that. Now if we want to, we can test this, and it's going to pop up and ask us to just provide whatever this variable would be. So if we grab that, we can then grab our Azure tenant ID from this customer and paste it in. We should see that CIPP will return us a bunch of groups. Perfect.

So once CIPP provides us the groups, it's actually providing us directly with an array, and that array is all of the groups that are provided. So if we copy all of that response again, we're going to go back to our JSON view. I'm going to create a new tab and I'm going to paste it in and format it.

So again, what we see is we've got each object here, which is quite big. And if we go to the viewer we can actually see the entire object and all its nested properties. So here we want to capture this array again so that we can add all of these groups to that custom table that we created earlier.

So we're going to go to our output variables, and in this case the response is directly all of the groups. So to get these groups, we're going to call this client groups. We're going to save in array again, and this is just going to be our response.

So we can save it and save that. Now we're going to create another array iteration to iterate through the groups for that customer. And that's going to be an action. Array iteration start. And again we're going to need to cancel it, hit save, refresh the page, and go back to here.

We can do an action, array iteration start, and then select our YouTube client groups. Again, we need to capture some information for each one of those groups. So what information do we want to capture? We want to capture the group ID as a string, and that will be the response ID. And we want to capture the name. So if we go down we have a display name here, which is probably going to be the best thing for us to capture. So we want to grab displayName.

So I'm going to go back to our integration, grab response.displayName. These are case sensitive, so you see here they're using camel case, I think it's where lowercase and then uppercase. So we need to make sure we capture that. And we'll call this group name.

Now we could capture as many properties as we want to at this point. But I'm just going to capture these two for now, hit save.

So now we're getting all of our customers. We're iterating through all of the customers. We're iterating through each tenant of each customer. And then we're getting all the groups from CIPP, and then we're beginning to iterate through those groups.

So the next thing we need to do is we need to update that custom table that we created earlier with all of this information from the group. To do that we need to do a new action which is going to be a new method, which will use our HaloPSA integration, and we're going to be hitting the custom table endpoint. And we're going to call this update Microsoft 365 groups.

Go to the body, set the type to be a post request because we're adding data, and set JSON. Now I'm going to paste this in because I've done a lot of the work previously. But we need to post to the custom table endpoint with all of the information we want to provide.

Now there's some information here which is not obvious and actually we need to change. So we need to go and find how do we get to post to the custom table? So if we go to our custom table by going into custom objects, custom tables, Microsoft 365 groups, we can see that we can actually import data via for example a spreadsheet. So we're going to do that. We're going to hit import data and we're going to go and download a sample of the custom tables spreadsheet. Going to paste that in here and then we're going to open it up.

To import to a custom table we need to provide a key, and the key in this case is going to be the customer ID that we want to import the data for. So in this case I'm going to use key of 12, which will be my own organisation, so that we can just import data to us. Again, this is just for testing at the moment. But that's what that key is.

Column one and column two relate to our custom field names of our columns here. So we're going to grab those and we're going to update this and we're going to update this one. Now in this case, value one and value two are fine. I just need to put some data into this table so that we can capture what's happening in the developer console when we do so.

So I'm going to open up my F12 menu. I'm going to move that over to the side and keep the network tab open. I'm going to click on import data, and this time I'm going to go to my downloads and select that custom table that we just imported. And you can see here we've got our key of 12, value one and value two, and a table ID of 107.

Well, we can already see we've got a table ID here of 105. So let's update that to be 107. And then this value, which is the same, we'll update it to be 107.

If we then go to our custom table again and hit start, what we'll see is a post to the custom table endpoint. And if we view the source and again pull this out to JSON viewer, I'm going to create a new tab. We can format it and we can see what's been pulled through.

So we can see that it's asking us for a key, the group ID, and the group name, so our two columns, a row number, an import, is it an import, yes, and the import type in this case of XLS, but in our case it's going to be a runbook.

So we want to copy this out and put this into our integration. In this case, instead of the group ID, we're going to use our new group ID name. I'm going to update this. I'm going to update this. I'm going to get rid of the description because we've only got those two values in our custom fields.

We then need to provide those actual values of the things that we're going to be updating, right? So in this case we again need to grab our ID with our ID. So this one is our group ID like this. And our group name, which is going to be this with our group name.

We don't need the description, so I'm going to get rid of this value in here. And this is what our import should look like. We'll keep a copy of this in the description so that you can grab it out if you need to.

The client ID here we actually don't have specified yet. So I'm going to save this. I'm going to set it as our method. Going to save it, and I'm going to go right back to get all clients and iterate clients array and view what we've got. Oh, we have saved it. It's YouTube client ID. I'm going to grab that. I'm going to go back to our method, edit it, go to the body, and update this to be the correct value.

Also, we need to update these values: group ID, and then we can hit save. Perfect.

So once it's updated the table, we then need to move on and iterate back through the next group. Right, once we've added that group to the table, we then want to start the next one. So on successful, we need to do an action, and the type will be array iteration next end, and we're going to be looping through the groups.

Hit save, and what will happen is we'll get this line here which goes back to the start and goes back to the groups. So for every group that we have in this array, it will add it, and it will add each one until all of them are done.

When it finishes, we then want to iterate through the Azure tenant. So we're going to set this next step iterate next Azure tenant. We're going to set type of action iteration next end Azure tenants, hit save.

And just so we can see it all, we can see that's moving back to the next tenant, right? And then when the Azure tenant finishes, we need to move to the next client. So we're going to select an action iteration next end, and we're going to go through the clients. Hit save. We can see again this is going all the way back to the start.

And that is broadly it for this. We need to add some fault tolerances so if any of this fails we can error out. And to do that we can just create a failed step with the type of end failed. For the moment I'm going to link them all to the same failed end. But if you wanted to, you could put each individual failure to its own step. That allows you a bit more granularity in understanding where it's failed.

Now, if the groups is empty, we actually want to move to the next Azure tenant. We don't want to fail if there's no groups. We just want to move on, right? And that's the same if there are no Azure tenants for this customer. We want to move on to the next customer. In this case, because if they don't have an Azure tenant, we don't want to fail. We just want to move on to the next one. And also, if get groups fails, we're going to move on to the next Azure tenant. Just again, skip that one rather than fail the whole runbook. We'll just skip on to the next one, and we can maybe pick that one up later, right?

We could again add some more error handling in here, but this will do us for the moment. If it fails to get all clients, if the client array has nothing, then that's failing because it should always have some clients. And if we fail to get some clients, we're again going to fail the entire runbook. Perfect.

So everything is built now, apart from we need to define success. Right? So when it's completed going through all of the clients, collecting all of their tenants, getting all of their groups, adding all of their groups, and all of that finishes, it's then ended successfully.

We're going to hit save, and we're going to go and test it. If we go to the details tab we can scroll all the way down and we can hit run now. Run now.

Once we click run now, we'll see it's whirring away. And there's a couple of steps which I didn't name. And the reason you name them is for this very reason. Let's rename this to update custom table. Let's update this one to iterate next group.

And we can see that that's actually been successful. If we go into the log, we can see everything that's happened. And if we go into request data, we can see each individual group being added to the table.

So now we can head over to custom tables, go to our customer, this was the customer that we had, scroll down, and we can see all of those groups have been added for that customer.

And that's probably everything for today. I don't want to get too complicated. There's more stuff we can do with this, but this is already probably complicated enough and probably already gone on for far too long already. I just wanted to show you a bit about runbooks, show you how we can start to import data from external sources, store it at our customer level, and then again we can later use that with some custom fields if we want to inside of, for example, a new user form.

I've been Robbie. This has been a very long video, and hopefully it's been really useful for you. Any questions, by all means let me know. Thank you.