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

• NJ-DBA

  SSChampion

  Points: 13834

  More actions

  September 22, 2011 at 12:07 pm

  #243621

  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?

• Marios Philippopoulos

  SSC Guru

  Points: 57190

  More actions

  September 22, 2011 at 12:46 pm

  #1385380

  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]

• David Benoit

  SSC-Dedicated

  Points: 34562

  More actions

  December 21, 2012 at 3:54 pm

  #1571096

  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