SQL Saturday 269 Exeter

Not quite sure where the time is going this year, but coming very shortly is SQL Saturday 269 in Exeter and it is well worth attending.

http://www.sqlsaturday.com/269/eventhome.aspx

This event will be held on 21st and 22nd March 2014 at

Jurys Inn Hotel Exeter,
Western Way,
Exeter,
Devon,
EX1 2DB,
United Kingdom.
(Location details – http://www.sqlsaturday.com/269/location.aspx)

Posted in Blog, SQL Server | Leave a comment

SQL Server Login password hash

In this article we will look at how SQL Server stores passwords and how we can go about working them out.

As a developer/administrator you are probably accessing SQL Server via a windows login, however the other option is when the instance is changed into mixed mode allowing SQL Logins. These logins are created within the master database and shown in sys.server_principals.

There is additional information in sys.sql_logins which itself inherits from sys.server_principals.  The 3 additional columns are called is_policy_checked,is_expiration_checked and password_hash.

If you take a hash of a known piece of text using the HASHBYTES function, and then compare it with an identical password you have entered when creating a sql login you will notice that they do not match. (Looking at the password_hash columns in sys.sql_logins)

The difference is that an addition piece of data called a salt has been added to the process.

x = Hash(PlainText + Salt) instead of x = Hash(PlainText)

SELECT HASHBYTES('SHA2_512',CAST(N'JonGurgul' AS VARBINARY(MAX)))
--0x605334B588BA046B9EA3FD2F7C501ABD549D2F698A57E78EAE525553F72CBA0B710FBD928FB1AE05FD6FAEECB9A957C2EEF0323EA6BC75FE92A60C7D4FAA7AD2

SELECT HASHBYTES('SHA2_512',CAST(N'JonGurgul' AS VARBINARY(MAX))+0xF1202F8A)
--0x0C9748812054A27F0209CD5DCA57EFE33C496A112BA6EBB048B91D35FA784F385A1625228777164719565A02612255B83F6BD37DE096DEBE74AF9B936BB8C02D

This helps to add additional security, however SQL server stores the salt as part of the hash. If you look at the password_hash column you will have something that looks like:

0x0200F1202F8A0C9748812054A27F0209CD5DCA57EFE33C496A112BA6EBB048B91D35FA784F385A1625228777164719565A02612255B83F6BD37DE096DEBE74AF9B936BB8C02D

Now you will see that the bold 4 bytes match the salt that I had used in the previous example, and the red data is the actual hash of the plain text.

Cracking/Hacking/Guessing SQL Login passwords

Now for an example and with a bit of guess work lets see if I can work out any of your passwords. Note that the hash algorithm changes from SHA1 to SHA2-512 from SQL 2012.

--http://jongurgul.com/blog/sql-server-login-password-hash
USE [tempdb]
GO
IF NOT EXISTS(SELECT * FROM [tempdb].sys.tables WHERE name = 'WordList')
BEGIN
 CREATE TABLE [dbo].[WordList]([Plain] NVARCHAR(MAX))

 --USERNAME//PASSWORD COMBOS
 INSERT INTO [WordList]([Plain])
 SELECT [name] FROM sys.sql_logins
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3') FROM sys.sql_logins
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3')+'.' FROM sys.sql_logins --example added character
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3')+'!' FROM sys.sql_logins --example added character

 --No Comment
 INSERT INTO [WordList]([Plain]) VALUES (N'')
 INSERT INTO [WordList]([Plain]) VALUES (N'password')
 INSERT INTO [WordList]([Plain]) VALUES (N'sa')
 INSERT INTO [WordList]([Plain]) VALUES (N'dev')
 INSERT INTO [WordList]([Plain]) VALUES (N'test')
END
DECLARE @Algorithm VARCHAR(10)
SET @Algorithm = CASE WHEN @@MICROSOFTVERSION/0x01000000 = 11 THEN 'SHA2_512' ELSE 'SHA1' END

SELECT
 [name]
,[password_hash]
,SUBSTRING([password_hash],3,4) [Salt]
,SUBSTRING([password_hash],7,(LEN([password_hash])-6)) [Hash]
,HASHBYTES(@Algorithm,CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING([password_hash],3,4)) [ComputedHash]
,w.[Plain]
FROM sys.sql_logins
INNER JOIN [tempdb].[dbo].[WordList] w
ON SUBSTRING([password_hash],7,(LEN([password_hash])-6)) = HASHBYTES(@Algorithm,CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING([password_hash],3,4))

IF EXISTS(SELECT * FROM [tempdb].sys.tables WHERE name = 'WordList')
BEGIN
 DROP TABLE [tempdb].[dbo].[WordList]
END
GO
SELECT
[name]
,[password_hash]
,SUBSTRING([password_hash],3,4) [Salt]
,SUBSTRING([password_hash],7,(LEN([password_hash])-6)) [Hash]
FROM sys.sql_logins
GO

I have used a table to store some plain text example passwords, but you can obviously add your own guesses to try to determine the obvious ones.

Hopefully the above simple code has not worked out any of your passwords, but if it has I would suggest taking the opportunity to change them.

Another option instead of manually extracting the salt and rehashing is to use the function PWDCOMPARE which will do the work for you.

SELECT [name],[password_hash]
FROM sys.sql_logins
WHERE PWDCOMPARE(N'JonGurgul',[password_hash]) = 1
Posted in SQL Server | Tagged , | Leave a comment

Running as local system to allow administrators in to SQL Server

In cases when there are no provisioned accounts in a SQL Server instance which are known or working, then there is an option instead to run as nt authority \ system (local system). Note that in SQL 2012 local system no longer has sa and you will have to instead start SQL Server in single user mode. http://technet.microsoft.com/en-us/library/dd207004.aspx

Method 1

This method basically involves creating a new windows service which starts an interactive command prompt.

1. sc create _IC binpath= “cmd /K start” type= interact type= own

[SC] CreateService SUCCESS
WARNING: The service _IC is configured as interactive whose support is being deprecated. The service may not function properly.

2. sc start _IC

[SC] StartService FAILED 1053:
The service did not respond to the start or control request in a timely fashion.

3. Check for the spawned dialog and click view the message

system_prompt

4. This will then lead to a command prompt running as local system. When finished close all your opened apps.

system_cmd_prompt

5. The final tidy up step is to delete the elevator sc delete _IC

Method 2

You need psexec from http://technet.microsoft.com/en-gb/sysinternals/bb897553.aspx

1. psexec –s –i cmd

Posted in SQL Server | Tagged , , | Leave a comment

Simple Table and Index Breakdown With Buffered

This script builds on the Simple Table and Index script to further show the buffered metrics. This script will give you a simple breakdown of your tables and indexes, which is shown with schema(s),table(s),index type(s),row count(s) as well as the partition(s) information.

This is a reduced version of the “Detailed Table and Index Breakdown With Buffered” which includes more information such as storage size, allocation type and filegroup.

--http://jongurgul.com/blog/simple-table-index-breakdown-buffered/
SELECT
 SCHEMA_NAME(ao.[schema_id]) [SchemaName]
,ao.[name] [ObjectName]
,i.[name] [IndexName]
,i.[type_desc] [IndexType]
,p.[partition_number] [PartitionNumber]
--,p.[data_compression_desc] [Compression]
,ds.[name] [PartitionName]
,p.[rows] [NumberOfRows]
,prv.[value] [LowerBoundaryValue]
,prv2.[value] [UpperBoundaryValue]
,b.[DataPagesBuffered]
,CONVERT(DECIMAL (15,3),b.[DataPagesBuffered]*0.0078125) [DataBuffered_MiB]
,b.[IndexPagesBuffered]
,CONVERT(DECIMAL (15,3),b.[IndexPagesBuffered]*0.0078125) [IndexBuffered_MiB]
,b.[PagesBuffered]
--,b.[numa_node] [NumaNode]
FROM sys.partition_functions pf
INNER JOIN sys.partition_schemes ps ON pf.[function_id] = ps.[function_id]
RIGHT OUTER JOIN sys.partitions p
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.all_objects ao ON i.[object_id] = ao.[object_id] ON ps.[data_space_id] = ds.[data_space_id]
LEFT OUTER JOIN sys.partition_range_values prv ON ps.[function_id] = prv.[function_id] AND p.[partition_number] - 1 = prv.[boundary_id]
LEFT OUTER JOIN sys.partition_range_values prv2 ON ps.[function_id] = prv2.[function_id] AND prv2.[boundary_id] = p.[partition_number]
	INNER JOIN sys.allocation_units au ON au.[container_id] = p.[partition_id]
	INNER JOIN
	(
	SELECT
	[allocation_unit_id], SUM(CASE WHEN [page_type] = 'INDEX_PAGE' THEN 1 ELSE 0 END) [IndexPagesBuffered]
	,SUM(CASE WHEN [page_type] = 'DATA_PAGE' THEN 1 ELSE 0 END) [DataPagesBuffered]
	,COUNT_BIG(*) [PagesBuffered]
	--,[numa_node]
	FROM sys.dm_os_buffer_descriptors
	WHERE [database_id] = DB_ID()
	GROUP BY [allocation_unit_id]--,[numa_node]
	) b
	ON au.[allocation_unit_id] = b.[allocation_unit_id]
WHERE ao.[is_ms_shipped] = 0 
--AND SCHEMA_NAME(ao.[schema_id]) ='dbo' 
--AND ao.[name] LIKE '%%' 
ORDER BY SCHEMA_NAME(ao.[schema_id]),ao.[name]

http://gallery.technet.microsoft.com/scriptcenter/Simple-Table-and-Index-4f040589

Posted in SQL Server | Tagged , , , , | Leave a comment

Detailed Table Index Breakdown With Buffered

This script builds on the Detailed Table and Index script to further show the buffered metrics. This script will give you a comprehensive breakdown of your tables and indexes, which is shown with schema(s),table(s),index type(s),index size(s),row count(s) as well as the filegroup(s) and partition(s) information.

Also in this script I have included two columns called TippingPoint* which relate to the rough bounderies where a nonclustered index is no longer selective enough. I have applied the formula to the clustered index for ease. For more detail on an indexes tipping point please see Kimberly Tripp’s article.

http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx

--http://jongurgul.com/blog/detailed-table-index-breakdown-buffered/
SELECT
 DB_NAME() [DatabaseName]
,ao.[object_id] [ObjectID]
,SCHEMA_NAME(ao.[schema_id]) [SchemaName]
,ao.[name] [ObjectName]
,ao.[is_ms_shipped] [IsSystemObject]
,i.[index_id] [IndexID]
,i.[name] [IndexName]
,i.[type_desc] [IndexType]
,au.[type_desc] [AllocationUnitType]
,p.[partition_number] [PartitionNumber]
,ds.[type] [IsPartition]
--,p.[data_compression_desc] [Compression]
,ds.[name] [PartitionName]
,fg.[name] [FileGroupName]
,p.[rows] [NumberOfRows]
,CASE 
 WHEN pf.[boundary_value_on_right] = 1 AND ds.[type] = 'PS' THEN 'RIGHT'
 WHEN pf.[boundary_value_on_right] IS NULL AND ds.[type] = 'PS' THEN 'LEFT'
 ELSE NULL
 END [Range]
,prv.[value] [LowerBoundaryValue]
,prv2.[value] [UpperBoundaryValue]
,CONVERT(DECIMAL (15,3),(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND p.[rows] >0 THEN p.[rows]/au.[data_pages] ELSE 0 END)) [RowsPerPage]
,(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND i.[type_desc] = 'CLUSTERED' THEN au.[used_pages]*0.20 ELSE NULL END) [TippingPointLower_Rows]
,(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND i.[type_desc] = 'CLUSTERED' THEN au.[used_pages]*0.30 ELSE NULL END) [TippingPointUpper_Rows]
,au.[used_pages][UsedPages]
,CONVERT(DECIMAL (15,3),(CASE WHEN au.[type] <> 1 THEN au.[used_pages] WHEN p.[index_id] < 2 THEN au.[data_pages] ELSE 0 END)*0.0078125) [DataUsedSpace_MiB]
,CONVERT(DECIMAL (15,3),(au.[used_pages]-(CASE WHEN au.[type] <> 1 THEN au.[used_pages] WHEN p.[index_id] < 2 THEN au.[data_pages] ELSE 0 END))*0.0078125) [IndexUsedSpace_MiB]
,au.[data_pages] [DataPages] --maybe better called leaf pages? page level 0 could be data pages or in nc index pages. it counts In-row data,LOB data and Row-overflow data.
,b.[DataPagesBuffered]
,CONVERT(DECIMAL (15,3),b.[DataPagesBuffered]*0.0078125) [DataBuffered_MiB]
,b.[IndexPagesBuffered]
,CONVERT(DECIMAL (15,3),b.[IndexPagesBuffered]*0.0078125) [IndexBuffered_MiB]
,b.[PagesBuffered]
--,b.[numa_node] [NumaNode]
FROM        
sys.partition_functions pf
INNER JOIN sys.partition_schemes ps ON pf.[function_id] = ps.[function_id]
RIGHT OUTER JOIN sys.partitions p
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id]
INNER JOIN sys.allocation_units au ON au.[container_id] = p.[partition_id]
INNER JOIN sys.filegroups fg ON au.[data_space_id] = fg.[data_space_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.all_objects ao ON i.[object_id] = ao.[object_id] ON ps.[data_space_id] = ds.[data_space_id]
LEFT OUTER JOIN sys.partition_range_values prv ON ps.[function_id] = prv.[function_id] AND p.[partition_number] - 1 = prv.[boundary_id]
LEFT OUTER JOIN sys.partition_range_values prv2 ON ps.[function_id] = prv2.[function_id] AND prv2.[boundary_id] = p.[partition_number]
	INNER JOIN
	(
	SELECT
	[allocation_unit_id], SUM(CASE WHEN [page_type] = 'INDEX_PAGE' THEN 1 ELSE 0 END) [IndexPagesBuffered]
	,SUM(CASE WHEN [page_type] = 'DATA_PAGE' THEN 1 ELSE 0 END) [DataPagesBuffered]
	,COUNT_BIG(*) [PagesBuffered]
	--,[numa_node]
	FROM sys.dm_os_buffer_descriptors
	WHERE [database_id] = DB_ID()
	GROUP BY [allocation_unit_id]--,[numa_node]
	) b
	ON au.[allocation_unit_id] = b.[allocation_unit_id]
WHERE
ao.[is_ms_shipped] = 0
AND au.[type_desc] = 'IN_ROW_DATA'
--AND SCHEMA_NAME(ao.[schema_id]) ='dbo'
--AND ao.[name] LIKE '%%'
ORDER BY SCHEMA_NAME(ao.[schema_id]),ao.[name]

http://gallery.technet.microsoft.com/scriptcenter/Detailed-Table-and-Index-1dd12259

Posted in SQL Server | Tagged , , , , , | Leave a comment

Quick data wipe via truncate

“How to remove all data from a database?” is something that I have often seen asked on the forums. How do you remove all the data quickly? First you try with delete and find that this is too slow and the transaction log has to record all the changes, so you use truncate which is great at removing the data and with less logging. However the problem with this is that you cannot use truncate when there are constraints.
On a side note I often say that it is best to have a scripted empty version of your database rather that remove data.

You will need SMO installed for this script to work.

This script will produce the commands to:
1. Drop Constraints
2. Truncate All Tables
3. Recreate Constraints
It is not fully automated for safety and relies on the end user checking that the scripts perform the tasks they want!
Replace (local) and AdventureWorks with the sql instance/database you want to script the changes for, and as long as you have not got any schema bound objects then you will have a script to accomplish the task.

!!!PLEASE CHECK!!! THIS WILL PRODUCE SCRIPTS THAT DELETE DATA & DROP/CREATE CONSTRAINTS!!!

0. Backup taken!!!
1. Correct Server/Instance selected?
2. Correct Database selected?

I take no Liability for any issues relating to this script, but appreciate any feedback.

#http://jongurgul.com/blog/quick-data-wipe-truncate/
[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
[Microsoft.SqlServer.Management.Smo.ScriptingOptions] $ScriptingOptions = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions";
$ScriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version90; #Version90, Version100, Version105
$ScriptingOptions.ContinueScriptingOnError = $false; #ignore scripts errors, advisable to keep set to $false
$SqlInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)"; 
$SqlInstance.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject");
$SqlInstance.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject");
$Database = $SqlInstance.Databases["AdventureWorks"];
$DropFKScript = $null;
$TruncateScript = $null;
$CreateFKScript = $null;
$SchemaBound = $($Database.Views|Where-Object {!$_.IsSystemObject -and $_.IsSchemaBound}|%{$_});
cls;
If ($SchemaBound -eq $null){
	$Database.Tables|%{$_.ForeignKeys}|%{
	$ScriptingOptions.DriForeignKeys = $true;
	$ScriptingOptions.SchemaQualifyForeignKeysReferences = $true;
	$ScriptingOptions.ScriptDrops = $false;
	$CreateFKScript += "{0}`r`nGO`r`n" -f $($_.Script($ScriptingOptions));
	$ScriptingOptions.ScriptDrops = $true;
	$DropFKScript += "{0}`r`nGO`r`n" -f $($_.Script($ScriptingOptions));
	}
	$TruncateScript += $Database.Tables|Where-Object {!$_.IsSystemObject}|%{"TRUNCATE TABLE [{0}].[{1}]" -f ($_.Schema,$_.Name)};
}
Else{
	Write-Host "Schema Bound Objects Present:";
	$SchemaBound|%{"[{0}].[{1}]" -f ($_.Schema,$_.Name)};
}
$DropFKScript;
$TruncateScript;
$CreateFKScript;
Posted in Powershell, SQL Server | Tagged , , , , | Leave a comment

Leap Year

A leap year can be calculated using a formula (Please see Microsoft article), however there is no real need to calculate it this way. All that is needed in sql is to take one day away from March 1st; with the resultant being either the 28th, or in the case of a leap year the 29th. Finally by substracting 28 this is simplified to 1 or 0.

Method to determine whether a year is a leap year
http://support.microsoft.com/kb/214019

--http://jongurgul.com/blog/leap-year/
DECLARE @Leap TABLE ([yyyy] INT)
INSERT INTO @Leap VALUES (YEAR(GETDATE()))
INSERT INTO @Leap VALUES (YEAR(DATEADD(yyyy,1,GETDATE())))
INSERT INTO @Leap VALUES (YEAR(DATEADD(yyyy,2,GETDATE())))
INSERT INTO @Leap VALUES (YEAR(DATEADD(yyyy,3,GETDATE())))
SELECT 
 [yyyy]
,DATEPART(d,DATEADD(d,-1,CAST(CAST([yyyy]*10000+0301 AS CHAR(8)) AS DATETIME)))-28 [isLeap] 
FROM @Leap
Posted in SQL Server | Tagged , , | Leave a comment

Last Backup Occurred

This script will show when the last backup occurred as well as the backup type for each database. This covers all the backup types available and pivots the output into a simple and easily readable format.
It will state the reason; if any, for log reuse wait and the database recovery model in use.
Depending on your requirements you may also wish to alter the script to exclude copy only backups. This can be done by adding the following:

SELECT [database_name],[backup_finish_date],[type]
FROM msdb..backupset
WHERE [is_copy_only] <> 1

--http://jongurgul.com/blog/last-backup-occurred/
SELECT
 QUOTENAME(d.[name]) [DatabaseName]
,SUSER_SNAME(d.[owner_sid]) [DatabaseOwner]
,d.[compatibility_level] [Compatibility]
,d.[collation_name] [CollationName]
,d.[is_read_only] [IsReadOnly]
,d.[is_auto_close_on] [IsAutoClose]
,d.[is_auto_shrink_on] [IsAutoShrink]
,d.[recovery_model_desc] [RecoveryModel]
,d.[page_verify_option_desc] [PageVerify]
,d.[state_desc] [State]
,d.[log_reuse_wait_desc] [LogReuse]
,pivbus.[D] [Database]
,pivbus.[I] [DifferentialDatabase]
,pivbus.[L] [Log]
,pivbus.[F] [FileOrFilegroup]
,pivbus.[G] [DifferentialFile]
,pivbus.[P] [Partial]
,pivbus.[Q] [DifferentialPartial]
FROM sys.databases d
LEFT OUTER JOIN
(
SELECT
piv.[database_name],[D],[I],[L],[F],[G],[P],[Q]
FROM
(
SELECT [database_name],[backup_finish_date],[type]
FROM msdb..backupset
) bus PIVOT
(MAX([backup_finish_date]) FOR [type] IN ([D],[I],[L],[F],[G],[P],[Q])) piv
) pivbus ON d.[name] = pivbus.[database_name]
Posted in SQL Server | Tagged , | Leave a comment

Get-VolumeFreeSpace

Here is an example function to return free space remaining on a volume with associated details. Updated 27/04/2012 to fix mount points.

Usage Examples:
Get-VolumeFreeSpace|ft
Get-VolumeFreeSpace|Select ComputerName,Name,MountPoint,FreeSpaceGiB|ft
Get-VolumeFreeSpace|Where-Object {!$_.SystemVolume}|Select ComputerName,Name,MountPoint,FreeSpaceGiB|ft
Get-VolumeFreeSpace “ComputerA”,”ComputerB” |ft

#http://jongurgul.com/blog/get-volumefreespace/
Function Get-VolumeFreeSpace{ 
    Param([String[]]$Computers) 
    If (!$Computers) {$Computers = $ENV:ComputerName} 
    $Base = New-Object PSObject; 
    $Base | Add-Member Noteproperty ComputerName -Value $Null; 
    $Base | Add-Member Noteproperty DeviceID -Value $Null; 
    $Base | Add-Member Noteproperty SystemVolume -Value $Null; 
    $Base | Add-Member Noteproperty DriveType -Value $Null; 
    $Base | Add-Member Noteproperty Name -Value $Null; 
    $Base | Add-Member Noteproperty MountPoint -Value $Null; 
    $Base | Add-Member Noteproperty FreeSpaceGiB -Value $Null; 
    $Results =  New-Object System.Collections.Generic.List[System.Object]; 
 
    ForEach ($Computer in $Computers){ 
        $Volume = Get-WmiObject -Class "Win32_Volume" -ComputerName $Computer; 
        $MountPoint = Get-WmiObject -Class "Win32_MountPoint" -ComputerName $Computer | Select @{Name="DeviceID";Expression={$_.Volume.ToString().Substring($_.Volume.ToString().IndexOf("`"")).Replace("`"","").Replace("\\","\")}},@{Name="MountPoint";Expression={$_.Directory.ToString().Substring($_.Volume.ToString().IndexOf("`"")).Replace("`"","").Replace("\\","\")}};   
        [String[]]$Mounts = $MountPoint|%{$_.DeviceID} 
        $Volume |%{ 
        $Entry = $Base | Select-Object * 
        $Entry.ComputerName = $Computer; 
        $Entry.DeviceID =  $_.DeviceID; 
        $Entry.SystemVolume =  $_.SystemVolume; 
        $Entry.DriveType = $_.DriveType; 
        $Entry.Name = $_.Name; 
        $Entry.FreeSpaceGiB = [Math]::Round($_.FreeSpace/1GB,3); 
        $DeviceID = $_.DeviceID;         
        $MountPoint| Where-Object{$DeviceID -contains $_.DeviceID}|%{
        $Local = $Entry | Select-Object *
        $Local.MountPoint = $_.MountPoint;
        [Void]$Results.Add($Local);
        }; 
        $_|Where-Object{$Mounts -notcontains $_.DeviceID}|%{[Void]$Results.Add($Entry);}; 
        } 
    }     
    $Results 
}
Posted in Powershell, SQL Server | Tagged , , | Leave a comment

Dell Service Tag

Here is an example of how to call the Dell Service Tag GetAssetInformation web service so that you can retrieve information about your server. It simply returns the results in an xml format, however if anyone has any ideas or requests please feel free to ask.

updated:
http://xserv.dell.com/jigsawwebservices/AssetService.asmx?WSDL changed to http://xserv.dell.com/services/AssetService.asmx?WSDL

Usage Examples:
Get-DellAssetInformation
Get-DellAssetInformation “MyTag”

Function Get-DellAssetInformation{
Param([String]$ServiceTag = $(Get-WmiObject -Class "Win32_Bios").SerialNumber);
	Try{
	$AssetService = New-WebServiceProxy -Uri "http://xserv.dell.com/services/AssetService.asmx?WSDL";
	$ApplicationName = "AssetService";
	$Guid = [Guid]::NewGuid();
	$Asset = $AssetService.GetAssetInformation($Guid,$ApplicationName,$ServiceTag);
	$Writer = New-Object "System.IO.StringWriter";
	$XmlSerializer = New-Object System.Xml.Serialization.XmlSerializer($Asset.GetType());
	$XmlSerializer.Serialize($Writer,$Asset);
	[String]$Result = $Writer.ToString();
	$Writer.Flush();
	$Writer.Close();
	Return $Result;
	}
	Catch{
	Write-Host $($_.Exception.Message);	
	}
}
Posted in Powershell | Tagged , , | Leave a comment