hello and welcome to part five of building a cmdb integration my name is Nick Ryan and today we are going to get into the business of actually starting to use the integration Hub ETL and building out our actual mappings from our staging data sources into the cmdb so what have we done so far so in part four we actually created our integration of actions to query the calm Gateway we then use data sources with custom scripts to call those integration of actions and we parse the parts of data to then be used within our ETL processes So today we're going to start the building process here in integration of ETL so what does that mean well we've done most of the steps so far in terms of defining this application and now we're going to finish out this and start bringing some data into the cmdb the the primary steps are the first time there's really a one-time thing is the first uh transform that we are going to set up we're going to create a new cmdb application as part of that after we do that then we'll start to transform some of the data for each of the data sources we'll start to map that into our various tables and then we'll create the import schedules and and show you how to do some testing at each step along the way and so we are going to talk about some of the more nuanced aspects throughout this so under step number two there a very important concept to understand as part of this will be the source native key so when we actually get into the building process we'll we'll explore what source native keys are in more depth so at this point too you should have your Source analysis document hopefully because this is going to show us what from The Source fields are going to go into which tables and to which attributes in those tables and if we want to do any Transformations on that or set anything explicitly those should be noted as well so let us hop into servicenow so if you navigate to the integration Hub ETL you can open that up so this one right now doesn't have anything defined in it so we're going to start out and create our first one so if you've actually loaded any service graph connectors those would actually show up in that list because this is the same tool which we internally use to create service graph connectors so first step here is going to be to specify some of these specific or the basic details this is where we're going to go ahead and create a new application name so let's call it now song Speedway and in the the third episode of this series we actually talked about the discovery source so we went ahead and created it at that point in time so hopefully viewers is already in here as well and let's just call this Kong we could give it all kinds of other names if we want but call it whatever you want usually it will identify and be tightly correlated with the data source that you're going to use as well but we can say load data from Gateway device then you can navigate to our data source that we created in part four of this series so here's our Kong Gateway data source you do have the option of using specific import sets if you're tweaking that or not so let's go ahead and Mark this as complete when you mark it as complete on this first time on this first step it's actually going to go out and create some more records that won't happen after this because we're going to continue to use this application over and over so we have that and then it should have gone out and retrieved the new import set for us so if you're connected to your third-party data source it should go query that for you let's see if we've got some data in here so yeah during my test run it actually created an import set so here we can see that we've actually got data in here for this particular data source at this point if we wanted to we could do any number of Transformations but let's say I just want to um set of fixed column value because I didn't have something that told me that this was operational at the point that we imported but I'm going to make the assumption that if I'm actually able to query this then it's operational this is just an example of potentially how to set uh static field here that we can then use in our in our mappings so we'll we'll get into some more complicated examples of transforms and some of the subsequent mappings but for now let's go ahead and accept that the third step is to start to pick our cmdd classes that we want to map so here we're just going to do a basic class and we'll get into some scenarios with conditional classes and other mappings but right now we're just going to do basic class so let's find our column Gateway that in here let's go ahead and set up the mapping so this is the part where we're going to start talking about Source native keys and so the attributes that show up here there's always going to be these top two that show up the source native key and a recency timestamp which are not actually columns on the this CI class but they are used in the sys object Source table as basically a level zero type of identification rule so when that identification and Reconciliation engine processes is going to first look at that sys object Source table and try to match a source native key for this class and this data source remember ours in this case was Mal Dash column GW so for that data source feed and this particular class it's going to try to look for that and the other thing that it brings in by default is any identifiers as specified by identification rules so here's the ID is the only identifier on this class so it sets it according to that so here for the source native key I'm actually going to use our connection and credential alias because to me the Gateway should be unique to this connection that we specify and there shouldn't we can't really specify two different gateways from the same connection and credential alias now could you change it down the road that certainly could happen and this would potentially have problems and and instead of create a new one would actually update this one if we stick with this but for our purposes I'm going to work off of the assumption that the connection ID actually represents a unique and unchanging Gateway so we're going to pick some additional columns here I want this admin URL I want a a another there's a database type in here yeah database types I've got a version more down in here we want to capture the version of the Gateway that's running version I'm gonna go ahead and get the name give it a name and let's go ahead and do a description as well all right so whichever other attribute you want besides those for the identification rule uh if there's multiple identification rules it actually shows you all those attributes as well but you just select and add those so we don't bring in all 100 plus attributes for for a CI it's just you have to go and pick the ones that you actually want so here it's really just drag and drop he starts mapping these in and quickly finish up a mapping here actually and and one nice thing is you're if you're mapping through here and you see someone you're like well this doesn't really nothing looks like it fits my purpose for like a name I don't see anything over here that looks like a really good name we can actually click up here to transform and let's say let's create a new transform based on one of these existing Source columns and so I'm going to choose there's many in here and they give you a nice little description of what they do let's go ahead and concatenate something together so let's say we'll just call it the Gateway of Gateway night so my first one let's say we'll use post name and actually I don't want to add make those together so yeah how you actually have to do this like what I need to do first is I want to set it with a static value I don't want to concatenate two existing ones so I'm going to set a fixed value here I'm going to call it Kong so there's our static value and now I can actually concatenate a couple Fields together way here this one was the prefix I'm gonna do prefix and hostname and and let's put maybe an underscore in there when you click apply then we've got our new name here and we can go back to our mapping and simply use that our name the last one here I'll just use this tagline diagram all right so now we've mapped that I don't have any other classes I want to map as part of this particular transformation so I'm going to mark this as complete so relationships we're not going to establish at this point and there will be relationships to the Gateway for sure but we're not going to establish them as part of this so I'm just going to leave that blank and Mark it as complete then we can do a test click run integration and we should see if we got one class being math one new record being inserted because I only had the one you can see any error logs or just all the general activity and you can review the attribute values loaded in for this and so that all looks pretty good to me we can actually say Mark complete It'll ask if we want to roll this back but this data looks good to me so I'm going to say let's go ahead and keep that data in the cndb so now that data is actually loaded into the cndb and at this point we can schedule this I'm going to come back to the scheduling because we're going to have a few a few of these to do and so at this point we've really got this Gateway and it's ready to to go so my handy dandy uh Source analysis tells me that the next thing I should do is the workspaces so let's go look at the workspaces tab doesn't look like there's going to be a whole lot to map there but that's the end this is actually a good use case because this is a non-tmdb table but it is related so if we were to come and look at the class manager and let's look up the Kong Gateway and let's look at these identification rules one of the related entries here is calling workspaces so this is going to allow me to effectively create a related list but also not duplicate things because I don't want duplicate workspaces in there so it's going to say for this Gateway use the ID as kind of a lookup value for workspaces that wants to create a new one of these like we said so we're going to use the now con Gateway application and I'm going to say call workspace data from Gateway spaces and we can find workspace and there's our column workspaces data source that this is complete that should be going and again pulling in an import set if we needed so that we click preview there it is so it went and kind of my connection I've only got one uh workspace as we saw before when looking at the GUI for Kong and it's it's the default workspace so at this point I see I'm just going to mark that as complete I don't think we need to really do any mappings here on that um what I do need to do here though is make sure that I'm associating this to the correct on Gateway so I'm going to need a column Gateway in here and of course are called workspace class or table so here in the mapping for Kong Gateway really the only thing I need to do is specify this Source native key because I don't want to update it this table with anything I just want it to be sure that it Associates it so if I specify the source native key which remember was just our connection and credential Alias then it'll do a lookup and Associate to this particular Gateway as I'm loading in these workspaces and so I want to ask him and set up the mapping here and my ID so actually this payload returned an ID specific to Kong which we could use here because this is a Kong specific table there's not another data source that is going to update this and so this ID value is specific to Kong and this workspace and so I can use it for both The Source native key and the ID value here we can say that we want to just associate it with that con Gateway that we already mapped and the last thing I'm going to do here is ADD the name value in here and so we should have that data loaded up go ahead and activate this I have to associate it actually with my Gateway here as well so this is the non-cmdb association to an actual CI class so I'm going to mark that as complete again here there's no relationships as such this is actually a reference and there's a reference between the workspace to that Gateway last step Let's test it out a few classes Maps Gateway didn't really update anything because we've just used the source native key within the workspace here we can see default that's associated to this Gateway that we used the ID and so on so that all looks good let's go ahead and retain that again I'll hold off on the import test but at this point in time let's go ahead and take a look at our con Gateway see what that so here's our one Gateway and I haven't pre-configured any of these forms so it might not be a whole lot here but let's configure the related list for this just to make sure that we're getting our workspaces Associated how we want to take these a couple off here this here look at um Gateway this one fair enough here's our song Gateway and the one default workspace here so off to a good start there come back and we're ready to do our next mapping so here let's come back to the fun racing with order so it says let's do a load balancer all right so back in eight four let's use this now I'll call them Gateway again and we'll say this is going to be Kong with balancers and we know that they're all called FG Kong all right so here's SG Chrome load balancers they Market's complete and the first time it's doing these it's actually creating some extra records about the uh Gateway or the the actual ETL well get that dismiss all these I got our data that is not much in here so and again I don't think that there's a lot that we need to worry about this um It's always important you know in all of our data sources we were passing the connection Alias ID in here which is obviously key for us tying this back to the proper Gateway and we are going to specify that Gateway is that as our load balancer class all right Gateway again don't need to really do anything except for specify this Source native key value so it knows which one you need to find and here we're gonna actually associate our ID on both the sorts native key remember we're not using the connection and credential Alias ID this is an ID that is actually returned from the payload specific to finding the load balancer so important the distinction to keep in mind there to make sure we're not and they're really the only other a couple pieces of information that we have here is there's an algorithm value I see and the name so just drag and drop wasn't much the Transformer name so this one is complete as far as the mappings go and this one we actually are going to set up a relationship so um our parents is a gateway so it's a load balancer and it automatically populates Us in because it's a depend the load balancer has a dependent relationship on the Gateway so it automatically populates that in if it wasn't dependent this is just a suggested relationship it might bubble out but um you'd be able to pick your own but here it's dependent and so we're going to go ahead and add that relationship so that the ire is happy when we actually pass this payload to it got that we can test this out yeah we had two classes although the Gateway didn't have much there the load balancer was really what we wanted here it created a relationship between them and created just the one new load balancer because that's all I have running on my Gateway at this point in time Mark that complete we'll retain this data and if we come back in here and we reload this form now actually you should see a relationship form open up this dependency View up here yeah but here are depends if you shows that we've got our Gateway and it's related to the load balancer plop out the uh the new and the old form there so we're done with our loading the Kong load balancers and now as we see in our mapping form we have a number of other ones that go through and do so I'm going to pause the video here I'm going to go out and create these but we will come back and talk about it but for the sake of time and speed I'm going to go ahead okay so now we're back and we have all of our data sources completed families Associated those Now with an ETL definition and so at this point we actually have completed our cdb integration so we've got our eight ETL definitions which are powered at the very beginning by these eight integration Hub actions either just the rest calls which then feed these eight data sources and I'm going to come back to the schedules but if we the last step that we didn't do is we were going through each of these definitions was actually creating the schedule and so we can open these up come in and click this last step to set an import schedule what this does is actually click on this gonna open up the job schedule form with a preset filter for the specific data source so you'll see there's a data source and this data source is the one you specified in the ETL definition at the beginning so this SG column Gateway data source so you could go through and do this for each one if you want and then you know to make everything look nice you can click the mark complete and then everything shows up with the check box next to it if you want or what's a little bit easier to me is if you just come into the the scheduler and add each one here and what you notice is in the case of our import for the Kong Gateway is they need to run in a specific order uh if we went back to our source analysis I actually included an import order some of these could run in in parallel and a lot of Integrations can have things run in parallel but for the most part I need them to happen in sequence so the first one is the Gateway and then the workspaces need to be loaded after that so you see it has runs after the parents of the Kong Gateway and so on then the load balancers need to run after the workspaces the targets after the load balancers the services can also be triggered after the load balancers the routes after Services consumers after routes and plug-ins last because the plugins actually make reference to Consumers routes and services so those obviously need to be loaded first um but once you have that loaded then we can actually come into the very first one and this is really the only one that you need to schedule I have it just to run once but if you want it to run every day you can we can just execute that now and then it will uh go and and do a fresh import and then what we have at the end of this is actually our Kong Gateway loaded with all those relationships refresh this here and you see that all these relationships are here down to certain levels I've got the related list of workspaces and consumers and plugins that make reference things and I will actually go back and look at these in more detail in another session but at the end of the day this data model that we looked at back in part two of this series is now being fully populated the way that we wanted to these related tables over here are also being populated and we've done all this using our pipeline of actions to the data sources to the integration of ETO at which point we did the transforms the mappings set up relationships and did some testing in our cmdb is being populated at this point so the last step uh that I would say is important to do and you can go through and activate these um if you want like you'll notice all our service graph connectors do that um but we also don't want to lose our work so remember to come back in to the app engine Studio once this opens up we will choose our now Kong GW application and I think that to the source code repository that we have it linked to or changes go through it look at all those if we want but I'm just going to assume that they are correct and comment below all right so at this point we are done and have built our cmdb integration I will have one more bonus uh video where we'll go in depth into some of these transforms um that you didn't see me build out and we'll look at some of the advanced scripting that can be done in there and how to do set up some relationships and things like that but for now thank you for watching and good luck in building your integration