Audit logging in Servoy

Good morning. Good afternoon or good evening depending on which part of the world you are in my name is Stephen porn I am with professional services here at Sir boy and with me as always is Sean Devlin Morning Sean party on Party on Sean party on Today Sean is put together a demonstration of audit logging audit logging is one of those things that sort of falls by the wayside Like commenting your code and documenting your solutions But it is one of those things that really separates a good solution from a great solution And we’re looking forward to learning more about it today So with that I will hand it over to you Sean although I will say one thing If you do have questions, please enter them in the questions panel and we will do our best to answer them at the end of the webinar All yours Sean Okay, thanks Steve my screen is visible. It is Okay, and I’m guessing you can hear me because you’re sound wanting to me. You sound perfect Okay, wonderful. Yeah, so Here we go again number 38 on the bi-weekly series We are doing one of those non-functional topics that is just one small part of a big platform and we try to get all these little Topics in over the course of a year and record them. So we have kind of in our video archive of documentation So if you missed any of the prior webinars And you see the list up here and if any of them look interesting to you you can always go check them out on the recorded version As is the tradition we will start by showing you something and then and then we’ll sort of do the overview with some slides We want for you to death with a bunch of slides today. We’re doing audit logging which is essentially logging everything that a user might do to a table So we’ll give you an example of how to set it up and then also once the log data is generated How does one sort of interact with that log data and then we’ll discuss some of the best practices? So I’m gonna switch on over to An application that I’ve set up quickly here, and I’m gonna log in The reason I have a login screen is because audit logging is tied to security and To the user that’s logged in so it makes sense that we have a current user when we do audit logging so One of the one of the things we might want to do when we are audit logging is See what a user has done to a particular record So in this case, I just put a little related Table down below. We’re looking at a customer record and we might want to see the audit history just for that record so you could see Most recently the user and what they’ve done to to this record So that’s the kind of outcome you got. I’m gonna show you a few more examples But I want to dive right under the hood into the IDE and show you how to set it up and then we’ll we’ll look at a few more examples so The first thing that you need to do when you set up audit logging is just a couple of minutes to really to really set it up One is you want to identify a database That’s going to serve as your log server now. This can be any of the databases that you connect to Quite often people create a separate database To hold their log data That’s up to you. You can also put it in with all your other All your other tables as well So I’ve created a separate database called log server And when you look at the settings for the database connection One of the one of the properties is this Boolean property your log server when you check it that that now Directs all the audit logging to to this server and it’s going to look for a table called log that has the right schema now the first time you set this up When you when you enable log server the create log table and create client statistics table buttons will become enabled And so you can just create the log table and it will generate that table for you If we open up the log table here You can see that it sets up a few columns to capture the log information Essentially what we want to capture is is what the user did and when to which Table and actually specifically the primary key data’s captures. We know which record was affected We know that the log type or the log action type So if they inserted updated deleted And also we have a before and after image for every column that was modified. So it’s it’s pretty Pretty granular the amount of data that’s captured So really you you take those two steps And then you have a log table and you can begin audit logging. However, you won’t automatically start populating this table You have to set up Which tables and in fact which User groups get audited As part of security settings before you really see the auditing so that would be the next step that you take So if we look at a particular entity, I’m going to bring up the Customers table since we were just looking at that You’ll notice one of the tabs in the table settings is called security Now one of the things you do in security is set up whether or not a user can you know insert update delete type of thing but another setting that you might Enable is tracking So you can enable tracking on insert update to lease and also on selection will get to the selection in a minute But for now, I’ve enabled insert update to lead for the group administrators Now in this in this particular example I’m also using this Servoy security extension which we cover in Pretty good detail in another webinar But suffice to say for this example that when when we call log in We’re also passing in the groups You know the security permissions that are associated with the user And that from there the Servoy security and audit engine takes care of everything for you So now that we’ve enabled tracking for this particular group for this particular table We’d be able to see the log entries getting filled in So let’s go back to the running solution and and just sort of see it in action maybe I’ll go to Say a different entity here This one has a little bit of log History already so if I’m gonna if I’m gonna change one of these cities Let’s maybe do Boston and we’ll do USA And at the moment where Where I save You can see that The two new entries came in here In the audit history Because we’re capturing every value that was changed so we actually write two records um For the city and the and the country that was changed Now I had to do a couple of other things to get that to show up here. So let’s take a look at that Because this is just a plain old database table. I was actually able to um To make a relation to it So I have a A relation here called customers to log And this is really just between the customers table and that log table it was generated Now there’s um the pk data is captured and it’s capturing kind of a funny format because we allow for Of course composite keys where multiple columns make up the key So it’s really a delimited string of values and customer Happens to have a single primary key. So it’s just one value, but I did make a calculation in The customer’s table in order to get the format right. I’ll show you what it looks like In my database admin tool you can see this column right here with pk data It actually is the number of characters Then a dot and then the then the characters of the primary key and then it’s separated by semicolon For if it’s composite key. So you just have to know that if you’re working with composite keys you have to You have to sort of mashinated a bit to get it to you know represent what you want In in my case. I made a simple calculation called log pk And you can see that it just Sort of concatenates that length with that Customer ID which is the primary key with the semicolon So that’s one way to do it and then of course in and serve way you can make relations that are based on calculations on the left-hand table. So I just plug that in and away we go so that on the form the customer edit form When we place a grid on that form You can see that the the found set for the grid uses the relation Customers to log and just by simply doing that I get the audit log to show up. Now one thing that I would point out is that The audit logging really can be updating that table or inserting to that table quite a lot So for that reason we don’t do a typical data broadcast on On that table because that would introduce some performance overhead. So what I did is I When you’ll notice that when I when I save the record I just put this back to the UK and save it At that moment. I’m calling the refresh in the In the audit law in the audit history here to force it to reload I’m not it doesn’t automatically broadcast in there and you wouldn’t want it to because it would introduce performance overhead Let’s take a look at just sort of the the audit table by itself I’ve created another view over here where you can see Pretty much all of the fields that are in that table and you can get a sense of how one might analyze this data now Some people just audit everything and they sit on it Just to have as an archive For some day when they have to go back and reconstruct something that happened But other folks might want to actually make it part of their application like I just showed you you can show the audit history of Individual entity This is looking at everything in that table and we could do some sort of filthed ring like You know, what is this particular User doing in say the past day And you can see that it’s filtered down there. Maybe I want to Do it only by Deleats So I can see what record what tables of what sorry Actions have been a deleta and I could even do it by By table so I could Have a look at say the orders table so Because it’s a simple database table you can You can chop it up any way you want A final example of that might be to also summarize it So here I created a couple of charts and you can see that We have the count of the number of edits Based on table and Also based on what the user did So we know we can say hey this this guy here is Working really hard. Let’s give him a race Just kidding And and that we just do by Quearing directly to the database and putting a data set into those charts I want to go back to the IDE here and show you how to enable the Read audit which I had disabled but I’m going to open up the products table and Under security for this group you can see that we’ve turned on tracking for insert update delete Now for this example I will also want to show on tracking on select So now it will actually write an audit log when the user Accesses a record so when they see a record it’s going to audit not only that they saw that record in when But also what were the values when they read it so that later if they changed over time you could still reconstruct that record And know what they had seen I’m going to turn this on but I should point out that this creates really a lot of data You can imagine that if you even if you see a grid of records then then it writes a bunch of data For for that so I would recommend that you use this with care and you’ll see how how much more data we get So I’m going to save that I’m also going to kind of relog into my My application and maybe I’ll use a different user so that it stands out a bit here. I’ll come in as say Juan and log in And so now when I go to my products table it’s actually going to generate a bunch of those log records just by the fact that I landed on that That form So there I’ve read some some records. I didn’t even go to the detail and if we go to our Log here you can see that the in on June 6 At 11 14 a.m All of these reads were we’re audited so it’s basically a fire hose of data If I were to filter that out and say we only wanted updates you would you would see it go away We wouldn’t have as much so we could easily mask that so it doesn’t pollute our view of say What’s been done to a record? This is only what’s been accessed in that record the same is true if we look at the summary here now You can see that there were 1,025 audit records created for that simple view of that that record so Now this doesn’t happen to every time you land on the form because it has to do also with caching So those records are Cashed and we know when they’ve changed and when the user has seen them So it’s not like every time I visit the form it’s going to generate another thousand records But it is a lot of data. So you want to you want to use that with care So that’s pretty much a simple example here. There’s not much to do to set it up And once you have it set up you can kind of chop it up any way you want Let’s go back to the Presentation and we’ll just kind of go over some of the what we saw and also some of the best practices So we really see the use cases for this particular in financial and health care applications and the the read audit that you saw Is we actually introduced that into the product because we had several health care applications where the developers were Requesting to have this feature to be hit the compliance on in the us we have Healthcare privacy laws that require people that store health care data to to audit Who has seen particular like patient records for example One thing that you can also do that’s because you have the before and after images you can you can restore The state of a record at a granular level so If you have some support crisis and One of your users has gone and messed up all of their data. It is possible to On a granular level fix, you know, individual records Although you may take you know, nightly snapshot to the database or something In that case it would be hard to restore The snapshot and then also keep all the changes they made in the meantime or something So this can be nice to say hey well We know what happened and we can we can revert it because we have the full audit trail before and after image So the steps we took is is really simple first we identify the log server That can be any of your databases And then from there we we create the log table inside that server And that generates this one table And then we set up tracking With the granular security settings and remember that’s for every table You can set that up differently also for every table for every group So you could say that certain security groups only are audited That kind of thing you could have a particular security group called audit that you assigned to certain users And then and then they are the ones that are audited Um, you know, it’s linked to internal security Which is complicated topic in and of itself and that’s covered in other webinars So we have that on the recorded archives So I recommend you go back and watch that if you need to It captures essentially who did what When including the before and after images And once you’ve captured the audit records It’s pretty easy to analyze and in our example we saw We saw the user chopping it up by Date and user and table This is a bit of a code I didn’t show you the code but in that case I was just doing simple found set filter params So The tokenized string for you know the list of users or actions We simply added a filter param for that and reloaded the records For the chart example that was done because that summarized data we were actually summarizing the number of edits per user or per table That was done through the query builder and that’s just a few lines code here and you can see that we get a data set Which is essentially providing that number of edits, you know per per user in this case And then we pass that into the chart and it re renders So some of the best practices you will generate a lot of data with audit logging So of course you want to maintain proper indexing so that when you go and you query that data You don’t want very slow results It will grow over time So it might be necessary to archive it say you Only keep 30 days worth of audit in in your database and and and everything else you put into a separate You know archive so that you if you have reads to that database it stays quick The read audit which is a separate option you really want to limit that to scenarios where you really need that data because it does generate so much So quickly that you don’t want to do that unless it’s necessary and if you do do that, you know steps 1 and 2 become even more important Finally It’s quite common that people will use a standalone database just for their log server because it can fill up quickly So you don’t want that to drag on resources of other tables that you query and update often so that’s a pretty common best practice as well We’re gonna take some questions now. I see some questions coming in and I’ll leave up these useful links Hopefully so you can digest them while we answer some questions Steve. What do we have? So we have a couple questions the first question is how do you easily analyze the data when the field changed is an ID And I think I know she’s talking about this is from oil If for instance the country if you’re storing countries in a separate table So that you can generate a valueless but then the data that’s actually stored in a country column for a customer Is a primary key or a foreign key in this case? What’s a good way to be able to analyze the the log data if all you’re seeing are IDs for values changed Right, so it would be a foreign key that’s being changed so you wouldn’t see very easily In that case, you know, you have to make You have to make relations or valueless On those you know, there’s those two columns the old value and the new value Which will just be probably probably integers, right? Sure. Well, that’s an I thought too But except you may be changing Data for lots of different tables Lots of different foreign tables one one thing I thought of is maybe you add a button On the on the table view that you can click that would just say translate keys So that it could then at that point for that record read what table is it talking about and then do a query to find the the actual text values for the for the keys Well, I mean Yeah, it is just a plain database table so you can use it in many different ways That the same time so if you’re looking at at the odd at trail of an individual like you know table like You know Say it’s a country code and it’s orders And if you’re looking at just the odd for the orders You can customize that view and just have a data provider on there that that uses a valueless for example for Even though it’s on the field old value new value it would use your country’s valueless for example Now that obviously wouldn’t apply if you were looking at a mix of other entities at the true time because then it would make no sense or even for that entity if the Changed column was something other than Country code right so you would have a mix even within the same table so you there you have another column called column name So you can capture the column name as well it is captured and so you you kind of have to Maybe make a calculation or something that looks at if it’s you’re looking at a mixer record that says oh well the column name is country code Then you know the value the display value should be This related to you know order to country something like that Or not order but an order audit to country Something like that, but yeah, I mean if you’re if you’re Analyzing lots of tables and then you may generate a lot of these extra artifacts just to view it But it’s just data and serve always really good at chopping that stuff up and displaying it the way you want So there’s lots of ways to do it indeed and Another question from Antonio is good afternoon can that table be filtered by tenant Yeah, that’s a tricky one that we don’t for this particular table. We don’t inject any extra info in that gets captured and there is no It bypasses any triggers or anything because of performance, you know if you’re inserting a bunch of records quickly We don’t run the the table events so It’s not filtered by tenant by default what we do captures the user ID Which will be enough except for setups where The user ID is not unique in tenant. So if the user ID is unique in the tenant then Then it’s not going to help it all to have that but if it is unique in the tenant and the user ID should be enough because you can relate from the user to the tenant Okay What about adding a field and setting a default value coming from a global variable? Yeah, that’s that might be possible. I have to look into that offline I think some some of the typical things that you can do to a table are bypassed because the Record is not generated in a found set and it doesn’t go through the normal Like events, you know like on record insert or before record insert that that sort of thing Auto enter might work because that’s really just a default value and not related to the found set so I’ll look into that One Antonio I think that does it for the questions Do you want to give us a Preview of what our next webinar will be? Yeah, well It’s the same as the last next webinar Which we we added a lay which is announcing some new stuff on the grid components front We’re getting that ready. It will probably coincide with Servoyeight dot three dot one We need to make some changes to the platform to really Take advantage of some of the grid components that we have to offer So we’re going to push that out a bit Hopefully by the next webinar to make an announcement there if not we have more of these sort of non-functional Topical ones on you know kind of in our backlog and and we’ll probably be doing One of those if we’re not quite ready Sounds great Well, thank you Sean and thank you all for attending and we will see you in two weeks

Close