Question_6.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a0740787",
   "metadata": {},
   "source": [
    "# Question 6: For each day of the week, what is the average number of seconds over which ”re-synch state events” occurred?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26ce9ee0",
   "metadata": {},
   "source": [
    "# Section 1: Data Preparation and Preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc7b3bf8",
   "metadata": {},
   "source": [
    "**IMPORTING NECESSARY LIBRARIES**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "923c79cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#https://spark.apache.org/docs/latest/api/python/getting_started/quickstart_df.html\n",
    "# Importinng 'SparkSession' module which is the main entry point for DataFrame and SQL functionality. SparkSession can be used create DataFrame, register DataFrame as tables, execute SQL over tables and cache tables.\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# Importing 'Date (datetime.date)', 'Timestamp (datetime.datetime)' , 'Double' data type from 'Data Types' module  \n",
    "from pyspark.sql.types import DateType, TimestampType, DoubleType, IntegerType\n",
    "\n",
    "# importing built-in functions available for DataFrame.\n",
    "from pyspark.sql.functions import to_date, to_timestamp, col, month, date_format, regexp_extract, dayofweek, avg\n",
    "\n",
    "# Importing 'matplotlib.pyplot' module for creating and customizing plots and visualizations. \n",
    "# 'pyplot' is a part of the Matplotlib library and provides a user-friendly interface to create different types of plots.\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Importing'time' module for measuring execution time\n",
    "import time\n",
    "\n",
    "# Import re module which provides regular expression matching operations in Python.\n",
    "# https://docs.python.org/3/library/re.html\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eaf7f0f0",
   "metadata": {},
   "source": [
    "**EXECUTION TIME COUNTER START**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9520f99f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Recording the starting time of the execution using the 'time.perf_counter()' function.It provides a high-resolution timer that measures the time elapsed in seconds to measure its execution time.\n",
    "executiontimestart= time.perf_counter()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5c8c3b29",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "23/08/11 21:26:49 WARN SparkSession: Using an existing Spark session; only runtime SQL configurations will take effect.\n"
     ]
    }
   ],
   "source": [
    "# Initializing SparkSession named 'ScpProject' using the SparkSession builder.\n",
    "session = SparkSession.builder.appName('ScpProject').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "faa7ecd9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# Reading data from CSV file into a DataFrame 'bgl_log_df'\n",
    "bgl_log_df = session.read.csv(\"BGLnew.log\", sep=',', inferSchema=True, header=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "64976506",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renaming the columns 'toDF()' method for better understanding into our dataframe.\n",
    "bgl_log_df = bgl_log_df.toDF('alert_message_flag', 'timestamp', 'date', 'node', 'date_and_time', 'node_repeated',\n",
    "                             'message_type', 'system_component', 'level', 'message_content')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b83ac62a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parsing 'date' column to standard DateType\n",
    "bgl_log_df = bgl_log_df.withColumn('date', to_date('date', 'yyyy.MM.dd'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fd40fd59",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Parsing 'date_and_time' column to standard TimestampType\n",
    "bgl_log_df = bgl_log_df.withColumn('date_and_time', to_timestamp('date_and_time', 'yyyy-MM-dd-HH.mm.ss.SSSSSS'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6a55ab84",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Casting columns to appropriate data types\n",
    "bgl_log_df = bgl_log_df.withColumn(\"timestamp\", bgl_log_df[\"timestamp\"].cast(TimestampType())) \\\n",
    "    .withColumn(\"date\", bgl_log_df[\"date\"].cast(DateType())) \\\n",
    "    .withColumn(\"date_and_time\", bgl_log_df[\"date_and_time\"].cast(TimestampType()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "45d2410d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- alert_message_flag: string (nullable = true)\n",
      " |-- timestamp: timestamp (nullable = true)\n",
      " |-- date: date (nullable = true)\n",
      " |-- node: string (nullable = true)\n",
      " |-- date_and_time: timestamp (nullable = true)\n",
      " |-- node_repeated: string (nullable = true)\n",
      " |-- message_type: string (nullable = true)\n",
      " |-- system_component: string (nullable = true)\n",
      " |-- level: string (nullable = true)\n",
      " |-- message_content: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Using 'printSchema()' method to display the updated schema of the DataFrame 'bgl_log_df'.\n",
    "bgl_log_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "199ad9ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "# bgl_log_df: The DataFrame on which the operation is performed. This DataFrame contains log data with various columns like \"date_and_time\".\n",
    "# bgl_log_df.withColumn(): A method used to add a new column to the DataFrame. It takes two arguments: the name of the new column and the expression to calculate its values.\n",
    "# \"day\": The name of the new column that will be added to the DataFrame. This column will store the day of the week corresponding to the \"date_and_time\" column.\n",
    "# date_format(col(\"date_and_time\"), \"EEEE\"): The expression to calculate the values of the new \"day\" column. It uses the \"date_format\" function from PySpark SQL to extract the day of the week (full name) from the \"date_and_time\" column. The \"EEEE\" format string specifies the full name of the day (e.g., Monday, Tuesday, etc.).\n",
    "# The result of the \"date_format\" function is applied to each row of the DataFrame, and the corresponding day of the week is stored in the new \"day\" column.\n",
    "bgl_log_df = bgl_log_df.withColumn(\"day\", date_format(col(\"date_and_time\"), \"EEEE\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c51663d2",
   "metadata": {},
   "source": [
    "**DATA PREPRATION AND PREPROCESSING COMPLETED**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e72b9e71",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-RECORD 0------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:50                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:50.363779               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 1------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:50                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:50.527847               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 2------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:50                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:50.675872               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 3------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:50                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:50.823719               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 4------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:50                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:50.982731               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 5------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:51                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:51.131467               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 6------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:51                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:51.293532               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 7------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:51                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:51.428563               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 8------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:51                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:51.601412               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 9------------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:51                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:51.749199               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 10-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:51                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:51.885834               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 11-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:52                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:52.041388               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 12-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:52                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:52.199063               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 13-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:52                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:52.345821               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 14-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:52                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:52.493353               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 15-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:52                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:52.638135               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 16-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:52                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:52.807927               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 17-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:52                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:52.951717               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 18-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:53                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:53.12578                \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "-RECORD 19-----------------------------------------------------\n",
      " alert_message_flag | -                                        \n",
      " timestamp          | 2005-06-03 22:42:53                      \n",
      " date               | 2005-06-03                               \n",
      " node               | R02-M1-N0-C:J12-U11                      \n",
      " date_and_time      | 2005-06-03 15:42:53.276129               \n",
      " node_repeated      | R02-M1-N0-C:J12-U11                      \n",
      " message_type       | RAS                                      \n",
      " system_component   | KERNEL                                   \n",
      " level              | INFO                                     \n",
      " message_content    | instruction cache parity error corrected \n",
      " day                | Friday                                   \n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# We are using the 'show()' method to display the DataFrame 'bgl_log_df' in a vertical format.\n",
    "# The 'vertical=True' argument is used to display the DataFrame vertically, showing each row in a single line.\n",
    "# The 'truncate=False' argument is used to prevent truncation of column values, ensuring complete visibility of the data.\n",
    "bgl_log_df.show(truncate=False, vertical=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4fa8c6f",
   "metadata": {},
   "source": [
    "# Section 2: Log Data Analysis : Spark RDD"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d77834c0",
   "metadata": {},
   "source": [
    "**TOTAL NUMBER OF LOG ENTRIES IN THE LOG DATASET COUNT**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "8a06cc1d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4747963"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count total logs in the DataFrame\n",
    "total_logs_count = bgl_log_df.count()\n",
    "\n",
    "# Display the total number of logs\n",
    "total_logs_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7d1e3cb0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-RECORD 0---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-04 01:21:45                                                 \n",
      " date               | 2005-06-03                                                          \n",
      " node               | R06-M1-N6-C:J15-U01                                                 \n",
      " date_and_time      | 2005-06-03 18:21:45.321124                                          \n",
      " node_repeated      | R06-M1-N6-C:J15-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected  \n",
      " day                | Friday                                                              \n",
      "-RECORD 1---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-04 02:13:26                                                 \n",
      " date               | 2005-06-03                                                          \n",
      " node               | R06-M1-N6-C:J15-U01                                                 \n",
      " date_and_time      | 2005-06-03 19:13:26.552359                                          \n",
      " node_repeated      | R06-M1-N6-C:J15-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected  \n",
      " day                | Friday                                                              \n",
      "-RECORD 2---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-05 08:55:12                                                 \n",
      " date               | 2005-06-05                                                          \n",
      " node               | R06-M1-N6-C:J15-U01                                                 \n",
      " date_and_time      | 2005-06-05 01:55:12.903167                                          \n",
      " node_repeated      | R06-M1-N6-C:J15-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected  \n",
      " day                | Sunday                                                              \n",
      "-RECORD 3---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-05 08:55:13                                                 \n",
      " date               | 2005-06-05                                                          \n",
      " node               | R06-M1-N6-C:J15-U01                                                 \n",
      " date_and_time      | 2005-06-05 01:55:13.435659                                          \n",
      " node_repeated      | R06-M1-N6-C:J15-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 31 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected \n",
      " day                | Sunday                                                              \n",
      "-RECORD 4---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:08:18                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M1-NB-C:J02-U11                                                 \n",
      " date_and_time      | 2005-06-06 08:08:18.373457                                          \n",
      " node_repeated      | R27-M1-NB-C:J02-U11                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 5---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:08:20                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NA-C:J02-U11                                                 \n",
      " date_and_time      | 2005-06-06 08:08:20.649555                                          \n",
      " node_repeated      | R27-M0-NA-C:J02-U11                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 6---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:08:20                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NA-C:J02-U11                                                 \n",
      " date_and_time      | 2005-06-06 08:08:20.699541                                          \n",
      " node_repeated      | R27-M0-NA-C:J02-U11                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 7---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:08:24                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R06-M1-N6-C:J15-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:08:24.750475                                          \n",
      " node_repeated      | R06-M1-N6-C:J15-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 4 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 8---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:08:24                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R06-M1-N6-C:J15-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:08:24.78004                                           \n",
      " node_repeated      | R06-M1-N6-C:J15-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 4 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 9---------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:08:25                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R06-M1-N6-C:J15-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:08:25.324527                                          \n",
      " node_repeated      | R06-M1-N6-C:J15-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 37 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected \n",
      " day                | Monday                                                              \n",
      "-RECORD 10--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:08:25                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R06-M1-N6-C:J15-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:08:25.33814                                           \n",
      " node_repeated      | R06-M1-N6-C:J15-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 37 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected \n",
      " day                | Monday                                                              \n",
      "-RECORD 11--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:08:48                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NE-C:J02-U11                                                 \n",
      " date_and_time      | 2005-06-06 08:08:48.884207                                          \n",
      " node_repeated      | R27-M0-NE-C:J02-U11                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 12--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:08:57                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NB-C:J02-U11                                                 \n",
      " date_and_time      | 2005-06-06 08:08:57.849851                                          \n",
      " node_repeated      | R27-M0-NB-C:J02-U11                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 13--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:09:01                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R23-M0-NA-C:J02-U11                                                 \n",
      " date_and_time      | 2005-06-06 08:09:01.458734                                          \n",
      " node_repeated      | R23-M0-NA-C:J02-U11                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 14--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:09:29                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NB-C:J12-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:09:29.450705                                          \n",
      " node_repeated      | R27-M0-NB-C:J12-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 15--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:09:29                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NB-C:J12-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:09:29.465421                                          \n",
      " node_repeated      | R27-M0-NB-C:J12-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 16--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:09:29                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NF-C:J16-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:09:29.480468                                          \n",
      " node_repeated      | R27-M0-NF-C:J16-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 17--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:09:29                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NF-C:J16-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:09:29.495456                                          \n",
      " node_repeated      | R27-M0-NF-C:J16-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 18--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:09:29                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NF-C:J12-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:09:29.562441                                          \n",
      " node_repeated      | R27-M0-NF-C:J12-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected  \n",
      " day                | Monday                                                              \n",
      "-RECORD 19--------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                   \n",
      " timestamp          | 2005-06-06 15:09:29                                                 \n",
      " date               | 2005-06-06                                                          \n",
      " node               | R27-M0-NE-C:J13-U01                                                 \n",
      " date_and_time      | 2005-06-06 08:09:29.656035                                          \n",
      " node_repeated      | R27-M0-NE-C:J13-U01                                                 \n",
      " message_type       | RAS                                                                 \n",
      " system_component   | KERNEL                                                              \n",
      " level              | INFO                                                                \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected  \n",
      " day                | Monday                                                              \n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Filter the DataFrame to extract rows containing the phrase \"re-synch state event\" in the message_content column.\n",
    "# The result will be a new DataFrame containing only the rows with the specified phrase.\n",
    "extracted_logs_df = bgl_log_df.filter(col(\"message_content\").contains(\"re-synch state event\"))\n",
    "\n",
    "# The `show()` method is used to display the content of the DataFrame\n",
    "extracted_logs_df.show(truncate=False, vertical=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "1b766331",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+-------------------+----------+-------------------+--------------------+-------------------+------------+----------------+-----+--------------------+------+-------+\n",
      "|alert_message_flag|          timestamp|      date|               node|       date_and_time|      node_repeated|message_type|system_component|level|     message_content|   day|seconds|\n",
      "+------------------+-------------------+----------+-------------------+--------------------+-------------------+------------+----------------+-----+--------------------+------+-------+\n",
      "|                 -|2005-06-04 01:21:45|2005-06-03|R06-M1-N6-C:J15-U01|2005-06-03 18:21:...|R06-M1-N6-C:J15-U01|         RAS|          KERNEL| INFO|1 tree receiver 2...|Friday|   null|\n",
      "|                 -|2005-06-04 02:13:26|2005-06-03|R06-M1-N6-C:J15-U01|2005-06-03 19:13:...|R06-M1-N6-C:J15-U01|         RAS|          KERNEL| INFO|1 tree receiver 2...|Friday|   null|\n",
      "|                 -|2005-06-05 08:55:12|2005-06-05|R06-M1-N6-C:J15-U01|2005-06-05 01:55:...|R06-M1-N6-C:J15-U01|         RAS|          KERNEL| INFO|1 tree receiver 0...|Sunday|   null|\n",
      "|                 -|2005-06-05 08:55:13|2005-06-05|R06-M1-N6-C:J15-U01|2005-06-05 01:55:...|R06-M1-N6-C:J15-U01|         RAS|          KERNEL| INFO|31 tree receiver ...|Sunday|   null|\n",
      "|                 -|2005-06-06 15:08:18|2005-06-06|R27-M1-NB-C:J02-U11|2005-06-06 08:08:...|R27-M1-NB-C:J02-U11|         RAS|          KERNEL| INFO|1 tree receiver 1...|Monday|   null|\n",
      "|                 -|2005-06-06 15:08:20|2005-06-06|R27-M0-NA-C:J02-U11|2005-06-06 08:08:...|R27-M0-NA-C:J02-U11|         RAS|          KERNEL| INFO|1 tree receiver 0...|Monday|   null|\n",
      "|                 -|2005-06-06 15:08:20|2005-06-06|R27-M0-NA-C:J02-U11|2005-06-06 08:08:...|R27-M0-NA-C:J02-U11|         RAS|          KERNEL| INFO|1 tree receiver 0...|Monday|   null|\n",
      "|                 -|2005-06-06 15:08:24|2005-06-06|R06-M1-N6-C:J15-U01|2005-06-06 08:08:...|R06-M1-N6-C:J15-U01|         RAS|          KERNEL| INFO|4 tree receiver 0...|Monday|   null|\n",
      "|                 -|2005-06-06 15:08:24|2005-06-06|R06-M1-N6-C:J15-U01|2005-06-06 08:08:...|R06-M1-N6-C:J15-U01|         RAS|          KERNEL| INFO|4 tree receiver 0...|Monday|   null|\n",
      "|                 -|2005-06-06 15:08:25|2005-06-06|R06-M1-N6-C:J15-U01|2005-06-06 08:08:...|R06-M1-N6-C:J15-U01|         RAS|          KERNEL| INFO|37 tree receiver ...|Monday|   null|\n",
      "+------------------+-------------------+----------+-------------------+--------------------+-------------------+------------+----------------+-----+--------------------+------+-------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/regexp_extract\n",
    "# Defined regular expression pattern to extract the seconds from the message_content\n",
    "#pattern =r\"re-synch state event(?:\\(s\\))? \\(dcr 0x019a\\) detected over (\\d+) seconds\"\n",
    "\n",
    "# The pattern matches logs with the following structure:\n",
    "# - \"re-synch state event\" followed by an optional \"s\" inside non-capturing group \"(?:...)\".\n",
    "# - \"(dcr 0x\" followed by one or more hexadecimal digits \"[0-9A-Fa-f]+\" and then a closing parenthesis \")\".\n",
    "# - Any non-digit characters \"[^0-9]+\" to allow for spaces or other characters between the hexadecimal number and \"seconds\".\n",
    "# - \"(\\d+)\" to capture one or more digits representing the number of seconds.\n",
    "\n",
    "pattern= r\"re-synch state event(?:\\(s\\))? \\(dcr 0x[0-9A-Fa-f]+\\) detected over (\\d+) seconds\"\n",
    "\n",
    "# Using the regular expression pattern to extract the number of seconds from the message_content.\n",
    "# The extracted value will be stored in a new column named \"seconds\" in the DataFrame \"extracted_logs_df\".\n",
    "# The extracted value is cast to an IntegerType to ensure it is represented as an integer.\n",
    "extracted_logs_df = extracted_logs_df.withColumn(\"seconds\", regexp_extract(col(\"message_content\"), pattern, 1).cast(IntegerType()))\n",
    "extracted_logs_df.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "29ac0a4e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 9:============================================>              (3 + 1) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-RECORD 0--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 13:18:52                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R02-M0-NE-C:J02-U11                                                                  \n",
      " date_and_time      | 2005-11-15 05:18:52.775116                                                           \n",
      " node_repeated      | R02-M0-NE-C:J02-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 2 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected over 388 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 388                                                                                  \n",
      "-RECORD 1--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 13:18:53                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R07-M1-N2-C:J02-U11                                                                  \n",
      " date_and_time      | 2005-11-15 05:18:53.547811                                                           \n",
      " node_repeated      | R07-M1-N2-C:J02-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 389 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 389                                                                                  \n",
      "-RECORD 2--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 13:47:41                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R36-M0-N7-C:J03-U11                                                                  \n",
      " date_and_time      | 2005-11-15 05:47:41.018191                                                           \n",
      " node_repeated      | R36-M0-N7-C:J03-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 6 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 390 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 390                                                                                  \n",
      "-RECORD 3--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 13:47:42                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R55-M0-ND-C:J03-U11                                                                  \n",
      " date_and_time      | 2005-11-15 05:47:42.185481                                                           \n",
      " node_repeated      | R55-M0-ND-C:J03-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 390 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 390                                                                                  \n",
      "-RECORD 4--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 13:47:42                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R55-M0-N9-C:J06-U11                                                                  \n",
      " date_and_time      | 2005-11-15 05:47:42.254581                                                           \n",
      " node_repeated      | R55-M0-N9-C:J06-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected over 390 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 390                                                                                  \n",
      "-RECORD 5--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 13:47:42                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R55-M0-N9-C:J06-U11                                                                  \n",
      " date_and_time      | 2005-11-15 05:47:42.278149                                                           \n",
      " node_repeated      | R55-M0-N9-C:J06-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 2 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 390 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 390                                                                                  \n",
      "-RECORD 6--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 13:47:42                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R24-M0-N5-C:J09-U11                                                                  \n",
      " date_and_time      | 2005-11-15 05:47:42.470164                                                           \n",
      " node_repeated      | R24-M0-N5-C:J09-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 2 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected over 389 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 389                                                                                  \n",
      "-RECORD 7--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 13:47:48                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R12-M1-N5-C:J07-U11                                                                  \n",
      " date_and_time      | 2005-11-15 05:47:48.368267                                                           \n",
      " node_repeated      | R12-M1-N5-C:J07-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected over 391 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 391                                                                                  \n",
      "-RECORD 8--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 14:09:21                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R22-M0-N6-C:J02-U01                                                                  \n",
      " date_and_time      | 2005-11-15 06:09:21.402169                                                           \n",
      " node_repeated      | R22-M0-N6-C:J02-U01                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 3 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 1675 seconds \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 1675                                                                                 \n",
      "-RECORD 9--------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 14:23:09                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R12-M0-N4-C:J03-U11                                                                  \n",
      " date_and_time      | 2005-11-15 06:23:09.027484                                                           \n",
      " node_repeated      | R12-M0-N4-C:J03-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 388 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 388                                                                                  \n",
      "-RECORD 10-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 14:23:10                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R03-M1-N9-C:J13-U01                                                                  \n",
      " date_and_time      | 2005-11-15 06:23:10.101627                                                           \n",
      " node_repeated      | R03-M1-N9-C:J13-U01                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 3 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 389 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 389                                                                                  \n",
      "-RECORD 11-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 14:25:42                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R36-M0-N7-C:J03-U11                                                                  \n",
      " date_and_time      | 2005-11-15 06:25:42.782018                                                           \n",
      " node_repeated      | R36-M0-N7-C:J03-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 3 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 388 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 388                                                                                  \n",
      "-RECORD 12-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-15 15:08:07                                                                  \n",
      " date               | 2005-11-15                                                                           \n",
      " node               | R06-M1-N6-C:J15-U01                                                                  \n",
      " date_and_time      | 2005-11-15 07:08:07.048436                                                           \n",
      " node_repeated      | R06-M1-N6-C:J15-U01                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 155 seconds  \n",
      " day                | Tuesday                                                                              \n",
      " seconds            | 155                                                                                  \n",
      "-RECORD 13-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-17 13:34:49                                                                  \n",
      " date               | 2005-11-17                                                                           \n",
      " node               | R77-M1-NA-C:J02-U11                                                                  \n",
      " date_and_time      | 2005-11-17 05:34:49.625862                                                           \n",
      " node_repeated      | R77-M1-NA-C:J02-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected over 43 seconds   \n",
      " day                | Thursday                                                                             \n",
      " seconds            | 43                                                                                   \n",
      "-RECORD 14-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-17 13:34:49                                                                  \n",
      " date               | 2005-11-17                                                                           \n",
      " node               | R55-M0-N9-C:J06-U11                                                                  \n",
      " date_and_time      | 2005-11-17 05:34:49.761766                                                           \n",
      " node_repeated      | R55-M0-N9-C:J06-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected over 43 seconds   \n",
      " day                | Thursday                                                                             \n",
      " seconds            | 43                                                                                   \n",
      "-RECORD 15-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-17 13:34:49                                                                  \n",
      " date               | 2005-11-17                                                                           \n",
      " node               | R55-M0-N9-C:J06-U11                                                                  \n",
      " date_and_time      | 2005-11-17 05:34:49.895129                                                           \n",
      " node_repeated      | R55-M0-N9-C:J06-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 2 tree receiver 2 in re-synch state event(s) (dcr 0x019a) detected over 43 seconds   \n",
      " day                | Thursday                                                                             \n",
      " seconds            | 43                                                                                   \n",
      "-RECORD 16-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-17 13:34:50                                                                  \n",
      " date               | 2005-11-17                                                                           \n",
      " node               | R72-M1-NB-C:J02-U11                                                                  \n",
      " date_and_time      | 2005-11-17 05:34:50.036304                                                           \n",
      " node_repeated      | R72-M1-NB-C:J02-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected over 43 seconds   \n",
      " day                | Thursday                                                                             \n",
      " seconds            | 43                                                                                   \n",
      "-RECORD 17-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-17 13:34:50                                                                  \n",
      " date               | 2005-11-17                                                                           \n",
      " node               | R73-M0-NB-C:J02-U11                                                                  \n",
      " date_and_time      | 2005-11-17 05:34:50.180055                                                           \n",
      " node_repeated      | R73-M0-NB-C:J02-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected over 43 seconds   \n",
      " day                | Thursday                                                                             \n",
      " seconds            | 43                                                                                   \n",
      "-RECORD 18-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-17 13:35:41                                                                  \n",
      " date               | 2005-11-17                                                                           \n",
      " node               | R63-M0-NC-C:J02-U11                                                                  \n",
      " date_and_time      | 2005-11-17 05:35:41.1928                                                             \n",
      " node_repeated      | R63-M0-NC-C:J02-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 1 in re-synch state event(s) (dcr 0x0185) detected over 43 seconds   \n",
      " day                | Thursday                                                                             \n",
      " seconds            | 43                                                                                   \n",
      "-RECORD 19-------------------------------------------------------------------------------------------------\n",
      " alert_message_flag | -                                                                                    \n",
      " timestamp          | 2005-11-17 13:35:52                                                                  \n",
      " date               | 2005-11-17                                                                           \n",
      " node               | R53-M0-NC-C:J02-U11                                                                  \n",
      " date_and_time      | 2005-11-17 05:35:52.677865                                                           \n",
      " node_repeated      | R53-M0-NC-C:J02-U11                                                                  \n",
      " message_type       | RAS                                                                                  \n",
      " system_component   | KERNEL                                                                               \n",
      " level              | INFO                                                                                 \n",
      " message_content    | 1 tree receiver 0 in re-synch state event(s) (dcr 0x01bc) detected over 224 seconds  \n",
      " day                | Thursday                                                                             \n",
      " seconds            | 224                                                                                  \n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "#filters out rows with 'null' values in the 'seconds' column of the DataFrame 'extracted_logs_df'. 'na.drop' method is used to remove rows containing 'null' values in the specified column 'seconds'.\n",
    "extracted_logs_df = extracted_logs_df.na.drop(subset=[\"seconds\"])\n",
    "extracted_logs_df.show(truncate=False, vertical=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f92f6c63",
   "metadata": {},
   "source": [
    "<span style=\"color:blue\">***THE AVERAGE NUMBER OF SECONDS OVER WHICH \"RE-SYNCH STATE EVENTS\" OCCURRED FOR EACH DAY OF THE WEEK***<span style=\"color:blue\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "7abc9605",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "[Stage 10:>                                                         (0 + 6) / 6]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "-RECORD 0-----------------------------\n",
      " day             | Tuesday            \n",
      " average_seconds | 8538.424347826087  \n",
      "-RECORD 1-----------------------------\n",
      " day             | Friday             \n",
      " average_seconds | 6954.137299771167  \n",
      "-RECORD 2-----------------------------\n",
      " day             | Thursday           \n",
      " average_seconds | 8412.631868131868  \n",
      "-RECORD 3-----------------------------\n",
      " day             | Monday             \n",
      " average_seconds | 14511.80625        \n",
      "-RECORD 4-----------------------------\n",
      " day             | Wednesday          \n",
      " average_seconds | 11170.689243027888 \n",
      "-RECORD 5-----------------------------\n",
      " day             | Saturday           \n",
      " average_seconds | 17872.340482573727 \n",
      "-RECORD 6-----------------------------\n",
      " day             | Sunday             \n",
      " average_seconds | 16091.967088607595 \n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "# Calculate the average number of seconds per day for \"re-synch state events\" in the DataFrame 'extracted_logs_df'.\n",
    "# Group the DataFrame by the 'day' column and calculate the average of the 'seconds' column for each group.\n",
    "# The result will be a new DataFrame with two columns: 'day' and 'average_seconds'.\n",
    "average_seconds_per_day=extracted_logs_df.groupBy(\"day\").agg(avg(\"seconds\").alias(\"average_seconds\"))\n",
    "average_seconds_per_day.show(truncate=False, vertical=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "297dc04a",
   "metadata": {},
   "source": [
    "**EXECUTION TIME COUNTER STOP- TOTAL EXECUTION TIME GENERATED BELOW**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "1309b95e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#https://docs.python.org/3/library/time.html\n",
    "#returns the value (in fractional seconds) of a performance counter.It does include time elapsed during sleep and is system-wide.\n",
    "executiontimeend= time.perf_counter()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9d897ef2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9.01429712800018"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculating the total execution time as  time.perf_counter() returns the absolute value of the counter\n",
    "# https://stackoverflow.com/questions/25785243/understanding-time-perf-counter-and-time-process-time\n",
    "totalexecution_time= executiontimeend - executiontimestart\n",
    "totalexecution_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "3efd5715",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - hive</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://192.168.64.4:4044\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.4.0</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>local[*]</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>PySparkShell</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0xffff87b477c0>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# all the information on the 'spark runtime environment', executors sumamry, jobs and its stages can be found on 'Spark UI'\n",
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "3bb018cf",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "[Stage 13:>                                                         (0 + 6) / 6]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------------------+\n",
      "|      day|   average_seconds|\n",
      "+---------+------------------+\n",
      "|  Tuesday| 8538.424347826087|\n",
      "|   Friday| 6954.137299771167|\n",
      "| Thursday| 8412.631868131868|\n",
      "|   Monday|       14511.80625|\n",
      "|Wednesday|11170.689243027888|\n",
      "| Saturday|17872.340482573727|\n",
      "|   Sunday|16091.967088607595|\n",
      "+---------+------------------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "average_seconds_per_day.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d9bc41e",
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}