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);
}
}
