Halo PSA Usergroup London 25th April – Elastic Cloud

Hey everyone, Chris Timm here from Sondela Consulting. Following on from our very successful first uh Halo PSA user group that we had in London on the 25th April. Uh the following video uh is from one of the members who attended. He's talking about using um Elastic uh reporting with Halo. So, how they've leveraged the API to design something that actually gives them all of the reports that they want um and gives them all of the information um through this uh this open source Elastic um reporting engine.

So, I'm going to play that video after this. Um if you want any more information, I will put them in the comments below. Um and if you want to know anything more about our Halo PSA user groups, uh you can find some information below at halopsausergroup.com. Uh I will also make a video uh straight after this which I'll link to up here somewhere that will talk all about um how to join the the community, that kind of thing. So, look forward to seeing you on the next video and uh thank you very much for watching.

What, three times? Doesn't matter. Uh welcome again anyone that's listening on the um on the uh online. Um I'll I'll introduce you I'm going to talk a bit first about our um PSA journey cuz I think it it sort of it it helps to understand why we're doing what we're doing um and why we're going to such lengths to get we want out of Halo. Um so, we're going to be talking about Elastic reporting.

Has anyone heard of Elastic? No? Good. Sort of good. We'll show you something new.

That's great. Um uh next slide. Uh I've done that bit. Synergy. I've uh you know who Synergy are now.

Has anyone heard of Synergy actually before we came here today? No, so we're not doing something very well. I have. Yeah, great. Well, well, I've met you before, so obviously Yes.

Um so, we um I've got a new guy. Um so, I'm going to go around the room. Um So, this is part of our community outreach. We do quite a bit of MSP community outreach stuff. Um we really believe in the community here, so we um this is one of them, and we also host Tech Tribe.

Anyone a member of Tech Tribe? Yeah. Yeah, good. So, if you um come along to the West London one, it's here. Um the East London one is at ConnectWise.

I guess Yes, this is too. Don't go to that. So, uh or Dave, if you're around the room. Um we started on ConnectWise. Synergy was um we were on ConnectWise for a year, spent quite a lot of money with professional services getting it set up for us.

Um but from day 30, you realize it probably wasn't the right decision. And uh and it was uh we found it clunky, um and it was just felt for a tech company very old-fashioned. And so, um it sort of wasn't doing our internal PR any good that we were in hiring people and then showing them this what looked like quite an old-fashioned system when we're hiring people who are technically minded and sort of naturally quite progressive and modern. Um and so, quite early on we took the decision to start looking around for um a new PSA tool. Um and uh we came across Halo.

We looked at some of the other ones, um came across Halo. And uh the reason we really like Halo is its extensibility. So, it wasn't, you know, it's great that it works on React. Everyone knows React and it's really nice to use. Um it looks good on pretty much any screen.

Um, the buttons and the fields are where you expect them to be. But it was about how it worked with our other applications. And so, um, I firmly believe that the future of tech and SaaS products isn't one system to rule them all because what you tend to get is it's the jack of all trades um saying where and and connect advice for certain things or testament to that. It tries to do everything but doesn't really get it um well enough. Um, and so, we took the um took the decision to change to a best product angle.

And so, um we use HubSpot for our CRM because it's, in my opinion, the best CRM system. Um, and um and there's other applications that we use which are best-in-product for those things. And so, at that point it became this sort of this the decision was which system were we going to use to be the center of that and be the hub to that wheel. Um, and uh um and Halo's API was vital to that because the the fact that it it already had all these integrations, although anyone who tried to work with them will see that they're sometimes a bit limited. Um, but um they have integrations.

They're really open with their API and they they play well with others. You know? And that's something we didn't experience as much in here. So, we decided to go on to um on to Halo. Okay.

And we started the journey. One thing one decision I did make at that point was that, well, if we're going to be linking and working with APIs, who the hell is going to do that? Because, yeah, we've got a tech company with technical people in it, but I haven't got someone who's a a genius with a code and APIs sitting around doing nothing. They're all serving clients. Um, and so, we took the decision to hire a platform developer.

And calling it a platform developer, it's not a coder because it's not someone who's just going to sit and pour over code all day. It's someone who is actually there to make sure that that application speaks to that application. Um, and that is Jake. And so we hired Jake um, three months ago as a platform engineer. And he he comes from a dev background and and also we've worked together for years in the advertising world.

Um, and so Jake is um, his job is to get all of our systems all of the different systems that we use and sew them all together into something that works really well. And each part of that is tends to be we try to make it best in breed for what it sets out to do. Um, and so over to Jake. Sure, thanks. So, um we decided to use Elastic um for our reporting.

