private Guid?GetUserId(string username) { using (var db = new SqlAuditLogDataContext()) { var sqlAuditLogUser = db.ExecuteQuery <SqlAuditLogUser>($"SELECT * FROM dbo.Users WHERE [Username] = '{username}'"); return(sqlAuditLogUser.FirstOrDefault()?.Id); } }
public object GetUserActivity(ActivityDataModel data) { using (var db = new SqlAuditLogDataContext()) { AuditGraph ret = new AuditGraph(); Dictionary <string, AuditGraphEntry> entries = new Dictionary <string, AuditGraphEntry>(); int max = 0; DateTime start = DateTime.Parse(data.Start); DateTime end = DateTime.Parse(data.End).AddDays(1); TimeSpan range = end.Subtract(start); var filter = BuildArrayQuery(data.Filters, data.Field, data.Databases); List <DateTime> dates = new List <DateTime>(); for (int i = 0; i <= range.Days; i++) { dates.Add(start.AddDays(i)); } foreach (string eventType in data.EventTypes) { AuditGraphEntry gentry = new AuditGraphEntry() { Color = GetEventType(eventType).Color }; foreach (var date in dates) { var query = "SELECT count(*) FROM [dbo].[AuditEntry] " + "WHERE {0} <= [Timestamp] AND [Timestamp] <= {1} AND [EventId] = {2} AND " + filter; var sqlAuditLogEntries = db.ExecuteQuery <int>( query, date, date.AddDays(1), eventType) .ToList(); var count = sqlAuditLogEntries.First(); if (count > max) { max = count; } gentry.Coordinates.Add(new AuditGraphCoordinates() { X = date.ToString("yyyy-MM-dd"), Y = count.ToString() }); } entries[eventType] = gentry; } ret.YMax = max.ToString(); ret.YMin = "0"; ret.XMin = start.ToString("yyyy-MM-dd"); ret.XMax = end.ToString("yyyy-MM-dd"); ret.GraphEntries = entries; ret.LogEntries = null; // timeSet; return(ret); } }
public HashSet <string> GetUsers() { using (var db = new SqlAuditLogDataContext()) { var sqlAuditLogUser = db.ExecuteQuery <SqlAuditLogUser>("SELECT * FROM dbo.Users"); var result = sqlAuditLogUser .Select(x => x.Username) .ToList(); return(new HashSet <string>(result)); } }
public void Log(IAuditEntry entry, string content = "", bool newRecord = true) { Guid userId; using (var db = new SqlAuditLogDataContext()) { var username = entry.User.ToLower(); var sqlAuditLogUser = db.ExecuteQuery <SqlAuditLogUser>("SELECT * FROM dbo.Users WHERE Username = {0}", username).ToList(); if (!sqlAuditLogUser.Any()) { userId = Guid.NewGuid(); db.ExecuteCommand("INSERT INTO dbo.Users (Id, Username) VALUES ({0}, {1})", userId, username); } else { userId = sqlAuditLogUser.First().Id; } } var role = _jsonSerializationService.SerializeObject(entry.Role); using (var db = new SqlAuditLogDataContext()) { db.ExecuteCommand( "INSERT INTO [dbo].[AuditEntry] " + "([Id], [UserId], [Role], [ItemId], [Database], [Path], [TimeStamp], [EventId], [Note], [Label], [Color], [Icon], [Content]) " + "VALUES ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12})", Guid.NewGuid(), userId, role, entry.Id?.ToLower() ?? "", entry.Database ?? "", entry.Path ?? "", DateTime.Now, entry.EventId ?? "", entry.Note ?? "", entry.Label ?? "", entry.Color ?? "", entry.Icon ?? "", content ?? "" ); } }
public object GetActivityData(ActivityDataModel data) { using (var db = new SqlAuditLogDataContext()) { DateTime start = DateTime.Parse(data.Start); DateTime end = DateTime.Parse(data.End).AddDays(1); StringBuilder sb = new StringBuilder(); sb.Append(BuildArrayQuery(data.Filters, data.Field, data.Databases)); if (sb.Length > 0) { sb.Append(" AND "); } sb.Append(BuildArrayQuery(data.EventTypes, "event", data.Databases)); var filterExpression = sb.ToString(); dynamic ret = new ExpandoObject(); var pageSize = 20; var countQuery = "SELECT count(*) FROM [dbo].[AuditEntry] " + "WHERE {0} <= [Timestamp] AND [Timestamp] <= {1} AND " + filterExpression; var selectQuery = "SELECT * FROM [dbo].[AuditEntry] " + "WHERE {0} <= [Timestamp] AND [Timestamp] <= {1} AND " + filterExpression + " ORDER BY [Timestamp] desc" + " OFFSET ({2}) ROWS FETCH NEXT ({3}) ROWS ONLY"; #if DEBUG Sitecore.Diagnostics.Log.Info($"SCS: {selectQuery}", this); #endif var sqlAuditLogEntriesCount = db.ExecuteQuery <int>( countQuery, start, end) .First(); var sqlAuditLogUser = db.ExecuteQuery <SqlAuditLogUser>("SELECT * FROM dbo.Users").ToList(); ret.total = sqlAuditLogEntriesCount; ret.perPage = pageSize; int skip = data.Page * pageSize; var sqlAuditLogEntries = db.ExecuteQuery <SqlAuditLogEntry>( selectQuery, start, end, skip, pageSize); ret.results = sqlAuditLogEntries .Select(x => new BasicAuditEntry { Uid = x.Id.ToString(), Id = x.ItemId?.ToString() ?? "", Path = x.Path, Database = x.Database, Color = x.Color, EventId = x.EventId, Icon = x.Icon, Label = x.Label, Note = x.Note, TimeStamp = x.TimeStamp, User = sqlAuditLogUser.FirstOrDefault(u => u.Id == x.UserId)?.Username ?? x.UserId.ToString() }) .ToList(); return(ret); } }