Skip to main content

4. Audit Results

Audit Task List​

The SQL audit list page includes important information.

  1. List Title and Functions:
    • Title is "Audit List", showing an overview of all SQL audit tasks
    • Top has a search box for quickly finding specific audit tasks by SQL or task name
    • "Create Audit" button for starting a new SQL audit task
  2. Basic Audit Task Information:
    • Audit Name: e.g., Audit_WL_2024053011O1, uniquely identifies each audit task
    • Workspace: Some tasks show specific workspaces, e.g., WP_DDL.File_202405221623
    • SQL Count: Number of SQL statements audited in each task, ranging from 8 to 29
    • Audit Template: All tasks use "MySQL Best Template" for auditing
  3. Audit Results and Quality Assessment:
    • Average Score: Numerical indicator reflecting SQL quality, ranging from 5 to 77
    • Audit Level: Different colors and labels indicate the severity of SQL issues
      • Red "Critical": Indicates serious issues needing immediate fixing
      • Yellow "Warning": Indicates issues needing attention
      • Blue "Suggestion": Indicates areas for improvement
    • Issue Count: Numbers after each level show the count of that type, e.g., "Critical: 1 Warning: 222 Suggestion: 16"
  4. Task Management Information:
    • Creator: All tasks created by Admin, suggesting an administrator or key responsible person
    • Creation Time: Records exact time of task creation, sorted in reverse chronological order (newest first)
    • Operation: "Delete" button at each row's end allows removing unwanted audit tasks

image-20240531220531916

Audit Task Summary​

The summary part of the SQL audit includes the following important information:

  1. Audit Summary: Shows basic audit info, like audit name (Audit_WL_202405242237) and template (MySQL Inspection Template).
  2. Creator: The audit was created by Admin.
  3. Creation Time: The audit was created on May 24, 2024, at 22:37:37.
  4. SQL Count: Total number of SQL statements in the audit is 29.
  5. Average Score: The average score given by the audit is 70.

Additionally, the page displays two pie charts:

  1. SQL Type: SQL type distribution, mainly divided into DDL/DML/Query/Other
  2. Risk Level: Risk level distribution, color-coded to indicate different risk levels (red for high risk, green for low risk)

This page has the following Analysis Functions:

  1. Audit Rule Display: The page's right side lists the Top 5 audit rules, such as "Enhance Object Naming Standards", "Suggest Using Strict Constraints for Input Parameters", etc., showing each rule's usage count.
  2. SQL Type Classification: Visually shows SQL statement type distribution via pie chart.
  3. Risk Level Analysis: Uses a pie chart to show distribution of different risk levels, helping to quickly identify high-risk areas.
  4. Score Range Filter: A slider at the bottom allows filtering SQL statements by score range, focusing on specific score intervals.
  5. SQL Statement List: Shows detailed info for each SQL statement, including SQL content, type (e.g., DDL), audit result (e.g., "Critical", "Suggestion") and score.
  6. Filter Function: Top of the page has "Select Rule" and "SQL Type" dropdown menus, allowing users to filter by specific rules or SQL types for precise analysis.

image-20240531220605255

Audit Details​

The details part of the SQL audit shows in-depth analysis for a specific SQL statement, including:

  1. Audit Object: Shows the full text of the currently audited SQL statement. In this example, it's a table creation DDL statement:

    CREATE TABLE REGION (
      R_REGIONKEY INT NOT NULL,
      R_NAME CHAR(25) NOT NULL,
      R_COMMENT VARCHAR(152) DEFAULT NULL
    ) ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 COLLATE=UTF8MB4_0900_AI_CI

  2. Violated Audit Rules: Lists specific rules this SQL statement violates, each with clear description and severity indicator: a. Table must have comment (Warning level) - REGION table lacks comment b. Table must have primary key (Critical level) - REGION table has no defined primary key c. Defining data from character set on table is prohibited (Warning level) - REGION(UTF8MB4) violates this rule d. Non-null columns need specified default value (Warning level) - R_NAME and R_REGIONKEY are non-null but have no default value

  3. Rule Severity: Uses different icons and colors to indicate rule violation severity:

    • Red exclamation mark (⛔): Indicates critical level, e.g., "Table must have primary key"
    • Yellow triangle (⚠): Indicates warning level, e.g., "Table must have comment", etc.

  4. Related Objects: Clearly points out which database objects (like tables, columns) violate rules:

    • REGION table: Lacks comment, primary key
    • REGION(UTF8MB4): Database character set issue
    • R_NAME, R_REGIONKEY columns: Non-null but no default value

  5. Audit Result Feedback: A thumbs-up icon (👍) next to each rule violation description, possibly for user feedback on audit results, like marking false positives or confirming issues.

Through this detailed audit report, developers can clearly understand:

  1. This CREATE TABLE statement has multiple issues needing correction.
  2. The most serious issue is not defining a primary key, which can lead to data integrity and performance problems.
  3. Other issues like missing table comments, using non-standard character sets, while at warning level, should also be adjusted based on organizational best practices.
  4. Non-null columns lacking default values may cause issues when inserting data in some cases.

This detailed audit report not only points out problems but also provides their severity and specific locations, helping developers quickly locate and fix various issues in SQL statements, thus improving database design and SQL writing quality.

image-20240531220651747