Um, now I'm sure you're all familiar with this. This is the current dashboard that we use. Um, it's the built-in SQL-based reporting which is which is great and we've been using it to good success and it's got all the stats you could you could want. And you can drill down per client and and it's basically it's all there. But we got to the point where we reached the ceiling of what this can do.

Um, it was quite rigid in the sense that if you want to change something you have to rewrite the SQL, create a new report, and then update the dashboard. And it's just not wasn't that flexible. So, we needed something that was a bit more um flexible and something that can actually show the data in a nicer looking format and actually present it better. Um, and cuz there is loads of data we have loads of data and Halo creates that on a daily basis. So, um to do that I looked at the APIs.

There was no real um native integration, so we basically decided to build our own. Um So, luckily the documentation that Halo provide for the APIs is actually quite good. Um and so we spent some time working through these and extracting the data using just simple Postman. And um getting the data and actually working through it and working out exactly what it is we want to do with this data and which part of the uh which API we want to use to use the uh data from. Um so, once we got to the point where we've worked out what we wanted to do, it was a case of creating a an application.

So, um I wrote an application that basically sits on our systems that pulls the APIs at Halo every 5 minutes. Um and it's specifically using these ones here. And it's pulling live JSON data. So, every ticket that gets logged, every action, every invoice, every site that gets updated, every client that gets updated or created um gets pulled back to our servers. Um At that point, we then process that data and then push that onto Elastic.

Elastic is basically set up using different indexes. Um And it treats every uh record as such as a document. Um so, each we created index for each type of data format. So, we've got an index for tickets, an index for actions, invoices, sites, etc. etc. Um But, the data that came from Halo was huge, massive.

In the sense that the JSON files were well, as an example, the tickets are 2,000 fields. So, it's just tons of data and it's too much to process. So, uh to get round that, we created a template. So, I built a template and just put the fields in it that I wanted. Um and then what that allows me to do is every time we pull a ticket um from from the API, it then goes through a filter which basically extracts the fields that we need um and then passes that on to the elastic server.

Um what this allows you to do is actually if you want to reduce this down, add or uh take out a field, you can just literally adjust the template and on the fly it will actually um change the data that's coming from Halo. So, what do we do with the data? I'm just going to show you actually what we did with it. Um And this is a live demo, so I'm trying to do a live demo. So, I've actually run a report So, this is basically an example of a an index within elastic.

Um it's kind of like the back end as it were. Um so, for the purposes of demo, I've actually had to remove a fair bit of data cuz the personal information is in there, ticket information, and client contact details. Um but, essentially, these fields are the fields that we've chosen. So, this is a live view of of tickets being logged at the moment. It updates every 5 minutes.

So, you've got like a ticket ID, who who logged the ticket, when the last action was, how it came in, the age of the ticket, so this one's 4 minutes old, um site ID, and over here this will update uh later on with actual geolocation data which a long lapse basically of the uh the person who's logged the ticket. Um you'll see why that's important later. So, this is an example of a dashboard. Um as you can see, it's kind of presented in a a nicer way. Um it actually allows us I'll just scroll down and see.

It actually allows us to see a lot more uh in-depth detail. So, uh we've got here closed tickets, um who's closed the tickets. This is a basically the service desk. Um so, these are all the guys on the service desk. The color is represented to the type of ticket that they're closed.

Um and up here, you've got open versus closed. So, obviously the green is good. Um but, this is basically showing a complete overview of the entire service desk. What's important here is that you can you can do quite a bit of this stuff in you can write a simple write and you can grab a load of data and you can make a graph and things like that. But, um we found this to be so much more cuz effectively, it's just a data lake behind this.

And then, we choose the data that we want to inspect without a uh SQL command cuz it's all it's got the data in the background. And then, we can create these and slice and dice them in in whatever way we want really quickly, really really quickly. Um this is actually closed tickets um uh data. And this is for KPIs that we run one of the on one of the service desk teams, one of the um one of the service desk teams in the UK. What it allows us to do is it shows us who it splits up how many take This is how many tickets were closed that day.

And then, who has closed them, right? So, Nisha has closed probably the most here. Um and it's also got things like medium ticket age age at close. That's how long that ticket was with us. So, from open to close, how long did it that take?

And we're That's one of our KPIs. We want them to be really short tickets. Now, the ideal ticket, one that arrives with us, we close it straight away. We sell something. Uh, we don't want any tickets that last for ages and ages.

