public ClientEntryDayDetail(ClientEntry clientEntry, DayEntry dayEntry) : base(clientEntry.IpAddress, clientEntry.AuthenticatedUserName, clientEntry.ClientName, clientEntry.InsertDate, clientEntry.LastAccess, clientEntry.TenantId, clientEntry.TotalAccesses)
{
DayNumber = dayEntry.DayNumber;
Date = dayEntry.Date;
Count = dayEntry.Count;
SourceFile = clientEntry.SourceFile;
RoleDescription = clientEntry.RoleDescription;
RoleGuid = clientEntry.RoleGuid;
}
//esentutl.exe /r svc /a # ?? need toi test this vs /i for current
//esentutl.exe /p .\Current.mdb
private void ProcessDatabase(string dbFile, int year)
{
using var instance = new Instance("dbFiile");
instance.Parameters.LogFileDirectory = Path.GetDirectoryName(dbFile);
instance.Parameters.SystemDirectory = Path.GetDirectoryName(dbFile);
//instance.Parameters.BaseName = "SRU";
instance.Parameters.TempDirectory = Path.GetDirectoryName(dbFile);
//instance.Parameters.Recovery = true;
instance.Init();
var chainedDb = new ChainedDatabase(dbFile);
_logger.Debug($"Setting up dbFile session for '{dbFile}'");
using var session = new Session(instance);
Api.JetAttachDatabase(session, dbFile, AttachDatabaseGrbit.ReadOnly);
Api.JetOpenDatabase(session, dbFile, null, out var dbid, OpenDatabaseGrbit.ReadOnly);
_logger.Debug("Getting Clients info");
try
{
using var rolesTable = new Table(session, dbid, "CLIENTS", OpenTableGrbit.ReadOnly);
Api.JetSetTableSequential(session, rolesTable, SetTableSequentialGrbit.None);
Api.MoveBeforeFirst(session, rolesTable);
while (Api.TryMoveNext(session, rolesTable))
{
var authUserName = Api.RetrieveColumnAsString(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "AuthenticatedUserName")).Trim('\0');
var clientName = Api.RetrieveColumnAsString(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "ClientName"))?.Trim('\0');
var idRaw = Api.RetrieveColumnAsInt64(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "InsertDate"));
var insertDate = DateTimeOffset.FromFileTime(idRaw.Value).ToUniversalTime();
var laRaw = Api.RetrieveColumnAsInt64(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "LastAccess"));
var lastAccess = DateTimeOffset.FromFileTime(laRaw.Value).ToUniversalTime();
var roleGuid = Api.RetrieveColumnAsGuid(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "RoleGuid"));
var tenantId = Api.RetrieveColumnAsGuid(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "TenantId"));
var address = Api.RetrieveColumn(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "Address"));
var totalAccess = Api.RetrieveColumnAsInt32(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "TotalAccesses"));
if (chainedDb.Clients.ContainsKey(roleGuid.Value) == false)
{
chainedDb.Clients.Add(roleGuid.Value, new List <ClientEntry>());
}
var ce = new ClientEntry(ConvertBytesToIpAddress(address), authUserName, clientName, insertDate, lastAccess, tenantId.Value, totalAccess.Value);
for (var i = 1; i <= 366; i++)
{
var dayVal = Api.RetrieveColumnAsInt16(session, rolesTable, Api.GetTableColumnid(session, rolesTable, $"Day{i}"));
if (dayVal.HasValue)
{
var dt = new DateTimeOffset(year, 1, 1, 0, 0, 0, TimeSpan.Zero).AddDays(i - 1);
var de = new DayEntry(i, dt, dayVal.Value);
ce.DayInfo.Add(de);
}
}
chainedDb.Clients[roleGuid.Value].Add(ce);
_logger.Trace($"Added client info: {ce}");
}
Api.JetResetTableSequential(session, rolesTable, ResetTableSequentialGrbit.None);
}
catch (Exception e)
{
_logger.Error($"Error processing Clients info: {e.Message} {e.StackTrace}");
}
_logger.Debug("Getting DNS info");
try
{
using var dnsTable = new Table(session, dbid, "DNS", OpenTableGrbit.ReadOnly);
Api.JetSetTableSequential(session, dnsTable, SetTableSequentialGrbit.None);
Api.MoveBeforeFirst(session, dnsTable);
while (Api.TryMoveNext(session, dnsTable))
{
var address = Api.RetrieveColumnAsString(session, dnsTable, Api.GetTableColumnid(session, dnsTable, "Address")).Trim('\0');
var hostName = Api.RetrieveColumnAsString(session, dnsTable, Api.GetTableColumnid(session, dnsTable, "HostName"))?.Trim('\0');
var idRaw = Api.RetrieveColumnAsInt64(session, dnsTable, Api.GetTableColumnid(session, dnsTable, "LastSeen"));
var lastSeen = DateTimeOffset.FromFileTime(idRaw.Value).ToUniversalTime();
var dns = new DnsEntry(lastSeen, address, hostName);
chainedDb.DnsInfo.Add(dns);
_logger.Trace($"Added DNS info: {dns}");
}
Api.JetResetTableSequential(session, dnsTable, ResetTableSequentialGrbit.None);
}
catch (Exception e)
{
_logger.Error($"Error processing DNS info: {e.Message} {e.StackTrace}");
}
_logger.Debug("Getting Role Access info");
try
{
using var rolesTable = new Table(session, dbid, "ROLE_ACCESS", OpenTableGrbit.ReadOnly);
Api.JetSetTableSequential(session, rolesTable, SetTableSequentialGrbit.None);
Api.MoveBeforeFirst(session, rolesTable);
while (Api.TryMoveNext(session, rolesTable))
{
var idRaw = Api.RetrieveColumnAsInt64(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "FirstSeen"));
var firstSeen = DateTimeOffset.FromFileTime(idRaw.Value).ToUniversalTime();
var laRaw = Api.RetrieveColumnAsInt64(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "LastSeen"));
var lastSeen = DateTimeOffset.FromFileTime(laRaw.Value).ToUniversalTime();
var roleGuid = Api.RetrieveColumnAsGuid(session, rolesTable, Api.GetTableColumnid(session, rolesTable, "RoleGuid"));
var re = new RoleAccessEntry(firstSeen, lastSeen, roleGuid.Value);
chainedDb.RoleAccesses.Add(re);
_logger.Trace($"Added role access info: {re}");
}
Api.JetResetTableSequential(session, rolesTable, ResetTableSequentialGrbit.None);
}
catch (Exception e)
{
_logger.Error($"Error processing Role Access info: {e.Message} {e.StackTrace}");
}
_logger.Debug("Getting Virtual Machines info");
try
{
using var vmTable = new Table(session, dbid, "VIRTUALMACHINES", OpenTableGrbit.ReadOnly);
Api.JetSetTableSequential(session, vmTable, SetTableSequentialGrbit.None);
Api.MoveBeforeFirst(session, vmTable);
while (Api.TryMoveNext(session, vmTable))
{
var serialNumber = Api.RetrieveColumnAsString(session, vmTable, Api.GetTableColumnid(session, vmTable, "SerialNumber")).Trim('\0');
var idRaw = Api.RetrieveColumnAsInt64(session, vmTable, Api.GetTableColumnid(session, vmTable, "CreationTime"));
var creationTime = DateTimeOffset.FromFileTime(idRaw.Value).ToUniversalTime();
var laRaw = Api.RetrieveColumnAsInt64(session, vmTable, Api.GetTableColumnid(session, vmTable, "LastSeenActive"));
var lastSeenActive = DateTimeOffset.FromFileTime(laRaw.Value).ToUniversalTime();
var biosGuid = Api.RetrieveColumnAsGuid(session, vmTable, Api.GetTableColumnid(session, vmTable, "BIOSGuid"));
var vmGuid = Api.RetrieveColumnAsGuid(session, vmTable, Api.GetTableColumnid(session, vmTable, "VmGuid"));
var vm = new VmEntry(biosGuid.Value, vmGuid.Value, creationTime, lastSeenActive, serialNumber);
chainedDb.VmInfo.Add(vm);
_logger.Trace($"Added VM info: {vm}");
}
Api.JetResetTableSequential(session, vmTable, ResetTableSequentialGrbit.None);
}
catch (Exception e)
{
_logger.Error($"Error processing Virtual Machine info: {e.Message} {e.StackTrace}");
}
//all done, so add it to list
ProcessedDatabases.Add(chainedDb);
}
