locked
Writing event log entries to a sql database RRS feed

  • Question

  • Hi Guys,

    Whats the best way to write event log entries to a database, so far I've been able to write the entires to a data grid by setting the data source of the datagrid to the event log object that I have created, but how can I then use that same event log object to write to a sql database?

    any suggestions would be appreciated.

    Thanks

    Friday, March 18, 2011 8:55 AM

Answers

  • ok found an example that helped me:

     

    protected void Page_Load(object sender, EventArgs e)
            {
              

                Hashtable ht;
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                DataRow drow;

                DataColumn fIndex = new DataColumn();
                dt.Columns.Add(fIndex);

                DataColumn fMessage = new DataColumn();
                dt.Columns.Add(fMessage);

                DataColumn fSource = new DataColumn();
                dt.Columns.Add(fSource);

                try
                {
                    EventLog log = new EventLog("Application");
                    for (int i = 0; i < log.Entries.Count; i++)
                    {
                        drow = dt.NewRow();

                        fIndex.DataType = System.Type.GetType("System.String");
                        fIndex.ColumnName = "Index";

                        fMessage.DataType = System.Type.GetType("System.String");
                        fMessage.ColumnName = "Message";

                        fSource.DataType = System.Type.GetType("System.String");
                        fSource.ColumnName = "Source";

                        drow[0] = log.Entries[i].Index;
                        drow[1] = log.Entries[i].Message;
                        drow[2] = log.Entries[i].Source;
                        dt.Rows.Add(drow);

                    }
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message.ToString());
                }

     

     

    Thanks to

      from http://bytes.com/topic/visual-basic-net/answers/843792-how-assign-array-values-datagridview

    • Marked as answer by Jackie-Sun Monday, March 28, 2011 2:37 AM
    Friday, March 18, 2011 1:16 PM

