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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy