Querying Imported Data with SQL
In the previous tutorial, you imported JSON data into the security_events table. In this tutorial, you will use SQL queries to analyse the imported data.
View All Events
Open phpMyAdmin and select the:
security_events
table.
Run:
SELECT *
FROM security_events;
This displays all imported records.
Count All Events
To find the total number of events:
SELECT COUNT(*) AS total_events
FROM security_events;
Example result:
| total_events |
|---|
| 3 |
This query is useful for dashboards and reporting.
Find Critical Events
To display only critical events:
SELECT *
FROM security_events
WHERE severity = 'critical';
Example result:
| device_id | event_type | severity |
|---|---|---|
| CAM-01 | remote_access_attempt | critical |
Count Events by Severity
Run:
SELECT
severity,
COUNT(*) AS total
FROM security_events
GROUP BY severity;
Example result:
| severity | total |
|---|---|
| critical | 1 |
| high | 1 |
| medium | 1 |
This is often used to build dashboard summary cards.
Find Events from a Specific Device
To display events generated by the security camera:
SELECT *
FROM security_events
WHERE device_id = 'CAM-01';
Example result:
| device_id | event_type |
|---|---|
| CAM-01 | motion_detected |
| CAM-01 | remote_access_attempt |
Count Events Per Device
Run:
SELECT
device_id,
COUNT(*) AS total_events
FROM security_events
GROUP BY device_id;
Example result:
| device_id | total_events |
|---|---|
| CAM-01 | 2 |
| LOCK-02 | 1 |
This identifies which devices are generating the most activity.
Find High-Risk Events
To display high and critical events:
SELECT *
FROM security_events
WHERE severity IN ('high', 'critical');
Example result:
| device_id | event_type | severity |
|---|---|---|
| CAM-01 | motion_detected | high |
| CAM-01 | remote_access_attempt | critical |
This query could be used to generate security alerts.
Sort Events by Time
To view the newest events first:
SELECT *
FROM security_events
ORDER BY event_timestamp DESC;
This is commonly used in event logs and monitoring systems.
Useful Dashboard Queries
Total Events
SELECT COUNT(*) AS total_events
FROM security_events;
Critical Events
SELECT COUNT(*) AS critical_events
FROM security_events
WHERE severity = 'critical';
Device Count
SELECT COUNT(DISTINCT device_id) AS total_devices
FROM security_events;
Events by Severity
SELECT
severity,
COUNT(*) AS total
FROM security_events
GROUP BY severity;
These queries are commonly used when building dashboards.
Complete Practice Queries
SELECT *
FROM security_events;
SELECT COUNT(*) AS total_events
FROM security_events;
SELECT *
FROM security_events
WHERE severity = 'critical';
SELECT
severity,
COUNT(*) AS total
FROM security_events
GROUP BY severity;
SELECT
device_id,
COUNT(*) AS total_events
FROM security_events
GROUP BY device_id;
SELECT *
FROM security_events
ORDER BY event_timestamp DESC;
You have successfully queried imported JSON data using SQL.
Next tutorial: Building a Security Dashboard Using PHP and MySQL.