Skip to main content

Building a Security Dashboard Using PHP and MySQL

InThis thisstandalone tutorial,example youqueries willfictional buildsecurity-event data with PDO and presents useful summary and detail output.

A dashboard is meaningful when each output answers a dashboarduser thatquestion. displaysDo securitynot statisticsadd directlya fromstatistic or chart merely because it is easy to calculate.

Connect with PDO

Use the shared connection created in includes/database.

The dashboard will display:

    Total Devices Total Events Critical Events High Events Recent Security Events

    Create the Dashboard File

    Create a new file called:

    dashboard.php

    Add the database connection::

    <?php
$conn = new mysqli(
    "localhost",
    "root",
    "",
    "project_db"
)declare(strict_types=1);

ifrequire ($conn->connect_error) {
    die("Connection failed: "__DIR__ . $conn->connect_error)'/includes/database.php';
}

?>

    Do

    Getnot repeat database credentials in the Totaldashboard Numberpage. of

    Query Eventsuseful summaries

    Add:

    $totalEventsQuerytotalEvents = "(int) $pdo
    ->query('SELECT COUNT(*) AS total_events
    FROM security_eventssecurity_events')
    "->fetchColumn();

$totalEventsResulthighRiskEvents = (int) $conn-pdo
    ->query($totalEventsQuery);

$totalEvents =
    $totalEventsResult->fetch_assoc()["total_events"];

    Get the Number of Devices

    Add:

    $totalDevicesQuery =
        "
    SELECT COUNT(DISTINCT device_id)
    AS total_devices
    FROM security_events
";

$totalDevicesResult =
    $conn->query($totalDevicesQuery);

$totalDevices =
    $totalDevicesResult->fetch_assoc()["total_devices"];

    Get Critical Events

    Add:

    $criticalEventsQuery = "
    SELECT COUNT(*) AS critical_events
         FROM security_events
         WHERE severity =IN ('high', 'critical')"
    ")
    ->fetchColumn();

$criticalEventsResultdeniedEvents = (int) $conn-pdo
    ->query($criticalEventsQuery);

$criticalEvents =
    $criticalEventsResult->fetch_assoc()["critical_events"];

    Get High Events

    Add:

    $highEventsQuery =
        "
    SELECT COUNT(*) AS high_events
         FROM security_events
         WHERE severityaccess_result =IN ('denied', 'high'blocked')"
    ";)
    $highEventsResult =
    $conn-->query($highEventsQuery);

$highEvents =
    $highEventsResult->fetch_assoc(fetchColumn()["high_events"];

    These queries answer three different questions: how much activity exists, how much is high priority, and how often access was prevented.

    CreateRetrieve therecent Dashboard Pagerecords

    Add the following HTML underneath the PHP code:

    <!DOCTYPE html>
<html>
<head>
    <title>Security Dashboard</title>
</head>
<body>

<h1>Security Dashboard</h1>

<div>
    <h2>Total Devices</h2>
    <p><?php echo $totalDevices; ?></p>
</div>

<div>
    <h2>Total Events</h2>
    <p><?php echo $totalEvents; ?></p>
</div>

<div>
    <h2>Critical Events</h2>
    <p><?php echo $criticalEvents; ?></p>
</div>

<div>
    <h2>High Events</h2>
    <p><?php echo $highEvents; ?></p>
</div>

</body>
</html>

    Test the Dashboard

    Open:

    http://localhost/json-import/dashboard.php

    Using the sample data, you should see something similar to:

    Security Dashboard

Total Devices: 2
Total Events: 3
Critical Events: 1
High Events: 1

    Display Recent Events

    Add the following query before the HTML:

    $recentEventsQueryrecentStatement = "$pdo->query(
    'SELECT
        *event_timestamp,
        device_id,
        room,
        event_type,
        severity,
        access_result
     FROM security_events
     ORDER BY event_timestamp DESC
     "LIMIT 10'
);

$recentEvents = $conn-recentStatement->query($recentEventsQuery)fetchAll();

    Select

    Createonly the Eventsfields Table

    needed

    Addby the followinginterface.

    underneath

    Present theaccessible dashboardsummary cards:output

    <section aria-labelledby="summary-heading">
  <h1 id="summary-heading">Security event summary</h1>

  <div class="summary-grid">
    <article class="card">
      <h2>RecentTotal Eventsevents</h2>
      <p><?= $totalEvents ?></p>
    </article>

    <article class="card">
      <h2>High-priority events</h2>
      <p><?= $highRiskEvents ?></p>
    </article>

    <article class="card">
      <h2>Denied or blocked</h2>
      <p><?= $deniedEvents ?></p>
    </article>
  </div>
</section>

    Headings and text communicate the meaning without relying on colour alone.

    Present recent events safely

    <?php if (!$recentEvents): ?>
  <p>No fictional events are available.</p>
<?php else: ?>
  <div class="table-wrap">
    <table>
      border="1"<caption>Ten most recent fictional security events</caption>
      <thead>
        <tr>
          <th scope="col">Time</th>
          <th scope="col">Device</th>
          <th scope="col">Event TypeRoom</th>
          <th scope="col">Event</th>
          <th scope="col">Severity</th>
          <th scope="col">TimestampAccess result</th>
        </tr>
      </thead>
      <tbody>
        <?php whileforeach ($recentEvents as $row =
    $recentEvents->fetch_assoc()
) {event): ?>
          <tr>
            <td><?php= echo htmlspecialchars($row["device_id"event['event_timestamp'];) ?></td>
            <td><?php= echo htmlspecialchars($row["event_type"event['device_id'];) ?></td>
            <td><?php= echo htmlspecialchars($row["severity"event['room'];) ?></td>
            <td><?php= echohtmlspecialchars($event['event_type']) ?></td>
            <td><?= htmlspecialchars($row["event_timestamp"event['severity'];) ?></td>
            <td><?= htmlspecialchars($event['access_result']) ?></td>
          </tr>
        <?php }endforeach; ?>
      </tbody>
    </table>
  </div>
<?php endif; ?>

    Refreshhtmlspecialchars() theprevents page.

    stored

    Youtext shouldfrom nowbeing seeinterpreted allas importedpage events underneath the dashboard.markup.

    AddResponsive Basic Stylingstyling

    Inside the <head> section add:

    <style>

body.summary-grid {
    font-family:display: Arial,grid;
    sans-serif;grid-template-columns: repeat(auto-fit, minmax(12rem, 1fr));
    gap: 1rem;
}

.cardtable-wrap {
    border:overflow-x: 1px solid #cccccc;
    border-radius: 8px;
    padding: 15px;
    margin-bottom: 10px;auto;
}

table {
    width: 100%;
    border-collapse: collapse;
    width: 100%;
}

th,
td {
    padding: 0.75rem;
    border: 1px solid #cccccc;#cbd5e1;
    padding:text-align: 10px;left;
}

th {
    background-color: #f2f2f2;
}

</style>

    Then

    Test update eachthe dashboard <div>: 

    <div class="card">

    The dashboard should now look much more professional.

    Complete Dashboard Features

    YourConfirm dashboard now:that:

    • Connectseach tototal MySQLmatches an independent SQL check;
    • Runsan SQLempty queriesdatabase has a clear message;
    • Displaystext summaryis statisticsescaped;
    • Displaysthe recenttable eventsremains usable on a narrow screen;
    • Updatesheadings automaticallyexplain whenevereach newvalue; JSONand
    dataall isrecords importedare fictional.

    This is one of the major advantages of importing JSON into a database rather than displaying the JSON directly.

    Back to top