Um, and we had to do some manipulation with that because um, there there is some data that comes through with things like quick time tickets and project tickets. We don't want to see those here. That's not important. And so, we're able to really easily So, let me I say really easily, Jake did it. Um, manipulate that data and ensure that what we're seeing here is really quite accurate and because people are being This is people are being judged cuz this is KPIs for individuals, vital that we get this stuff right.

Um, and um, so, yeah, and then the the other stuff, you scroll down, Jake. Um, it's also got things like that where you know, what the category are the categories are for all the stuff that's being closed. That's important to us as well, not necessarily for our KPIs, but it's important that we know where where we're spending all this time and what we're doing. But, the other thing it does is Jake's probably going to show you is it allows you to drill down into the data. And so, now we can see that that's just Jonathan's data.

Right? So, and you can So, he's awesome. We know Jonathan's awesome. Um, this is what he's been doing. These are his closed tickets and open tickets over time.

Um, if you scroll up, we're able to do that. You can drill down and drill down and drill down. That's That's natural. We haven't had to code any of that, do anything. It's built into Elastic.

We're just giving it data. We're just telling it what we want to see. So, when you get when you start a journey of, you know, if we looked at all the tickets that Synergy does, there's thousands over 2 years, we can start to drill down and drilling down is just clicking. Click down, you get figure out. We found the other day why they had 2,000 tickets opened in 2021 because it's all the test data that Halo gave us and they never deleted it.

And being able to see that sort of it's really difficult to see in Halo all that sort of just junk data that's in there without this sort of tool where you can really see it and visualize. Just starting interest in because obviously one of the things is I like about the drill down and I've been familiar with Power BI for a number of years now. I get the impression that Halo are working towards a Power BI integration that's going to be able to do something similar. Where was that something that you explored as well as as to our question is the Power BI as an alternative? We work with Power BI with clients who have Power BI there.

Um I prefer Elastic. It's open source. Yeah. Doesn't cost anything. Yeah, yeah, yeah.

I mean it does. We pay for it to be hosted because we want to rely on their security mainly. Um and um but it's um whereas Power BI tends to have those two sections. You want to either create or you want to look at it. Same that's that's the view.

If you press edit at the top of the app, you're editing that to the dashboard. We can then we can then share this to anyone without having Power BI. It works in iframes and we're going to put the data onto those screens there to show live data. Much because it's open source, because it's um not a Microsoft product, we're not worried about licensing with my sharing of that. Could you look at BrightGauge at all?

We used to use BrightGauge. That's why because we used to use BrightGauge and I think we missed that now. Yeah, yeah, well that to be honest, that is why we ended up looking at this because actually, you know, Halo is one of the other things that I should have mentioned but didn't is that with the Halo um dashboards that's great. That's Halo data. But we're not That's not all the data Exactly.

We want to look at, is it? So Jake is working on We're not showing that today, are we? Jake's working on our Zoom integration. So our Zoom calling We use Zoom phones. So I want to see on here how many calls we're getting. calls are there?

How long are people taking before answering the phone? Yeah, yeah. And that needs to go on here as well. But we couldn't And that's why we were sitting there going I'm sitting there coming up with him to be inspired by BrightGauge again, but don't want to get down that path with one of but Yeah, I know. I have Halo integration with Halo.

Well, but you know what I mean? It's that kind of product. Yeah, yeah, yeah, but They Yeah, and they I mean, from a Halo perspective on dashboards, even the fundamentals are missing. I was talking to Morgan last week about the fact that for every single widget, if you want, you have to write a separate report. That's the thing.

Yes. To modify that and it can take ages. Takes hours because the schema is not great in the sense it doesn't You can't identify the fields easily. So Yeah, it's trying to write SQL commands is cumbersome. Yes.

We're now looking at iFraming this back into Halo. Mhm. So Yes. want We want our Our Our engineers spend their time in Halo. We don't want them coming out and they won't do this. And this isn't This is only for me and Jake to use at the moment while we're while we're developing it.

Um but we will look to put it back in the Halo so that it's it's visible to everyone in there. So once we've We're basically getting the data, we're we're sanitizing it and making sure it's valid and relevant to people and then pushing it back in as a dashboard. From a perspective of how long get this to that point. We've got the data out. How How many hours of time did it take?

What, development time? Yeah. Two months? Uh What, pretty solid? Um I'd say it's 60% 50% near.

It's hard to quantify. But It was all getting the data in Yeah. So, it was because like we showed in the previous slide, it it gives you 2,000 things. Yeah. Yeah.