All replies

  • basically I want to write 3  event log entries into a table into a database, so far I can write the entries on screen like so:

    protected void Page_Load(object sender, EventArgs e)
            {
                EventLog log = new EventLog("Application");
                log.MachineName = ".";
                //GridView1.DataSource = log.Entries;
                //GridView1.DataBind();
                //string type = log.Entries.Count.ToString();

                foreach (EventLogEntry entry in log.Entries)
                {
                    if (entry.EntryType.ToString() == "Error")
                    {
                        Response.Write(entry.EventID + " " entry.Message.ToString() + " " + "Date: " + entry.TimeGenerated.ToString() + " " + entry.EntryType.ToString() + "<BR>" + "<BR>");
                    }
                }

            }

     

    so, Im using response.write to show me the Entry Message, The Time Generated and the Entry type, from here how can I insert these values into a my table which have the same filed names.

    I'm trying to gather the system event logs from a list a local server and load it into a dataset, and from there load all the records into either an XLS file or a SQL table or both.

    What I can't figure out for the life of me is how to bind the contents of an event log to a dataset using .net. If I can figure out how to get the event log events loaded into a set, then I can hanlde the rest. I'm sure this is ridiculously easy, but I've searched MSDN and google and I just can't find what I'm looking for.

     

    So far I have tried using this code:

    public static DataTable GetLog()
            {
                DataTable logTable = new DataTable();
                EventLog log = new EventLog("Application");
                log.MachineName = ".";
              

                foreach(EventLogEntry entry in log.Entries)
                {
                    if (entry.EntryType.ToString() == "Error")
                    {

                        //logTable.Columns.Add(entry.InstanceId.ToString());
                        //logTable.Columns.Add(entry.Message.ToString());
                        //logTable.Columns.Add(entry.TimeGenerated.ToString());
                        //logTable.Columns.Add(entry.EntryType.ToString());
                        logTable.Columns.Add(entry.InstanceId.ToString());
                    }
                }

                return logTable;

               
            }

    but this gives the error:

    A column named '3221226525' already belongs to this DataTable.

     

     


    Friday, March 18, 2011 10:55 AM
  • ok found an example that helped me:

     

    protected void Page_Load(object sender, EventArgs e)
            {
              

                Hashtable ht;
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                DataRow drow;

                DataColumn fIndex = new DataColumn();
                dt.Columns.Add(fIndex);

                DataColumn fMessage = new DataColumn();
                dt.Columns.Add(fMessage);

                DataColumn fSource = new DataColumn();
                dt.Columns.Add(fSource);

                try
                {
                    EventLog log = new EventLog("Application");
                    for (int i = 0; i < log.Entries.Count; i++)
                    {
                        drow = dt.NewRow();

                        fIndex.DataType = System.Type.GetType("System.String");
                        fIndex.ColumnName = "Index";

                        fMessage.DataType = System.Type.GetType("System.String");
                        fMessage.ColumnName = "Message";

                        fSource.DataType = System.Type.GetType("System.String");
                        fSource.ColumnName = "Source";

                        drow[0] = log.Entries[i].Index;
                        drow[1] = log.Entries[i].Message;
                        drow[2] = log.Entries[i].Source;
                        dt.Rows.Add(drow);

                    }
                }
                catch (Exception ex)
                {
                    Response.Write(ex.Message.ToString());
                }

     

     

    Thanks to

      from http://bytes.com/topic/visual-basic-net/answers/843792-how-assign-array-values-datagridview

    • Marked as answer by Jackie-Sun Monday, March 28, 2011 2:37 AM
    Friday, March 18, 2011 1:16 PM
  • Here is the working source, although fo my purposes Im going to refine so it only starts reading the event log if the log entry type = 'error'.  If anybody can suggest any improvements it would be most welcome:

     

    protected void Page_Load(object sender, EventArgs e)
            {
                GetLogs();
            }

            private void GetLogs()
            {
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                DataRow drow;

                DataColumn fIndex = new DataColumn();
                dt.Columns.Add(fIndex);

                DataColumn fMessage = new DataColumn();
                dt.Columns.Add(fMessage);

                DataColumn fSource = new DataColumn();
                dt.Columns.Add(fSource);

                DataColumn fEntryType = new DataColumn();
                dt.Columns.Add(fEntryType);

                DataColumn fDate = new DataColumn();
                dt.Columns.Add(fDate);

                string strConn = ConfigurationSettings.AppSettings["Connection"].ToString();
                SqlConnection dbConn = new SqlConnection(strConn);
                SqlCommand command = new SqlCommand();
                //command = null;

                try
                {
                    EventLog log = new EventLog("Application");
                    for (int i = 0; i < log.Entries.Count; i++)
                    {
                        drow = dt.NewRow();

                        fIndex.DataType = System.Type.GetType("System.String");
                        fIndex.ColumnName = "Index";

                        fMessage.DataType = System.Type.GetType("System.String");
                        fMessage.ColumnName = "Message";

                        fSource.DataType = System.Type.GetType("System.String");
                        fSource.ColumnName = "Source";

                        fEntryType.DataType = System.Type.GetType("System.String");
                        fEntryType.ColumnName = "EntryType";

                        fDate.DataType = System.Type.GetType("System.String");
                        fDate.ColumnName = "TimeGenerated";

                        drow[0] = log.Entries[i].Index;
                        drow[1] = log.Entries[i].Message;
                        drow[2] = log.Entries[i].Source;
                        drow[3] = log.Entries[i].EntryType;
                        drow[4] = log.Entries[i].TimeGenerated;
                        dt.Rows.Add(drow);

                        foreach (DataRow dr in dt.Rows)
                        {

                            string strInsertion = @" INSERT INTO [EventLog].[dbo].EVENTS ([EVENT_ID], [MESSAGE], [TIME_GENERATED], [ENTRY_TYPE]) VALUES (@EVENT_ID, @MESSAGE, @TIME_GENERATED, @ENTRY_TYPE)";

                            command.CommandText = strInsertion;
                            command.CommandType = CommandType.Text;
                            command.Connection = dbConn;

                            DateTime start = DateTime.Parse(dr["TimeGenerated"].ToString());


                            command.Parameters.Add("@EVENT_ID", dr["Index"].ToString());
                            command.Parameters.Add("@MESSAGE", dr["Message"].ToString());
                            command.Parameters.Add("@ENTRY_TYPE", dr["EntryType"]);
                            command.Parameters.Add("@TIME_GENERATED", start);
                            //command.Parameters.Add("@TIME_GENERATED", dr["TimeGenerated"].ToString());

                            dbConn.Open();
                            command.ExecuteNonQuery();
                            command.Connection.Close();
                            command = new SqlCommand(strConn);
                        }

                    }
                 
                }



                catch (Exception ex)
                {
                    Response.Write(ex.Message.ToString() + "<br>" + ex.InnerException.ToString() + "<br>" + ex.StackTrace.ToString());
                }
                finally
                {
                    dbConn = null;
                    command = null;
                    GC.Collect();
                    Response.Write("FINISHED");
                  
                }

               
            }

    Friday, March 18, 2011 4:57 PM