Purpose of this query

This SuiteQL statement returns employee access and login activity metrics over the past year, including counts for the last 12 months and last 30 days, the most recent login date, and an activity status. It is optimized to scan only the last 12 months of login audit data for performance and pre-aggregates login events to avoid correlated subqueries. Use this to identify inactive users, verify access, measure adoption, and support compliance or audit reviews. The query filters to employees who have been granted access and are active by default; you can change those filters, the lookback window, or the activity thresholds in the SQL comments.

SuiteQL

SELECT
    COALESCE(e.firstname || ' ' || e.lastname, e.entityid) AS "Employee Name",
    e.email,
    e.isinactive AS "Employee Inactive",
    e.giveaccess AS "Has Access",
    COALESCE(login_stats.login_count_12m, 0) AS "Login Count (12 Months)",
    COALESCE(login_stats.login_count_30d, 0) AS "Login Count (30 Days)",
    login_stats.last_login_date AS "Last Login Date",
    CASE
        WHEN e.giveaccess = 'F' THEN 'No Access Granted'
        WHEN e.isinactive = 'T' THEN 'Employee Inactive'
        WHEN login_stats.last_login_date IS NULL THEN 'Never Logged In'
        WHEN login_stats.last_login_date < ADD_MONTHS(CURRENT_DATE, -3) THEN 'Inactive (90+ Days)'
        WHEN login_stats.last_login_date < ADD_MONTHS(CURRENT_DATE, -1) THEN 'Low Activity (30+ Days)'
        ELSE 'Active User'
    END AS "Activity Status"
FROM
    employee e
LEFT JOIN (
    -- Pre-aggregate login data to avoid correlated subqueries
    -- OPTIMIZATION: Only scan last 12 months of login data
    SELECT 
        la.user AS employee_id,
        COUNT(*) AS login_count_12m,
        COUNT(CASE WHEN la.date >= ADD_MONTHS(CURRENT_DATE, -1) THEN 1 END) AS login_count_30d,
        MAX(la.date) AS last_login_date
    FROM 
        loginaudit la
    WHERE 
        la.date >= ADD_MONTHS(CURRENT_DATE, -12)  -- Only last 12 months
    GROUP BY 
        la.user
) login_stats ON e.id = login_stats.employee_id
WHERE
    e.isinactive = 'F'
    AND e.giveaccess = 'T'  -- OPTIMIZATION: Only employees with access granted
ORDER BY
    login_stats.last_login_date DESC NULLS LAST,
    e.lastname,
    e.firstname;

Sample Output

How the query works

It pre-aggregates login events. The subquery aggregates the login audit table for the last 12 months to produce counts and the last login date per employee, which avoids expensive correlated lookups.
It joins aggregated login stats to the employee table to return name, email, access flags, and activity metrics.
Activity Status is determined by a CASE expression that checks access, inactive flag, whether the user ever logged in, and recency thresholds (30 and 90 days).
The WHERE clause limits results to active employees who have access (giveaccess = ‘T’ and isinactive = ‘F’) for a focused, high-signal list. Remove or change these filters to include inactive accounts or users without access.
Sorting places the most recently active users first while keeping a stable alphabetical fallback.

Why AR team, security, and ops teams benefit from this report

• Security and access reviews: quickly surface accounts with access but no recent activity to remove or review.

• Audit and compliance: provide an auditable export that shows last login dates and activity counts.

• Adoption and training: identify users who rarely log in and may need onboarding or support.

• Incident response: find recent active users when investigating access events.

• Delegation for managers: map inactive or never-logged-in users to managers for follow up (add manager field if desired).

Customization notes and suggested edits

• Lookback window: change ADD_MONTHS(CURRENT_DATE, -12) to a different range if you need more or less history.

• Activity thresholds: edit the 30- and 90-day cutoffs in the CASE expression to match your policy.

• Include inactive or no-access users: remove or change the WHERE filters e.isinactive = ‘F’ and e.giveaccess = ‘T’.

• Add role or department: join role, department, or manager fields to categorize users for delegation and reporting.

• Performance: if loginaudit is large, add additional filters (date ranges or partitions) or materialize login_stats periodically.

• Timezone and date format: adjust last_login_date formatting in Excel after export if needed.

Admin | NetSuite Employee Login Activity and Access Summary