Get-ServerErrors Powershell Function

Posted on January 6, 2012, 4:57 pm, by Rhys, under DBA, Powershell.

Here’s a little Powershell function I’m using to check the Event Logs and SQL Server Error Logs in one easy swoop;

?View Code POWERSHELL
function Get-ServerErrors
{
	# Server to check & hours back. Will only support default sql instances
	# Could add a third param for instance and modify script where appropriate if needed
	param ($server, [int]$hours);
 
	[datetime]$after = $(Get-Date).AddHours(-$hours);
 
	# Windows Event Log (Application & System) Errors & Warnings
 
	Write-Host "Application Event Log Errors from $server after $after";
	Write-Host "========================================================================";
	Get-EventLog -ComputerName $server -LogName "Application" -EntryType "Error" -After $after | Format-List;
	Write-Host "Press any key to continue or ctrl + c to quit";
	$r = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown");
 
	Write-Host "System Event Log Errors from $server after $after";
	Write-Host "========================================================================";
	Get-EventLog -ComputerName $server -LogName "System" -EntryType "Error" -After $after | Format-List;
	Write-Host "Press any key to continue or ctrl + c to quit";
	$r = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown");
 
	Write-Host "Application Event Log Warnings from $server after $after";
	Write-Host "========================================================================";
	Get-EventLog -ComputerName $server -LogName "Application" -EntryType "Warning" -After $after | Format-List;
	Write-Host "Press any key to continue or ctrl + c to quit";
	$r = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown");	
 
	Write-Host "System Event Log Warnings from $server after $after";
	Write-Host "========================================================================";
	Get-EventLog -ComputerName $server -LogName "System" -EntryType "Warning" -After $after | Format-List;
	Write-Host "Press any key to continue or ctrl + c to quit";
	$r = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown");	
 
	# SQL Server Error Log
	[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") ;
	$sql_server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server;
	$sql_server.ReadErrorLog() | Where-Object {$_.Text -like "Error*" -and $_.LogDate -ge $after};
}

Usage is as follows;

?View Code POWERSHELL
Get-ServerErrors <server name> <hours back to check>;

No Comments »

Working with multiple computers in Bash

Posted on December 30, 2011, 4:12 pm, by Rhys, under Bash, Linux.

Working with multiple computers in Powershell is absurdly easy using the Get-Content cmdlet to read computer names from a text file. It’s as easy as this…

?View Code POWERSHELL
$a = Get-Content "C:\Scripts\Test.txt"
 
foreach ($i in $a)
    {$i + "`n" + "=========================="; Get-WMIObject Win32_BIOS -computername $i}

As I’m doing more and more bash scripting I thought it would be useful to replicate this. Here’s how you do it;

?View Code BASH
#!/bin/bash
 
for c in `cat computers.txt`
do
        # Do work with each computer here...
        echo "Computer = $c";
done

No Comments »

List AD Organizational Units with Powershell

Posted on December 30, 2011, 12:28 pm, by Rhys, under Powershell.

Here’s a quick Powershell one-liner to list all the Organizational Units, or OUs, in your Active Directory domain. Firstly you’ll probably need to load the ActiveDirectory module. This can be done at the Powershell prompt with the below command;

?View Code POWERSHELL
Import-Module ActiveDirectory;

Then we can use the Get-ADOrganizationalUnit cmdlet to retrieve a list of OUs.

?View Code POWERSHELL
Get-ADOrganizationalUnit -Filter * | Select-Object -Property Name | Format-Table -AutoSize;

This will display a list looking something like below;

Name
----
Domain Controllers
Microsoft Exchange Security Groups
Security Groups
Domain Servers
Domain Workstations
Domain Guest Accounts
Printers
Management
Virtual Desktops
IT
Service Accounts
Users
Computers
Production Servers
SQL Servers
Web Servers

No Comments »

Beware the Powershell -Contains operator

Posted on November 29, 2011, 12:20 pm, by Rhys, under Powershell.

Many of us tend to jump quickly, into a new programming or scripting language, applying knowledge we’ve learned elsewhere to the current task at hand. Broadly speaking this works well but these always a little gotcha to trip you up!

A good example is the Powershell -contains operator. It’s not like .Net String.Contains method as you might first think.

?View Code POWERSHELL
$var = "Rhys Campbell";
$var -contains "Rhys";

The result of this is false. Why? Because the -contains operator in Powershell is used for testing for items in an array.

?View Code POWERSHELL
$var = "Rhys Campbell", "Joe Bloggs";
$var -contains "Rhys Campbell";

Will return true, but;

?View Code POWERSHELL
$var -contains "Rhys*";

Will return false as wildcards will not work as we assume they will. For wildcard matching we can use the -match operator.

?View Code POWERSHELL
$var = "Rhys Campbell";
$var -match "Rhys";

This will return true when matching simple strings or the matching items from an array of strings. Now I’m going to try and remember what assumption is the mother of (NSFW, contains cussing).


Tags: ,
2 Comments »

Monitoring SSRS Subscriptions with Powershell

Posted on November 25, 2011, 1:17 pm, by Rhys, under DBA, Powershell, SSRS.

We don’t use SSRS much at my workplace but its usage is slowly creeping up. I realised that none of us are keeping an eye on the few subscriptions we have set-up. So I decided to do something about that.

Here’s a bit of Powershell code that uses the SSRS Web Service to pull out a list of subscriptions from SSRS and print out some information to the screen. As always I try to bring problems to attention with a little red text so you can identify any failed subscriptions. There’s a few assumptions in the script, so be sure to read the comments, and it’s a far from complete and fully tested script. Just a little something to get me started onto something better.

?View Code POWERSHELL
# If you use multiple ssrs server just stick in another foreach loop
# and iterate over an array of ssrs server names.
$reportserver = "sqlserver";
$url = "http://$($reportserver)/reportserver/reportservice.asmx?WSDL";
 
$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential -Namespace "ReportingWebService";
 
# Uncomment to list all available methods &amp; properties in the ssrs web service...
#$ssrs | Get-Member;
 
# Get datasources (assumes default path to folder)
$datasources = $ssrs.ListChildren("/Data Sources", $true);
 
# for each datasource
foreach($ds in $datasources)
{
	# Get reports
	$reports = $ssrs.ListReportsUsingDataSource($ds.Path);
	# For each report
	foreach($r in $reports)
	{
		# Get all subscriptions
		$subscriptions = $ssrs.ListSubscriptions($r.Path, $r.Owner);
		# foreach subscription
		foreach($s in $subscriptions)
		{
			# Uncomment to view more subscription methods &amp; properties
			# $s | Get-Member;
			# Probably missing a few important keywords here...
			$colour = "Green";
			if($s.Status -match "Failure" -or $s.Status -match "Error")
			{
				$colour = "Red";
			}
			Write-Host -ForegroundColor $colour $s.Report $s.Status $s.LastExecuted | Format-Table -AutoSize;
		}
	}
}

After running this script successfully you should be something like below;

powershell ssrs1 Monitoring SSRS Subscriptions with Powershell

 


Tags: , ,
1 Comment »

Using Powershell to increment the computers date

Posted on November 10, 2011, 4:10 pm, by Rhys, under Powershell.

Here’s a little snippet of Powershell code I used recently to test some TSQL that runs according to a two week schedule.

This code will increment the date by one day, up to the maximum specified in $days, sleeping in between for a short while. The date is set back correctly at the end. Far from rocket science but it helps me test various bits of code in minutes instead of 2 weeks!

?View Code POWERSHELL
# Number of days to increment the date by
$days = 14;
 
for($counter=0; $counter -lt $days; $counter++)
{
	Set-Date (Get-Date).AddDays(1);
	# Sleep for 2 minutes so the code gets a chance to run
	Start-Sleep -Seconds 120;
}
 
# Set the date back correctly
Set-Date (Get-Date).AddDays(-$days);

No Comments »

TSQL to generate date lookup table data

Posted on November 10, 2011, 1:20 pm, by Rhys, under T-SQL.

I needed to generate a range of data about dates for a lookup table. There’s an elegant solution using a recursive cte that does the job;

?View Code TSQL
WITH daysCte
(
	d 
)
AS
(
	SELECT CONVERT(DATETIME, '1 January 2011') AS d -- starting date
	UNION ALL
	SELECT DATEADD(D, 1, d)
	FROM daysCte
	WHERE DATEPART(yyyy, d) <= 2012 -- stop year
)
SELECT d, 
	   DATEPART(wk, d) AS week_number,
	   DATENAME(dw, d) AS day_name,
	   DATENAME(m, d) AS month_name,
	   DATENAME(q, d) AS [quarter]
FROM daysCte 
OPTION (MAXRECURSION 800); -- set > number of days you want data for

This will display something looking like below;

d			week_number	day_name	month_name	quarter
2011-01-01 00:00:00.000	1		Saturday	January		1
2011-01-02 00:00:00.000	2		Sunday		January		1
2011-01-03 00:00:00.000	2		Monday		January		1
2011-01-04 00:00:00.000	2		Tuesday		January		1
2011-01-05 00:00:00.000	2		Wednesday	January		1
2011-01-06 00:00:00.000	2		Thursday	January		1
2011-01-07 00:00:00.000	2		Friday		January		1

2 Comments »

MySQL Storage engine benchmarking

Posted on November 7, 2011, 8:49 pm, by Rhys, under DBA, MySQL.

Here’s a stored procedure I use to perform some simple benchmarking of inserts for MySQL. It takes three parameters; p_table_type which should be set to the storage engine you wish to benchmark i.e. ‘MyISAM’, ‘InnoDB’, p_inserts; set this to the number of inserts to perform. p_autocommit; set the autocommit variable (relevant to InnoDB only) to 0 or 1.

?View Code MYSQL
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `table_engine_test`$$
        CREATE DEFINER=`root`@`%`
        PROCEDURE `table_engine_test`(IN p_table_type VARCHAR(20),
                                      IN p_inserts    INT,
                                      IN p_autocommit TINYINT)
                SQL SECURITY INVOKER
        BEGIN
 
                DECLARE sql_string VARCHAR(300);
 
                # Set session autocommit
                SET SESSION autocommit = p_autocommit;
 
                # TABLE TO hold session times
                CREATE TABLE IF NOT EXISTS test_session
                             (
                                          Id         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                                          table_type VARCHAR(20) NOT NULL                   ,
                                          inserts INT NOT NULL,
                                          autocommit TINYINT NOT NULL,
                                          started    DATETIME NULL                          ,
                                          finished   DATETIME NULL
                             );
 
                CREATE TABLE IF NOT EXISTS test_session_inserts
                             (
                                          id              INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
                                          test_session_id INTEGER NOT NULL                           ,
                                          started         DATETIME NOT NULL                          ,
                                          finished        DATETIME NOT NULL
                             );
 
                # clean up ANY existing test TABLE
                DROP TABLE IF EXISTS test_table_type;
 
                SET sql_string = CONCAT('CREATE TABLE test_table_type
					(
						id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
						random INTEGER,
						text1 VARCHAR(10) NOT NULL,
						text2 VARCHAR(10) NOT NULL
					)ENGINE = ', p_table_type);
                # PREPARE SQL AND EXECUTE
                SET @q = sql_string;
                PREPARE stmt FROM @q;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
                # START the test session
                INSERT
                INTO   test_session
                       (
                              table_type,
                              inserts,
                              autocommit,
                              started
                       )
                       VALUES
                       (
                              p_table_type,
                              p_inserts,
                              p_autocommit,
                              NOW()
                       );
 
                SET @id               = LAST_INSERT_ID();
                SET @i                = 0;
                while_loop: WHILE (@i < p_inserts) DO
                # Generate VALUES for insert
                SET @random  = RAND();
                SET @text1   = SUBSTRING(MD5(RAND()), -10);
                SET @text2   = SUBSTRING(MD5(RAND()), -10);
                SET @started = NOW();
                # INSERT the test row
                INSERT
                INTO   test_table_type
                       (
                              random,
                              text1 ,
                              text2
                       )
                       VALUES
                       (
                              @random,
                              @text1 ,
                              @text2
                       );
 
                # record INSERT times
                        INSERT
                        INTO   test_session_inserts
                               (
                                      test_session_id,
                                      started        ,
                                      finished
                               )
                               VALUES
                               (
                                      @id     ,
                                      @started,
                                      NOW()
                               );
 
                        # increment counter
                        SET @i = @i + 1;
                END WHILE while_loop;
                # Finish the session
                UPDATE test_session
                SET    finished = NOW()
                WHERE  id       = @id;
 
END$$
DELIMITER ;

Run your tests like so…

?View Code MYSQL
CALL table_engine_test('MyISAM', 10000, 0);
CALL table_engine_test('InnoDB', 10000, 0);
CALL table_engine_test('InnoDB', 10000, 1);
CALL table_engine_test('MyISAM', 100000, 0);
CALL table_engine_test('InnoDB', 100000, 0);
CALL table_engine_test('InnoDB', 100000, 1);

The test_session contains some summary information about each of the tests;

?View Code MYSQL
SELECT *
FROM test_session;

mysql table engine test thumb MySQL Storage engine benchmarking

Calculate the time taken for each test with the following query;

?View Code MYSQL
SELECT *, TIMEDIFF(finished, started) AS seconds
FROM test_session;

Happy Benchmarking!


Tags: , , ,
No Comments »

Testing a Failover Cluster with Powershell

Posted on November 3, 2011, 11:26 am, by Rhys, under DBA, Powershell.

Just a quick Powershell snippet that I’m going to use to run validation tests on one of my staging Failover Clusters during OOH.

The script below will take some services offline, run the validation tests, before bringing the appropriate cluster groups back online. The report will be saved using the date as the name. To use this you will need to set $cluster appropriately and perhaps customize the cluster groups that are brought offline & online.

?View Code POWERSHELL
Import-Module FailoverClusters;
 
# Set cluster name
$cluster = "Cluster";
 
# Date stamp used for report name
$date = Get-Date -Format "yyyyMMdd";
 
# Take cluster services offline. You may need to customise this
# according to your specific needs
Stop-ClusterGroup -Cluster $cluster -Name "ClusterDtc";
Stop-ClusterGroup -Cluster $cluster -Name "SQL Server (MSSQLSERVER)";
 
# Test Cluster
Test-Cluster -Cluster $cluster -ReportName "$date";
 
# Bring services back online
Start-ClusterGroup -Cluster $cluster -Name "ClusterDtc";
Start-ClusterGroup -Cluster $cluster -Name "SQL Server (MSSQLSERVER)";

The output will look something like below…

Name                       OwnerNode                                      State
----                       ---------                                      -----
ClusterDtc            	   Node1                                 	  Offline
SQL Server (MSSQLSERVER)   Node1                                 	  Offline
WARNING: Cluster Configuration - Validate Resource Status: The test reported
some warnings..
WARNING: Network - Validate IP Configuration: The test reported some warnings..
WARNING:
Test Result:
ClusterConditionallyApproved
Testing has completed successfully. The configuration appears to be suitable
for clustering.  However, you should review the report because it may contain
warnings which you should address to attain the highest availability.
Test report file path: C:\Users\ClusterAdmin\AppData\Local\Temp\20111103.mht
20111103.mht
ClusterDtc            	   Node1                                 	 Online
SQL Server (MSSQLSERVER)   Node1                                 	 Online

Tags: ,
No Comments »

Domain user password expiry with Powershell

Posted on October 13, 2011, 12:14 pm, by Rhys, under Powershell.

I needed to figure out a method for producing alerts when a domain account is approaching the password reset date. Here it is in a few lines of Powershell…

?View Code POWERSHELL
# Set name here
$name = "Rhys Campbell";
$user = Get-ADUser -LDAPFilter "(Name=$name)" -Properties PasswordLastSet;
$days = $(Get-ADDefaultDomainPasswordPolicy).MaxPasswordAge;
$expires = $user.PasswordLastSet + $days;
$days_left = $expires - $(Get-Date);
Write-Host "Password for $name expires on $expires which is in $($days_left.Days) days.";

This will display something along the lines of;

Password for Rhys Campbell expires on 11/18/2011 08:19:36 which is in 35 days.

Tags: ,
No Comments »
« Older Entries