You probably want to use something like,
CREATE OR REPLACE TRIGGER usr_t_audit_user_alnd AFTER LOGON ON DATABASE
BEGIN
-- insert audit record only if user is one of specified, else ignore
if ora_login_user in ('USER_A','USER_C','USER_D') then
INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
end if;
END;
You can't do this at the audit specification level, but you can do it at the server audit level. So, if this audit does other things too, and you don't want to filter out those events by login, you will need to create a separate audit. Assuming you want to filter out all activity of any kind by this service account, you can do this:
ALTER SERVER AUDIT OurAudit WITH (STATE = OFF);
GO
ALTER SERVER AUDIT OurAudit WHERE server_principal_name <> N'service account name';
GO
ALTER SERVER AUDIT OurAudit WITH (STATE = ON);
GO
Depending on how they connect, authenticate, perhaps impersonate, and how the login is mapped to the database user, you may need to experiment with this and filter against database_principal_name
or session_server_principal_name
instead of server_principal_name
. I just tested this on SQL Server 2012 and it seems to work as you would expect. I don't believe it will work on 2008 or 2008 R2 (not relevant for you, but for future readers).
I still think that filtering a large portion of your activity seems suspect and not really in the spirit of auditing. You must have pretty laissez-faire auditors if they just trust that you protect your passwords for service accounts. Someone must know them, right? An auditor's job is to not inherently trust anyone.
Best Answer
The audit data uses UTC time. See this link for suggestions to adjust your query.