Using Powershell to loop through a list of servers and parse errorlog

  • Oh mighty Powershell gurus, I come to you humbly seeking guidance.

    Presently, I review error logs on all my servers using managment studio. I do so by sending the block of SQL below to all of my servers using a multiserver query on "local server groups"... basically running is against all of my servers. I use option to "Add server name to results" to get something like this:

    Server NameLogDateProcessInfoLogText

    MyServername2011-09-21 17:16:14.170ServerThis instance of SQL Server last reported using a process ID of 5588 at 9/21/2011 5:16:05 PM (local) 9/21/2011 9:16:05 PM (UTC). This is an informational message only; no user action is required.

    MyServername2011-09-21 17:16:15.390spid7sRecovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

    MyServername2011-09-21 17:16:15.590spid7sResource governor reconfiguration succeeded.

    MyServername2011-09-21 17:16:15.590spid7sSQL Server Audit is starting the audits. This is an informational message. No user action is required.

    MyServername2011-09-21 17:16:15.610spid7sSQL Server Audit has started the audits. This is an informational message. No user action is required.

    MyServername2011-09-21 17:16:15.840spid7sSQL Trace ID 1 was started by login "sa".

    MyServername2011-09-21 17:16:15.890spid7sThe resource database build version is 10.00.4311. This is an informational message only. No user action is required.

    MyServername2011-09-21 17:16:16.180spid7sServer name is 'MyServername'. This is an informational message only. No user action is required.

    MyServername2011-09-21 17:16:16.180spid7sThe NETBIOS name of the local node that is running the server is 'ANodeName'. This is an informational message only. No user action is required.

    My goals are two fold- to make this a little easier, and to learn something about running SQL statements through powershell against multipul servers and ouputting to a file.

    I would like to know see a powershell script which can do the following:

    1.read a list of servers from "serverlist.txt"

    2.run a large block of code (perhaps also read in from "query.txt")

    against each server in that list.

    3.output the results to a single text file in the same format as above: servername, then the results of the query.

    This is what I run through mgmt studio:

    ---------------------

    --Following block parses error log and produces interesting results

    ----------------------

    CREATE TABLE #ErrorLog (

    LogDate DATETIME,

    ProcessInfo NVARCHAR(255),

    LogText NVARCHAR(MAX)

    );

    GO

    INSERT INTO #ErrorLog (

    [LogDate],

    [ProcessInfo],

    [LogText]

    )

    EXEC xp_readerrorlog

    --select * from #ErrorLog

    --where ProcessInfo !='Logon'

    --and LogText not like 'Database backed up%'

    select * from #ErrorLog

    where ProcessInfo !='Logon'

    and LogText not like 'Database backed up%'

    and LogText not like 'Log was backed up%'

    and LogText not like '%found 0 errors%'

    --and LogText not like 'This instance of SQL Server has been using a process ID%'

    and LogText not like 'Configuration option ''user options'' changed from 0 to 0. Run the RECONFIGURE statement to install.'

    and LogText not like 'Microsoft SQL Server 200%'

    and LogText not like '(c) %'

    and LogText not like 'All rights rese%'

    and LogText not like 'Server process ID is%'

    and LogText not like 'System Manufacturer:%'

    and LogText not like 'Authentication mode is %'

    and LogText not like 'Logging SQL Server messages in file%'

    --and LogText not like 'This instance of SQL Server last reported using a process ID o%'

    and LogText not like 'Registry startup parameters:%'

    and LogText not like 'SQL Server is starting at normal priority base%'

    and LogText not like 'Detected % CPUs. This is an informational messag%'

    and LogText not like 'Using locked pages for buffer pool%'

    and LogText not like 'Using dynamic lock allocation.%'

    and LogText not like 'Node configuration: node 0: CPU mask%'

    and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100%'

    and LogText not like 'Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServerTempDB.'

    and LogText not like 'Server is listening on %'

    and LogText not like 'Server local connection provider is ready to accept connection on%'

    and LogText not like 'The SQL Server Network Interface library successfully registered the Service Principal Name%'

    and LogText not like 'Service Broker manager has started.'

    and LogText not like 'Starting up database%'

    and LogText not like 'CHECKDB for database % finished without errors on %'

    and LogText not like 'FILESTREAM: effective level = 0, configured level = 0%'

    and LogText not like 'AppDomain % unloaded.'

    --change the 72 below to alter timeframe of log read.

    and LogDate> DATEADD(hh, - 72, GETDATE())

    drop table #ErrorLog

    Any takers?

  • I'm not sure to what extent this will suit your needs, but here is a script that loops through a list of servers stored in a db table and executes a sql query on each of them:

    #Variables

    $dbadmin = "server1\instance1";

    $query = "SELECT [ServerName] FROM [RPTDB].[dbo].[ServerNames]";

    $results = Invoke-Sqlcmd -Query $query -ServerInstance $dbadmin;

    foreach ($result in $results)

    {

    $server = $result.ServerName;

    $query = "EXEC UTILDB.dbo.procname1 @server = '" + $server + "'";

    $file = "D:\APP\APP1_" + $server.replace("\","-") + ".txt";

    sqlcmd -Q $query -S $serverProd -o $file;

    }

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Thanks, Marios. This was helpful.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply