JDE Security History (F9312) – Tracking User Activity, Logins, Additions, and Deletions

In Oracle JD Edwards EnterpriseOne, security auditing is a critical capability for tracking user behavior, compliance, and system integrity. One of the most important tables used for this purpose is F9312 – Security History Table, which captures key security events such as logins, user creation, and deletions.

This blog explains how to use F9312 effectively with practical SQL queries for common audit requirements.

What is F9312?

The F9312 (Security History File) stores historical security event logs generated by JD Edwards EnterpriseOne Security Server.

It helps track:

• Failed login attempts
• User creation events
• User deletion events
• Other security-related activities

Each record includes:

• User ID
• Event Type (SHEVTYP)
• Event Status (SHEVSTAT)
• Date (SHUPMJ – Julian date format)
• Time and additional audit details

1. Failed Login Attempts for a Specific User

To identify failed login attempts for a user:

SELECT *
FROM SY920.F9312
WHERE SHUSER = 'xxxxx'
  AND SHEVTYP = '01'
  AND SHEVSTAT = '02';

Explanation:

• SHUSER → User ID
• SHEVTYP = '01' → Login Event
• SHEVSTAT = '02' → Failed Login Status

👉 This query helps detect brute-force attempts or incorrect password usage patterns.

2. User Creation Events (From a Given Date)

To track when users were added:

SELECT *
FROM SY920.F9312
WHERE SHEVTYP = '05'
  AND SHUPMJ >= 125001;

Explanation:

• SHEVTYP = '05' → User Creation Event
• SHUPMJ >= 125001 → Records from 01-Jan-2025 onwards (Julian format)

👉 Useful for onboarding audits and compliance checks.

3. User Deletion Events (From a Given Date)

To track deleted users:

SELECT *
FROM SY920.F9312
WHERE SHEVTYP = '06'
  AND SHUPMJ >= 125001;

Explanation:

• SHEVTYP = '06' → User Deletion Event
• SHUPMJ >= 125001 → From 01-Jan-2025 onwards

👉 Helps ensure no unauthorized user removals occurred.

4. Common SHEVTYP (Event Types)

Below are some commonly used event type codes in F9312:

Here is a List of all Event Tyoe (SHEVTYP)

Why F9312 is Important

Using F9312 effectively helps organizations:

• Strengthen security monitoring
• Detect suspicious login behavior
• Maintain compliance (SOX, audit requirements)
• Track administrative changes in real time
• Improve governance in ERP systems

Best Practices

• Regularly archive F9312 data to avoid performance issues
• Build dashboards for failed login trends
• Alert on repeated failed login attempts
• Combine with user profile tables for deeper analysis
• Restrict access to security audit tables
• 
  

Final Thoughts

The F9312 Security History table in Oracle JD Edwards EnterpriseOne is a powerful but often underutilized tool. With the right queries and monitoring strategy, it can significantly improve your enterprise security posture and audit readiness.

SQL Server: Identify Data Files with Less Than 10% Free Space

 

In database administration, monitoring storage utilization is critical to avoid performance degradation, blocking issues, and unexpected outages. One of the most important checks for a DBA is identifying data files that are running low on free space.

In this blog, we’ll look at a practical approach to identify database files in Microsoft SQL Server that have less than 10% free space using a dynamic query across all databases.

Why Monitoring File Space Matters

When data files run out of space, it can lead to:

• Transaction failures
• Slow query performance
• Blocking and deadlocks
• Application downtime
• Emergency auto-growth events (which are expensive)

Proactive monitoring helps DBAs:

• Plan storage expansion
• Avoid emergency outages
• Optimize database performance

Solution Overview

The following script:

• Scans all databases
• Collects data file size and free space
• Stores results in a temporary table
• Filters files with less than 10% free space

SQL Query to Identify Low-Space Data Files

CREATE TABLE #FileSize

(

    dbName NVARCHAR(128), 

    FileName NVARCHAR(128), 

    type_desc NVARCHAR(128),

    CurrentSizeMB DECIMAL(10,2), 

    FreeSpaceMB DECIMAL(10,2),

    FreeSpacePercentage DECIMAL(10,2)

);

INSERT INTO #FileSize

(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB, FreeSpacePercentage)

EXEC sp_msforeachdb 

'

USE [?];

SELECT 

    DB_NAME() AS DbName,

    name AS FileName,

    type_desc,

    CAST((size * 8.0 / 1024) AS DECIMAL(10, 2)) AS CurrentSizeMB,

    CAST(((size - FILEPROPERTY(name, ''SpaceUsed'')) * 8.0 / 1024) AS DECIMAL(10, 2)) AS FreeSpaceMB,

    CAST((((size - FILEPROPERTY(name, ''SpaceUsed'')) * 100.0) / size) AS DECIMAL(10, 2)) AS FreeSpacePercentage

FROM sys.database_files

WHERE type IN (0,1);

';

SELECT * 

FROM #FileSize

WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb')

AND FreeSpacePercentage < 10;

DROP TABLE #FileSize;

How This Query Works

1. Temporary Table Creation

A staging table #FileSize is created to store results from all databases.

2. Iterating Through All Databases

We use:

sp_msforeachdb

This stored procedure loops through every database in the instance and executes the query.

3. Collecting File Metrics

From:

sys.database_files

We extract:

• File name
• File type (data/log)
• Total size
• Used space
• Free space percentage

4. Filtering Critical Files

We exclude system databases:

• master
• model
• msdb
• distribution

Then we filter:

FreeSpacePercentage < 10

This highlights files that are running critically low on space.

Output Example

dbName	FileName	type_desc	CurrentSizeMB	FreeSpaceMB	FreeSpacePercentage
SalesDB	Sales_Data	ROWS	20480	1200	5.8
HRDB	HR_Data	ROWS	10240	800	7.9

Best Practices

1. Set Alerts

Integrate this query into SQL Agent jobs for proactive alerting.

2. Avoid Frequent Auto-Growth

Instead of relying on auto-growth, plan capacity ahead of time.

3. Separate Data and Log Monitoring

Data files and log files behave differently—monitor both independently.

4. Use Performance Dashboards

Visualize file growth trends using tools like:

• Power BI
• Grafana
• SSRS

Enhancements You Can Add

If you want to extend this script further:

• Add disk-level free space checks
• Include auto-growth settings
• Identify top growing databases
• Export results to email alerts

Final Thoughts

In Microsoft SQL Server environments, storage issues are one of the most common causes of production incidents. This simple query helps DBAs proactively identify databases running low on space and take corrective action before users are impacted.