Esempio n. 1
0
        public async Task Start()
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    using (var command = connection.CreateCommand())
                    {
                        var clauseList = new List <string>();

                        if (!string.IsNullOrEmpty(_additionalFilterClause))
                        {
                            clauseList.Add(_additionalFilterClause);
                        }

                        //Set the current run time, less 1 second, to allow for timestamps that don't measure in milliseconds (such as timestamps calculated from MSDB sysjobhistory)
                        var  runTime        = DateTime.Now.AddSeconds(-1);
                        bool validLastStamp = false;
                        var  dateTime       = DateTime.Now;

                        //Ensure we update the state of the file to avoid an infinite loop of event ingestion on first run
                        _fileInfo.Refresh();

                        // Get last valid timestamp from file
                        if (_fileInfo.Exists)
                        {
                            var content = File.ReadAllText(_fileInfo.FullName).Trim();
                            if (!string.IsNullOrEmpty(content))
                            {
                                validLastStamp = true;
                                dateTime       = DateTime.Parse(content);
                            }
                            else
                            {
                                _logger.Debug("lastScan.txt did not contain content");
                            }
                        }
                        else
                        {
                            _logger.Debug("lastScan.txt not found");
                        }

                        if (!validLastStamp)
                        {
                            //Avoid ingesting every event by limiting the query to last day up to current runTime (-1 sec)
                            dateTime = DateTime.Now.AddDays(-1);
                            _logger.Debug("Could not determine last scan time - query limited to last day");
                        }

                        //Only retrieve events that occurs after the last dateTime and up to the current runTime (- 1 sec)
                        clauseList.Add($"{_columnNameTimeStamp} > '{dateTime:yyyy-MM-dd HH:mm:ss.fff}'");
                        clauseList.Add($"{_columnNameTimeStamp} <= '{runTime:yyyy-MM-dd HH:mm:ss.fff}'");
                        _logger.Debug("Query new table rows from {StartDateTime} to {EndDateTime}", dateTime, runTime);

                        var queryString = _query + CreateWhereClause(clauseList);

                        // Create command and execute
                        command.CommandText = queryString;
                        command.CommandType = CommandType.Text;
                        var dataReader = await command.ExecuteReaderAsync();

                        // Write current runTime to file
                        using (var sw = _fileInfo.CreateText())
                            sw.Write(runTime.ToString("O"));

                        // Get data for properties
                        var columns           = dataReader.GetColumnSchema();
                        var columnNameList    = columns.Select(s => s.ColumnName).ToList();
                        var includeColumnList = _columnNamesInclude.Split(',').ToList();
                        var timeStampIndex    = columnNameList.FindIndex(s => s == _columnNameTimeStamp);
                        var messageIndex      = columnNameList.FindIndex(s => s == _columnNameMessage);

                        while (await dataReader.ReadAsync())
                        {
                            var timeStamp = dataReader.GetDateTime(timeStampIndex);
                            var message   = dataReader.IsDBNull(messageIndex) ? string.Empty : dataReader.GetString(messageIndex);
                            _logger.BindMessageTemplate(message, new object[0], out var messageTemplate, out var boundProperties);
                            var logEvent = new LogEvent(timeStamp, (LogEventLevel)Enum.Parse(typeof(LogEventLevel), _logEventLevel.ToString()), null, messageTemplate, boundProperties);

                            for (var i = 0; i < dataReader.FieldCount; i++)
                            {
                                if (includeColumnList.Contains(columnNameList[i]))
                                {
                                    if (_logger.BindProperty(columnNameList[i], dataReader[i], false, out var property))
                                    {
                                        logEvent.AddOrUpdateProperty(property);
                                    }
                                }
                            }

                            // Add additional properties
                            _logger.BindProperty("Application", _applicationName, false, out var applicationProperty);
                            logEvent.AddOrUpdateProperty(applicationProperty);

                            // Write LogEvent as Json to text writer
                            var writer = new TextWriterSink(_textWriter);
                            writer.Emit(logEvent);
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                _logger.Error(ex, "A SQL exception was occured.");
            }
        }
