September 22, 2011 at 12:07 pm
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?
September 22, 2011 at 12:46 pm
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]
December 21, 2012 at 3:54 pm
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