For every ticket. Imagine that's a that's reasonable amount of data. Um and so, it there was a lot of sort of uh we've chosen that field. Actually, I think it's a different field that we need. So, we need to cross that field out, get a new one out, and Jake would then write the write the code to bring it in.

Once it's in here, once it's in this index, so in here This I mean, I've deleted this and remade it a few times. Takes about 20 minutes. No time at all, really. You just choose what you want on these, and it shows the data. And so, that is just that initial getting building that pipe, and it is called a pipeline.

Building that pipeline from um our server polling um Halo getting all the data in, chewing through it, and then sticking it into um Elasticsearch. Any bottlenecks with your polling? We haven't noticed any. No, I mean, we did As it gets busier and it gets heavier and it gets are you going to start hitting issues with Cuz like when we first started first was like, "Yeah, we can pull it from Microsoft." Then they started throttling stuff. Yeah.

There's going to be a time when Halo's going to go, "Whoa, hold on. You're pulling too much from us. We've got a problem connection." Yeah, and and on they do throttle the SQL connection. So, we did do this on SQL first, and they throttled that to um I think 5,000 records Yeah. Um just do it through the API instead.

We did through the API instead, and um they might be throttling that. But, we we actually don't hit them that hard, do we? No, what we do is every 5 minutes I grab the IDs of the tickets that are modified in last 5 minutes. So, in the last 5 minutes, you can only modify 100 tickets, maybe? Maybe less?

Yeah. Um as a maximum. So, normally it's a lot less than that. So, once you've got those IDs, then you can call on the API. but I rate I rate limit Yeah, but if it I rate limit that so I don't hit the API 100 at a time. I do it a few at a time.

Can you not do it the other way around? Like push out through web hooks. Um You can, but I don't think you can customize them to get the data. I think you can. Yeah, some some the payload.

I did try it. Yeah, it's just the um There's It's not just the tickets I wanted. There's a lot of other stuff that I've taken that's not documented. Yeah. Um so it's not really supported by Halo.

All right, so I kind of like went my own way with that. But um Yeah, it probably some of it is possible with web hooks, but not all of it. Yeah, cuz I think the I'm going to go through this Zomentum stuff. Zomentum's integration from Halo in terms of pushing data from Halo into Zomentum is all through their web hooks. So it's it's at ticket change, site change, user change, or new.

And so there's a series of things. It doesn't direct cuz products products don't push. Yeah. So So not everything is covered, but there are Yeah. a lot of number of them. Um on the every time it changes it pushes out.

Yeah. That's not that the web hooks. Yeah. And that's extensibility and the fact that we can link it to something like N8N. Um it means that actually um that's one of the another reason we went with Halo is just because it's ability, like I said earlier, to play nice with others.

Yeah. And it does have web web hooks. So if we want to do something clever with a web hook from Okta, for example, you know, Okta notices a new user, we can you can you can relatively easily get Okta to push a web hook to Halo, and Halo can create a new user. It's not that difficult. Um yeah, so another another reason we went for it.

Um so that's that dashboard, and we we we actually this is a a dashboard we do actually use at the moment. Um but there are there are there are a few more that we're we're sort of writing. Same data in the background. We're just writing a new dashboard. And like I said, it takes 20 minutes to produce that in a in a way that we want to see it.

Um now I think Jacob hijacked you. Um So this is basically a live mapping view of where all the tickets are currently being opened. Um so this is this will update. So to produce this I pulled the site data from the site API uh and then grab those IDs and then use the Google Maps API to get the long and lats of the location of the user and then push that back to elastic uh to match up the ID of the ticket. Um uh it's just a great way of actually displaying the tickets and where they are.

So if I zoom in So because we're a global company like I said it is actually quite handy to see where are we at where are all our all our So there's metadata of locate location data. Apparently we we did No, it's yeah. The location data doesn't exist. I had to create that. Okay.

So it's it's basically based on so the address information for all the users is in the API. So I pulled that and used the IDs of the user uh with the address and then combine that with the Maps API the Google Maps API to get long and lats and then push that back uh to the ticket. So the ticket's got that information and hence then I can display it on a map view. Um Yeah, and you can just drill down and you can view the um stats on the on the pins. Each pin represents a ticket a live ticket.

Uh yeah, it's just quite a nice um way of viewing data. You can see we've got a lot of synergy tickets. Yeah. We do a lot of internal work. So that's um yeah, it's a lot we could we should probably work everything it we treat everything as a ticket pretty much.

Um Not the ConnectWise way where you meant to I treat I need to get to the toilet and put a ticket in and The red circle is the king. That's like that's like the king going But yeah, you can see we're quite busy in New York at the moment. So what I like about this this is live. These are the actual tickets. So these are being worked on.

So this is a live view of how we are how busy we are at the moment with open tickets in New York. You can drill into those pins and Yeah, so it's got tickets No, not yet. No, but you can do that because of the way they write the URLs and Halo basically can you could we can just stick that ID at the end ticket ID and it will I press a button and it will it will launch that ticket. The it's based on open tickets then it's not a time it's not a time window is it just all open tickets? Open tickets until they get closed.

When they get closed they come off the board. Yeah, it's on another index. It's on a whole different dashboard as well. So I can't show you that cuz it's got too much information on it. Yeah, so that's not we're not we're playing with that.

We're trying to develop it and see what cool stuff Basically this whole thing is about the cool stuff you can do with the data within Halo. So this Essentially here what we're looking at So we're using um here this is actually we're using machine learning to predict spikes in activity or times where there is a lot of tickets being uh logged on the system. So down here this is existing this is the current data. So this is spikes in tickets over previous months uh and you can set the severity there to different so at the moment it's set to critical to display um highest amount. You can see here that averages are up there.

So, what that allows you to do is actually plot that to a the graph that will predict um when spikes will happen. Um again, it takes out that kind of guesswork um and instinct from people that previously would have known when it was a busy period. This is actually based on the data itself and actually um should be proven to actually be more accurate. And what we're planning on that with this, as Jake says, is when we're quite we're actually a medium-size MSP now. So, it's not as when you're I I um I run my own MSP for 20 years.

So, when you're a bit smaller, you just know when you've got something to be busy. You know that as a fair you know when they're out. Oh, they're on holiday, they're going to come back from this break and they'll be busy. But as you get bigger and bigger, you actually sort of lose that um and you shouldn't be. That shouldn't be in your head, you know, you you you have to start finding other ways of up upping the workforce and service desk, for example.

Um and maybe getting people to work late who are in London to support New York or something like that. So, what we're doing or aiming at here is that we can find out we can use machine learning, the data to decide when we need our so-and-so on in the service desk in the UK needs to work New York hours because New York is going to be really busy next week. We can make that sort of decision based on data rather than just knowledge of that's in some account manager's head. Um it's what I think will happen. And that will that will be I mean, we're we we still know when our clients you know, the account manager's thing.

But as we grow, this will become more and more important for us to make real business decisions about workforce and what they're doing at what times. And where they should be based. And where they should be based, etc. Yeah, and that's is that all built is that built into what did you have to do with the machine learning stuff? Is this all part of it or It's it's part of elastic.

It's native to elastic, but you you've got to have the data. You put the data in an index and then it it learns it looks back and it has to produce that. It's a good job then. Yeah, display that right. And I think you know, we have seen the developer as well, but elastic is about 70 quid a month.

Yeah. So it's not this is not an expensive tool. You know, it's actually pretty good value. And you can self-host it as well. If you want to have this running on your own machine and looking at indexes, a lot of people do that.

And you can But you need the development. You don't necessarily know. You can throw any any data at it. To get to where you guys have got Yes. you need someone with Yeah, and so And if if um one thing that we are we are looking at doing is helping other MSPs and providing it to them. Yeah, that's it.

Okay, so you want you want this you want to look at your data in the same way that we use it. Yeah. Well, Jake can help with that. It will be it'll be a sort of T&M for that. And we can do that.

Yeah. Cuz the funny thing is like a lot of it is going to be similar, but then there's custom fields everyone's going to create custom fields depending on their Yeah. Yeah, well that's why when records you need to be able Yeah, that's why I use the template because you can literally just Add them. add or remove hit save and it's done. All right. Yeah.

Yeah, so you said you said you're going to package this and is the plan to sell it to other MSPs or is it kind of Yeah, well we are I was hoping Hey, I'll be here so I can go back to Halo. And be able to see that you know, we're we're doing some quite useful things. Lots of people I think would find this useful. Yeah. Um and um and we can build an integrate.

You know, we can actually build a sort of one click is you know, show us the fields you want and we'll build that, and then um Halo can Halo can use it, and Halo's users can use um as well. Um but uh um we can have that conversation with Halo, but we can also have that conversation with individuals as well if people want this sort of thing in their own system. Um we can quote on that and say Jake can go and install that. Mhm.