Esempio n. 2
0
        public async Task Start()
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    using (var command = connection.CreateCommand())
                    {
                        var clauseList = new List <string>();

                        if (!string.IsNullOrEmpty(_additionalFilterClause))
                        {
                            clauseList.Add(_additionalFilterClause);
                        }

                        // Get last valid timestamp from file
                        if (_fileInfo.Exists)
                        {
                            var content = File.ReadAllText(_fileInfo.FullName).Trim();
                            if (!string.IsNullOrEmpty(content))
                            {
                                var dateTime = DateTime.Parse(content);
                                clauseList.Add($"{_columnNameTimeStamp} >= '{dateTime:yyyy-MM-dd HH:mm:ss.fff}'");
                                _logger.Debug("Query new table rows starting at {StartDateTime}", dateTime);
                            }
                        }

                        var queryString = _query + CreateWhereClause(clauseList);

                        // Create command and execute
                        command.CommandText = queryString;
                        command.CommandType = CommandType.Text;
                        var dataReader = await command.ExecuteReaderAsync();

                        // Write new timestamp to file
                        using (var sw = _fileInfo.CreateText())
                        {
                            sw.Write(DateTime.Now.ToString("O"));
                        }

                        // Get data for properties
                        var columns           = dataReader.GetColumnSchema();
                        var columnNameList    = columns.Select(s => s.ColumnName).ToList();
                        var includeColumnList = _columnNamesInclude.Split(',').ToList();
                        var timeStampIndex    = columnNameList.FindIndex(s => s == _columnNameTimeStamp);
                        var messageIndex      = columnNameList.FindIndex(s => s == _columnNameMessage);

                        while (await dataReader.ReadAsync())
                        {
                            var timeStamp = dataReader.GetDateTime(timeStampIndex);
                            var message   = dataReader.IsDBNull(messageIndex) ? string.Empty : dataReader.GetString(messageIndex);
                            _logger.BindMessageTemplate(message, new object[0], out var messageTemplate, out var boundProperties);
                            var logEvent = new LogEvent(timeStamp, (LogEventLevel)Enum.Parse(typeof(LogEventLevel), _logEventLevel.ToString()), null, messageTemplate, boundProperties);

                            for (var i = 0; i < dataReader.FieldCount; i++)
                            {
                                if (includeColumnList.Contains(columnNameList[i]))
                                {
                                    if (_logger.BindProperty(columnNameList[i], dataReader[i], false, out var property))
                                    {
                                        logEvent.AddOrUpdateProperty(property);
                                    }
                                }
                            }

                            // Add additional properties
                            _logger.BindProperty("Application", _applicationName, false, out var applicationProperty);
                            logEvent.AddOrUpdateProperty(applicationProperty);

                            // Write LogEvent as Json to text writer
                            var writer = new TextWriterSink(_textWriter);
                            writer.Emit(logEvent);
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                _logger.Error(ex, "A SQL exception was occured.");
            }
        }
