Simple Query Auditing in LightSwitch

Introduction

On this blog you will find several articles on audit. So far, this audit was related to the save pipeline: who changed by data.

This article will cover audit from the perspective of the retrieve pipeline. It’s a very simple solution, simply using all LightSwitch goodness.

What do we want to know?

screen

 

We want to know:

  • who queried ?
  • what’s the name of the query?
  • when was the query executed?
  • how many hits did the query return?
  • what were the query parameters?

Note, that in the silverlight client, we have also the possibility to do a “quick search”. As you can see in the above screen shot, the Quick search is available as well as a query parameter.

Which tables do we need for our query audit?

A QueryAudit table:

queryaudit

 

with a one to many relation to QueryAuditParameter:

parameter

 

 

How to keep things secure?

Obviously, we want that no one can alter our audit data. That is very easy to accomplish.

We first create a special permission called “CanManageQueryAudit”. We never give this permission to a UI user, we will only use this permission server side via the permission elevation mechanism.

So, the Can CRUD method goes as follows:

partial void QueryAudits_CanInsert(ref bool result)
        {
            result = this.Application.User.HasPermission(Permissions.CanManageQueryAudit);
        }
        partial void QueryAudits_CanUpdate(ref bool result)
        {
            result = false;
        }
        partial void QueryAudits_CanDelete(ref bool result)
        {
            result = false;
        }

        partial void QueryAuditParameters_CanDelete(ref bool result)
        {
            result = false;
        }
        partial void QueryAuditParameters_CanUpdate(ref bool result)
        {
            result = false;
        }

        partial void QueryAuditParameters_CanInsert(ref bool result)
        {
            result = this.Application.User.HasPermission(Permissions.CanManageQueryAudit);
        }

 

The query audit machinery

We hook up the query audit machinery in the Query_Executed method. Obviously, when you have several data sources in your project, you’ll need to do this once per data source.

Inside the ApplyQueryAudit method we use the powerful permission elevation mechanism and simply “inspect” the query.

partial void Query_Executed(QueryExecutedDescriptor queryDescriptor)
        {
            ApplyQueryAuditing(queryDescriptor);
        }

        private void ApplyQueryAuditing(QueryExecutedDescriptor queryDescriptor)
        {
            using (this.Application.User.AddPermissions(Permissions.CanManageQueryAudit))
            {
                if (queryDescriptor.Name == "QueryAudits" || queryDescriptor.Name == "QueryAuditParameters")
                {
                    return;
                }

                ApplicationData dataService = this.DataWorkspace.ApplicationData;
                QueryAudit queryAudit = dataService.QueryAudits.AddNew();
                queryAudit.QueryName = queryDescriptor.Name;
                queryAudit.UserName = this.Application.User.Name;
                queryAudit.QueryDate = DateTime.Now;
                queryAudit.RawQueryString = HttpContext.Current.Request.QueryString.ToString();
                IEnumerable<IEntityObject> results = queryDescriptor.Results as IEnumerable<IEntityObject>;
                queryAudit.Hits = results.Count();

                //the quick search
                var searchKey = HttpContext.Current.Request.QueryString.AllKeys.Where(k => k == "_search").SingleOrDefault();
                if (searchKey != null)
                {
                    var searchTerm = HttpContext.Current.Request.QueryString.GetValues("_search").FirstOrDefault();
                    QueryAuditParameter parameter = queryAudit.QueryAuditParameters.AddNew();
                    parameter.ParameterName = "Quick Search";
                    parameter.ParameterValue = searchTerm;
                }

                //custom query with parameters
                foreach (var item in queryDescriptor.Parameters)
                {
                    QueryAuditParameter parameter = queryAudit.QueryAuditParameters.AddNew();
                    parameter.ParameterName = item.Name;
                    if (item.Value != null)
                    {
                        parameter.ParameterValue = item.Value.ToString();
                    }
                }
                dataService.SaveChanges();
            }
        }

 

Conclusion

Simple problem with simple solution !