xp_readerrorlog

To read the SQL Server errorlog through T-SQL one usually uses the system stored procedure sys.sp_readerrorlog. Unfortunately you can't grant execute right on this to others than members of the server roles sysadmin and securityadmin or logins with VIEW SERVER STATE right. It would be nice to grant this right to trainees or other that does not have the mentioned administrative rights.
But when you look at the definition of the stored procedure you see a IF-statement that finters on the mentioned rights. Also you see that the extended stored procedure sys.xp_readerrorlog is called with various parameters.
So going to the source and taking a deeper look at sys.xp_readerrorlog. It turns out that this procedure has more parameters and the more possibilities than the procedure sys.sp_readerrorlog. These parameters are unfortunately not documented by Microsoft – actually the procedure sys.xp_readerrorlog is not documented at all by Microsoft.
But using my favorite internet search engine I found eight different parameters.

xp_readerrorlog syntax

The procedure can be called with two different sets of parameter names where the names are long a descriptive or the parameter names are short just indicating the position in the parameter list.

sys.xp_readerrorlog @ArchiveID[, @LogType[, @FilterText1[, @FileterText2[, @FirstEntry[, @LastEntry[, @SortOrder[, @InstanceName]]]]]]]

sys.xp_readerrorlog @p1[, @p2[, @p3[, @p4[, @p5[, @p6[, @p7[, @p8]]]]]]]

Name	Type	Description
@ArchiveId @p1	[int]	Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
@LogType @p2	[int]	Log file type: 1 or NULL = error log, 2 = SQL Agent log
@FilterText1 @p3	[nvarchar()]	Search string 1: String one you want to search for
@FilterText2 @p4	[nvarchar()]	Search string 2: String two you want to search for to further refine the results
@FirstEntry @p5	[datetime]	Search from start time
@LastEntry @p6	[datetime]	Search to end time
@SortOrder @p7	[nchar()]	Sort order for results: N'asc' = ascending, N'desc' = descending
@InstanceName @p8	[nvarchar()]	Instance name. This parameter actually I don't get. A database instance name has no influence.

When a string is used for parameter value then mark string af unicode string with N and single quotes (N'<value>').

You can call the procedure without giving parameter names, but if you want to omit the parameter name somewhere in the execution string then the subsequent parameter must be called without parameter name or you will get an error like this
Msg 119, Level 15, State 1, Line 18
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Examples

Read current errorlog file

EXECUTE master.sys.xp_readerrorlog;

Read current errorlog file in descending order

EXECUTE master.sys.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc';

Read previous errorlog file

EXECUTE master.sys.xp_readerrorlog 1;

Read messages on failed backup in current errorlog file

EXECUTE master.sys.xp_readerrorlog 0, 1, N'backup', N'failed';

Read messages on failed login in current errorlog file

EXECUTE master.sys.xp_readerrorlog 0, 1, N'login', N'failed';

Read messages after a given time

EXECUTE master.sys.xp_readerrorlog 0, 1, @p3 = NULL, @p4 = NULL, @p5 = '2020-01-07 14:36:29';

Read all messages between two given times

EXECUTE master.sys.xp_readerrorlog 0, 1, NULL, NULL, @FirstEntry = '2020-01-07 14:36:29', @LastEntry = '2020-01-07 18:00';

Read messages with newest first

EXECUTE master.sys.xp_readerrorlog 0, 1, NULL, NULL, @FirstEntry = '2020-01-07 20:56:29', @LastEntry = NULL, @SortOrder = N'desc';

Reading all errorlog files

Some solutions use xp_dirtree to get the number of errorlog files, but output needs some treatment to be usable.
Using the procedure xp_instance_regread instead gives a direct usable output.

DECLARE @search_string1 nvarchar(256) = N'backup';
DECLARE @search_string2 nvarchar(256) = N'failed';

DECLARE @num_errorlogs int;
EXECUTE xp_instance_regread
  @rootkey=N'HKEY_LOCAL_MACHINE',
  @key=N'Software\Microsoft\MSSQLServer\MSSQLServer',
  @value_name=N'NumErrorLogs',
  @value=@num_errorlogs OUTPUT;

DECLARE @errors AS TABLE (
  LogDate datetime,
  ProcessInfo nvarchar(64),
  [Text] nvarchar(4000)
)
DECLARE @i int = 0;
WHILE @i <= @num_errorlogs
BEGIN
  INSERT INTO @errors EXECUTE xp_readerrorlog @i, 1, @search_string1, @search_string2;
  SET @i = @i + 1;
END
SELECT * FROM @errors;

Reference

mssqltips.com: „Reading the SQL Server log files using TSQL“
(www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql)

sqlserver-help.com: „SQL Internals : Useful Parameters for xp_readerrorlog“
(sqlserver-help.com/2014/12/10/sql-internals-useful-parameters-for-xp_readerrorlog)