Esempio n. 3
0
        public async Task Start()
        {
            try
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    await connection.OpenAsync();

                    using (var command = connection.CreateCommand())
                    {
                        var clauseList = new List <string>();

                        if (!string.IsNullOrEmpty(SqlConfig.AdditionalFilterClause))
                        {
                            clauseList.Add(SqlConfig.AdditionalFilterClause);
                        }

                        //Set the current run time, less SecondsDelay, to allow for late-running rows or timestamps that don't measure in milliseconds (such as timestamps calculated from MSDB sysjobhistory)
                        var runTime        = DateTime.Now.AddSeconds(-SqlConfig.SecondsDelay);
                        var validLastStamp = false;
                        var dateTime       = DateTime.Now;

                        //Ensure we update the state of the file to avoid an infinite loop of event ingestion on first run
                        _fileInfo.Refresh();

                        // Get last valid timestamp from file
                        if (_fileInfo.Exists)
                        {
                            var content = File.ReadAllText(_fileInfo.FullName).Trim();
                            if (!string.IsNullOrEmpty(content))
                            {
                                validLastStamp = true;
                                dateTime       = DateTime.Parse(content);

                                //Compensate for time changes, eg. DST
                                if (DateTime.Compare(dateTime, runTime) > 0)
                                {
                                    dateTime = runTime.AddSeconds(-1);
                                }
                            }
                            else
                            {
                                if (SqlConfig.Debug)
                                {
                                    _logger.Debug("lastScan.txt did not contain content");
                                }
                            }
                        }
                        else
                        {
                            if (SqlConfig.Debug)
                            {
                                _logger.Debug("lastScan.txt not found");
                            }
                        }

                        if (!validLastStamp)
                        {
                            //Avoid ingesting every event by limiting the query to last day up to current runTime (-1 sec)
                            dateTime = DateTime.Now.AddDays(-1);
                            if (SqlConfig.Debug)
                            {
                                _logger.Debug("Could not determine last scan time - query limited to last day");
                            }
                        }

                        //In case SecondsDelay is changed and we now would have an invalid query, adjust dateTime
                        if (dateTime >= runTime)
                        {
                            dateTime = runTime.AddSeconds(-SqlConfig.SecondsDelay);
                        }

                        //Only retrieve events that occurs after the last dateTime and up to the current runTime (- 1 sec)
                        clauseList.Add($"{SqlConfig.ColumnNameTimeStamp} > '{dateTime:yyyy-MM-dd HH:mm:ss.fff}'");
                        clauseList.Add($"{SqlConfig.ColumnNameTimeStamp} <= '{runTime:yyyy-MM-dd HH:mm:ss.fff}'");

                        var queryString = _query + CreateWhereClause(clauseList);
                        if (SqlConfig.Debug)
                        {
                            _logger.ForContext("ConnectionString", _connectionString)
                            .ForContext("QueryString", queryString).Debug(
                                "Query new table rows from {StartDateTime} to {EndDateTime}", dateTime,
                                runTime);
                        }
                        else
                        {
                            _logger.Debug("Query new table rows from {StartDateTime} to {EndDateTime}", dateTime,
                                          runTime);
                        }

                        // Create command and execute
                        command.CommandText = queryString;
                        command.CommandType = CommandType.Text;
                        var dataReader = await command.ExecuteReaderAsync();

                        // Write current runTime to file
                        using (var sw = _fileInfo.CreateText())
                        {
                            await sw.WriteAsync(runTime.ToString("O"));
                        }

                        // Get data for properties
                        var columns        = dataReader.GetColumnSchema();
                        var columnNameList = columns.Select(s => s.ColumnName).ToList();
                        var timeStampIndex = columnNameList.FindIndex(s =>
                                                                      s.Equals(SqlConfig.ColumnNameTimeStamp, StringComparison.OrdinalIgnoreCase));
                        var messageIndex = columnNameList.FindIndex(s =>
                                                                    s.Equals(SqlConfig.ColumnNameMessage, StringComparison.OrdinalIgnoreCase));
                        var eventLevelIndex = -1;
                        if (!string.IsNullOrEmpty(SqlConfig.ColumnNameEventLevel) &&
                            SqlConfig.EventLevelMapping.Count > 0)
                        {
                            eventLevelIndex = columnNameList.FindIndex(s =>
                                                                       s.Equals(SqlConfig.ColumnNameEventLevel, StringComparison.OrdinalIgnoreCase));
                        }
                        var tagIndex = -1;
                        if (!string.IsNullOrEmpty(SqlConfig.ColumnNameTags))
                        {
                            tagIndex = columnNameList.FindIndex(s =>
                                                                s.Equals(SqlConfig.ColumnNameTags, StringComparison.OrdinalIgnoreCase));
                        }
                        var priorityIndex = -1;
                        if (!string.IsNullOrEmpty(SqlConfig.ColumnNamePriority))
                        {
                            priorityIndex = columnNameList.FindIndex(s =>
                                                                     s.Equals(SqlConfig.ColumnNamePriority, StringComparison.OrdinalIgnoreCase));
                        }
                        var responderIndex = -1;
                        if (!string.IsNullOrEmpty(SqlConfig.ColumnNameResponder))
                        {
                            responderIndex = columnNameList.FindIndex(s =>
                                                                      s.Equals(SqlConfig.ColumnNameResponder, StringComparison.OrdinalIgnoreCase));
                        }
                        var projectKeyIndex = -1;
                        if (!string.IsNullOrEmpty(SqlConfig.ColumnNameProjectKey))
                        {
                            projectKeyIndex = columnNameList.FindIndex(s =>
                                                                       s.Equals(SqlConfig.ColumnNameProjectKey, StringComparison.OrdinalIgnoreCase));
                        }
                        var initialEstimateIndex = -1;
                        if (!string.IsNullOrEmpty(SqlConfig.ColumnNameInitialEstimate))
                        {
                            initialEstimateIndex = columnNameList.FindIndex(s =>
                                                                            s.Equals(SqlConfig.ColumnNameInitialEstimate, StringComparison.OrdinalIgnoreCase));
                        }
                        var remainingEstimateIndex = -1;
                        if (!string.IsNullOrEmpty(SqlConfig.ColumnNameRemainingEstimate))
                        {
                            remainingEstimateIndex = columnNameList.FindIndex(s =>
                                                                              s.Equals(SqlConfig.ColumnNameRemainingEstimate,
                                                                                       StringComparison.OrdinalIgnoreCase));
                        }
                        var dueDateIndex = -1;
                        if (!string.IsNullOrEmpty(SqlConfig.ColumnNameDueDate))
                        {
                            dueDateIndex = columnNameList.FindIndex(s =>
                                                                    s.Equals(SqlConfig.ColumnNameDueDate, StringComparison.OrdinalIgnoreCase));
                        }

                        while (await dataReader.ReadAsync())
                        {
                            var timeStamp = dataReader.GetDateTime(timeStampIndex);
                            var message   = dataReader.IsDBNull(messageIndex)
                                ? string.Empty
                                : dataReader.GetString(messageIndex);
                            var logEventLevel = (LogEventLevel)Enum.Parse(typeof(LogEventLevel),
                                                                          SqlConfig.LogEventLevel.ToString());
                            if (eventLevelIndex >= 0 && !dataReader.IsDBNull(eventLevelIndex) &&
                                TryGetEventLevelCI(dataReader.GetString(eventLevelIndex), out var eventLevel))
                            {
                                logEventLevel = eventLevel;
                            }

                            _logger.BindMessageTemplate(message, Array.Empty <object>(), out var messageTemplate,
                                                        out var boundProperties);
                            var logEvent = new LogEvent(timeStamp, logEventLevel, null, messageTemplate,
                                                        boundProperties);

                            for (var i = 0; i < dataReader.FieldCount; i++)
                            {
                                foreach (var unused in SqlConfig.ColumnNamesInclude.Where(columnName =>
                                                                                          columnName.Equals(columnNameList[i], StringComparison.OrdinalIgnoreCase)))
                                {
                                    if (_logger.BindProperty(columnNameList[i], dataReader[i], false,
                                                             out var property))
                                    {
                                        logEvent.AddOrUpdateProperty(property);
                                    }
                                }
                            }

                            var tags = new List <string>();
                            if (tagIndex == -1 && SqlConfig.Tags.Any())
                            {
                                tags.AddRange(SqlConfig.Tags);
                                if (_logger.BindProperty("Tags", tags, false, out var tagEvent))
                                {
                                    logEvent.AddOrUpdateProperty(tagEvent);
                                }
                            }
                            else if (tagIndex >= 0 && !dataReader.IsDBNull(tagIndex) &&
                                     TryGetPropertyValueCI(SqlConfig.TagMappings, dataReader.GetString(tagIndex),
                                                           out var tag))
                            {
                                tags.Add(tag);
                                if (_logger.BindProperty("Tags", tags, false, out var tagEvent))
                                {
                                    logEvent.AddOrUpdateProperty(tagEvent);
                                }
                            }

                            if (priorityIndex >= 0 && !dataReader.IsDBNull(priorityIndex))
                            {
                                if (SqlConfig.PriorityMapping.Count > 0 && TryGetPropertyValueCI(
                                        SqlConfig.PriorityMapping, dataReader.GetString(priorityIndex),
                                        out var priorityValue))
                                {
                                    if (_logger.BindProperty("Priority", priorityValue, false, out var priority))
                                    {
                                        logEvent.AddOrUpdateProperty(priority);
                                    }
                                }
                                else
                                {
                                    if (_logger.BindProperty("Priority", dataReader.GetString(priorityIndex), false,
                                                             out var priority))
                                    {
                                        logEvent.AddOrUpdateProperty(priority);
                                    }
                                }
                            }

                            if (responderIndex >= 0 && !dataReader.IsDBNull(responderIndex))
                            {
                                if (SqlConfig.ResponderMapping.Count > 0 && TryGetPropertyValueCI(
                                        SqlConfig.ResponderMapping, dataReader.GetString(responderIndex),
                                        out var responderValue))
                                {
                                    if (_logger.BindProperty("Responders", responderValue, false,
                                                             out var responder))
                                    {
                                        logEvent.AddOrUpdateProperty(responder);
                                    }
                                }
                                else
                                {
                                    if (_logger.BindProperty("Responders", dataReader.GetString(responderIndex),
                                                             false,
                                                             out var responder))
                                    {
                                        logEvent.AddOrUpdateProperty(responder);
                                    }
                                }
                            }

                            if (projectKeyIndex >= 0 && !dataReader.IsDBNull(projectKeyIndex))
                            {
                                if (SqlConfig.ProjectKeyMapping.Count > 0 && TryGetPropertyValueCI(
                                        SqlConfig.ProjectKeyMapping, dataReader.GetString(projectKeyIndex),
                                        out var projectKeyValue))
                                {
                                    if (_logger.BindProperty("ProjectKey", projectKeyValue, false,
                                                             out var projectKey))
                                    {
                                        logEvent.AddOrUpdateProperty(projectKey);
                                    }
                                }
                                else
                                {
                                    if (_logger.BindProperty("ProjectKey", dataReader.GetString(projectKeyIndex),
                                                             false,
                                                             out var projectKey))
                                    {
                                        logEvent.AddOrUpdateProperty(projectKey);
                                    }
                                }
                            }

                            if (initialEstimateIndex >= 0 && !dataReader.IsDBNull(initialEstimateIndex))
                            {
                                if (SqlConfig.InitialEstimateMapping.Count > 0 && TryGetPropertyValueCI(
                                        SqlConfig.InitialEstimateMapping, dataReader.GetString(initialEstimateIndex),
                                        out var initialEstimateValue))
                                {
                                    if (_logger.BindProperty("InitialEstimate", initialEstimateValue, false,
                                                             out var initialEstimate))
                                    {
                                        logEvent.AddOrUpdateProperty(initialEstimate);
                                    }
                                }
                                else
                                {
                                    if (_logger.BindProperty("InitialEstimate",
                                                             dataReader.GetString(initialEstimateIndex), false, out var initialEstimate))
                                    {
                                        logEvent.AddOrUpdateProperty(initialEstimate);
                                    }
                                }
                            }

                            if (remainingEstimateIndex >= 0 && !dataReader.IsDBNull(remainingEstimateIndex))
                            {
                                if (SqlConfig.RemainingEstimateMapping.Count > 0 && TryGetPropertyValueCI(
                                        SqlConfig.RemainingEstimateMapping,
                                        dataReader.GetString(remainingEstimateIndex),
                                        out var remainingEstimateValue))
                                {
                                    if (_logger.BindProperty("RemainingEstimate", remainingEstimateValue, false,
                                                             out var remainingEstimate))
                                    {
                                        logEvent.AddOrUpdateProperty(remainingEstimate);
                                    }
                                }
                                else
                                {
                                    if (_logger.BindProperty("RemainingEstimate",
                                                             dataReader.GetString(remainingEstimateIndex), false,
                                                             out var remainingEstimate))
                                    {
                                        logEvent.AddOrUpdateProperty(remainingEstimate);
                                    }
                                }
                            }

                            if (dueDateIndex >= 0 && !dataReader.IsDBNull(dueDateIndex))
                            {
                                if (SqlConfig.DueDateMapping.Count > 0 && TryGetPropertyValueCI(
                                        SqlConfig.DueDateMapping, dataReader.GetString(dueDateIndex),
                                        out var dueDateValue))
                                {
                                    if (_logger.BindProperty("DueDate", dueDateValue, false, out var dueDate))
                                    {
                                        logEvent.AddOrUpdateProperty(dueDate);
                                    }
                                }
                                else
                                {
                                    if (_logger.BindProperty("DueDate", dataReader.GetString(dueDateIndex), false,
                                                             out var dueDate))
                                    {
                                        logEvent.AddOrUpdateProperty(dueDate);
                                    }
                                }
                            }

                            // Add additional properties
                            if (!string.IsNullOrEmpty(SqlConfig.ApplicationName))
                            {
                                var appName = "Application";
                                if (!string.IsNullOrEmpty(SqlConfig.ApplicationPropertyName))
                                {
                                    appName = SqlConfig.ApplicationPropertyName;
                                }

                                _logger.BindProperty(appName, SqlConfig.ApplicationName, false,
                                                     out var applicationProperty);
                                logEvent.AddOrUpdateProperty(applicationProperty);
                            }


                            // Write LogEvent as Json to text writer
                            var writer = new TextWriterSink(_textWriter);
                            writer.Emit(logEvent);
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                _logger.Error(ex, "A SQL exception has occurred.");
            }
        }