Exemplo n.º 1
0
 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);
     }
 }
Exemplo n.º 2
0
        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);
            }
        }
Exemplo n.º 3
0
 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));
     }
 }
Exemplo n.º 4
0
        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 ?? ""
                    );
            }
        }
Exemplo n.º 5
0
        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);
            }
        }