LOG_DATA_CLEANING.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "289d7cf8",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sample log file content:\n",
      "[Sun Dec 04 04:47:44 2005] [notice] workerEnv.init() ok /etc/httpd/conf/workers2.properties\n",
      "[Sun Dec 04 04:47:44 2005] [error] mod_jk child workerEnv in error state 6\n",
      "[Sun Dec 04 04:51:08 2005] [notice] jk2_init() Found child 6725 in scoreboard slot 10\n",
      "[Sun Dec 04 04:51:09 2005] [notice] jk2_init() Found child 6726 in scoreboard slot 8\n",
      "[Sun Dec 04 04:51:09 2005] [notice] jk2_init() Found child 6728 in scoreboard slot 6\n",
      "Parsed logs sample:\n",
      "{'datetime': 'Sun Dec 04 04:47:44 2005', 'log_level': 'notice', 'message': 'workerEnv.init() ok /etc/httpd/conf/workers2.properties'}\n",
      "{'datetime': 'Sun Dec 04 04:47:44 2005', 'log_level': 'error', 'message': 'mod_jk child workerEnv in error state 6'}\n",
      "{'datetime': 'Sun Dec 04 04:51:08 2005', 'log_level': 'notice', 'message': 'jk2_init() Found child 6725 in scoreboard slot 10'}\n",
      "{'datetime': 'Sun Dec 04 04:51:09 2005', 'log_level': 'notice', 'message': 'jk2_init() Found child 6726 in scoreboard slot 8'}\n",
      "{'datetime': 'Sun Dec 04 04:51:09 2005', 'log_level': 'notice', 'message': 'jk2_init() Found child 6728 in scoreboard slot 6'}\n",
      "DataFrame columns:\n",
      "Index(['datetime', 'log_level', 'message'], dtype='object')\n",
      "         date      time log_level  \\\n",
      "0  2005-12-04  04:47:44    notice   \n",
      "1  2005-12-04  04:47:44     error   \n",
      "2  2005-12-04  04:51:08    notice   \n",
      "3  2005-12-04  04:51:09    notice   \n",
      "4  2005-12-04  04:51:09    notice   \n",
      "\n",
      "                                             message  \n",
      "0  workerEnv.init() ok /etc/httpd/conf/workers2.p...  \n",
      "1            mod_jk child workerEnv in error state 6  \n",
      "2  jk2_init() Found child 6725 in scoreboard slot 10  \n",
      "3   jk2_init() Found child 6726 in scoreboard slot 8  \n",
      "4   jk2_init() Found child 6728 in scoreboard slot 6  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "\n",
    "# Define a function to read the log file\n",
    "def read_log_file(file_path):\n",
    "    with open(file_path, 'r') as file:\n",
    "        log_data = file.readlines()\n",
    "    return log_data\n",
    "\n",
    "# Example usage\n",
    "log_file_path = r\"C:\\Users\\drsar\\Downloads\\sample log analysis\\Apache_2k.log\"\n",
    "log_data = read_log_file(log_file_path)\n",
    "\n",
    "# Print the first few lines of the log file for verification\n",
    "print(\"Sample log file content:\")\n",
    "for line in log_data[:5]:  # Print the first 5 lines\n",
    "    print(line.strip())\n",
    "\n",
    "# No need to split the log data, it is already a list of lines\n",
    "log_lines = log_data\n",
    "\n",
    "# Regular expression pattern to parse the log lines\n",
    "log_pattern = re.compile(r'\\[(.*?)\\] \\[(.*?)\\] (.+)')\n",
    "\n",
    "# Initialize a list to store parsed logs\n",
    "parsed_logs = []\n",
    "\n",
    "# Parse each line using the regular expression\n",
    "for line in log_lines:\n",
    "    line = line.strip()  # Ensure there are no leading/trailing whitespaces\n",
    "    match = log_pattern.match(line)\n",
    "    if match:\n",
    "        datetime, log_level, message = match.groups()\n",
    "        parsed_logs.append({\n",
    "            'datetime': datetime,\n",
    "            'log_level': log_level,\n",
    "            'message': message\n",
    "        })\n",
    "    else:\n",
    "        # Print lines that do not match the regex\n",
    "        print(f\"Line did not match regex: {line}\")\n",
    "\n",
    "# Check the contents of parsed_logs to ensure it is correctly populated\n",
    "print(\"Parsed logs sample:\")\n",
    "for entry in parsed_logs[:5]:  # Print the first 5 entries\n",
    "    print(entry)\n",
    "\n",
    "# Convert the parsed logs to a DataFrame\n",
    "df = pd.DataFrame(parsed_logs)\n",
    "\n",
    "# Check the columns in the DataFrame to ensure 'datetime' is present\n",
    "print(\"DataFrame columns:\")\n",
    "print(df.columns)\n",
    "\n",
    "# Split the datetime into separate columns for date and time\n",
    "if 'datetime' in df.columns:\n",
    "    df['date'] = pd.to_datetime(df['datetime']).dt.date\n",
    "    df['time'] = pd.to_datetime(df['datetime']).dt.time\n",
    "\n",
    "    # Drop the original datetime column\n",
    "    df.drop(columns=['datetime'], inplace=True)\n",
    "\n",
    "    # Reorder the columns\n",
    "    df = df[['date', 'time', 'log_level', 'message']]\n",
    "\n",
    "    # Save the cleaned data to a CSV file\n",
    "    df.to_csv('cleaned_log_data.csv', index=False)\n",
    "\n",
    "    print(df.head())\n",
    "else:\n",
    "    print(\"The 'datetime' column is missing from the DataFrame.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "027d1e6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "\n",
    "# Connect to the MySQL database\n",
    "conn = mysql.connector.connect(\n",
    "    host=\"localhost\",\n",
    "    user=\"root\",\n",
    "    password=\"Amrutha1*\",\n",
    "    database=\"sample_log_db\"\n",
    ")\n",
    "\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Create a table if it doesn't exist\n",
    "cursor.execute('''\n",
    "    CREATE TABLE IF NOT EXISTS logs (\n",
    "        id INT AUTO_INCREMENT PRIMARY KEY,\n",
    "        date DATE,\n",
    "        time TIME,\n",
    "        log_level VARCHAR(255),\n",
    "        message TEXT\n",
    "    )\n",
    "''')\n",
    "\n",
    "# Insert DataFrame rows into the database\n",
    "for index, row in df.iterrows():\n",
    "    cursor.execute('''\n",
    "        INSERT INTO logs (date, time, log_level, message) VALUES (%s, %s, %s, %s)\n",
    "    ''', (row['date'], row['time'], row['log_level'], row['message']))\n",
    "\n",
    "# Commit the transaction\n",
    "conn.commit()\n",
    "\n",
    "# Close the connection\n",
    "cursor.close()\n",
    "conn.close()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2312fa9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90a892fb",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}