Logins Not being used in last one month I have a requirement to create a query which can get the logins details which were not used in last one month.
I have tried several way like extended events and audit tracing. But i'm unable to get the list of the logins that were not being used in last one month,
Please help me with this.
Since you've already added the successful login auditing to the instance, you can dump the error log data to a #temp table, then parse the individual login names, and then find all the rows in sys.server_principals that don't exist in the filtered data.

CREATE TABLE #x
(
  LogDate datetime,
  ProcessInfo nvarchar(max),
  [Text] nvarchar(max)
);

DECLARE @dt datetime = DATEADD(MONTH, -1, GETDATE());

INSERT #x(LogDate, ProcessInfo, [Text]) 
  EXEC master.sys.xp_readerrorlog 0, 1, N'Login succeeded', NULL, 
    @dt;

;WITH p(pattern) AS 
(
  SELECT SUBSTRING([text], CHARINDEX(CHAR(39), [text]) + 1, 500) FROM #x
),
x(name) AS 
(
  SELECT SUBSTRING(pattern, 1, CHARINDEX(CHAR(39), pattern)-1) FROM p
)
SELECT p.name, p.create_date, p.is_disabled
  FROM sys.server_principals AS p
  WHERE p.principal_id &gt; 255      -- no system logins
  AND p.name NOT LIKE N'##%##'    -- e.g. ##MS_AgentSigningCertificate
  AND p.name NOT LIKE N'NT %\%'   -- e.g. NT AUTHORITY\SYSTEM
  AND p.name NOT IN (SELECT name FROM x);


Assuming that perhaps your current error log file is less than a month old, you can see the following article for ideas on searching more than one error log at a time:


Search multiple SQL Server Error Logs at the same time




In the future, I would rather create an audit specification or extended events session to avoid polluting the error log.

CREATE SERVER AUDIT GoodLogins
TO FILE (FILEPATH = 'C:\temp\GoodLogins\');
GO

CREATE SERVER AUDIT SPECIFICATION GoodLoginsSpec
  FOR SERVER AUDIT GoodLogins
  ADD (SUCCESSFUL_LOGIN_GROUP);
GO

ALTER SERVER AUDIT GoodLogins WITH (STATE = ON);
GO


Then you can just query all of the audit data:

DECLARE @dt datetime = DATEADD(MONTH, -1, GETDATE());

;WITH x(name) AS 
(
  SELECT server_principal_name
    FROM sys.fn_get_audit_file('C:\temp\GoodLogins\*', NULL, NULL)
    WHERE event_time &gt;= @dt
    GROUP BY server_principal_name 
)
SELECT p.name
  FROM sys.server_principals AS p
  WHERE p.principal_id &gt; 255      -- no system logins
  AND p.name NOT LIKE N'##%##'    -- e.g. ##MS_AgentSigningCertificate
  AND p.name NOT LIKE N'NT %\%'   -- e.g. NT AUTHORITY\SYSTEM
  AND p.name NOT IN (SELECT name FROM x);