Nazer Mohamed

ssh failed logins

2016-01-01T22:34:00.004+00:00

To get the list the of failed ssh logins

grep sshd.\*Failed /var/log/auth.log

To extract only the ip address and get a unique list of ips

grep sshd.\*Failed /var/log/auth.log | grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}' | sort | uniq

To block the ip's that have failed the ssh logins using iptables

grep sshd.\*Failed /var/log/auth.log | grep -o '[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}\.[0-9]\{1,3\}' | sort | uniq | while read ip ; do sudo iptables -A INPUT -s $ip -j DROP; done

running this command twice will create duplicates. Working on it to create it as a cron job,

Pending IO

2015-12-17T12:19:00.002+00:00

-- Look at pending I/O requests by file
SELECT DB_NAME(mf.database_id) AS [Database] , mf.physical_name ,r.io_pending , r.io_pending_ms_ticks , r.io_type , fs.num_of_reads , fs.num_of_writesFROM sys.dm_io_pending_io_requests AS r INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_idORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;

IO stalls

2015-12-17T12:18:00.000+00:00

-- Good < 5 msec
-- ideal value < 20 msec

SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(null, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'

Get all table sizes

2014-07-10T12:22:00.002+01:00

SELECT
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages)/ 128.00 AS TotalSpaceMB,
SUM(a.used_pages) / 128.00 AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) / 128.00 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
SUM(a.total_pages) desc

List emails from SSRS Subscription

2014-03-06T16:59:00.003+00:00

Get Email Subscriptions from SSRS

with Cte as
(select CONVERT(XML, ExtensionSettings) x
FROM
ReportServer.dbo.Subscriptions a
inner join
Catalog c on a.Report_OID = c.ItemID
where c.Name = ''
)
select
cast(x.query('/ParameterValues/ParameterValue[1]/Value/text()') as varchar(max)) ToEmails,
cast(x.query('/ParameterValues/ParameterValue[4]/Value/text()') as varchar(max)) Subject
from cte

List the backups for a database

2013-01-07T14:53:00.002+00:00

select backup_start_date, backup_finish_date, physical_device_name from backupset bs
inner join backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
where database_name = ''
order by backup_start_date desc

Script all indexes of a database

2012-10-11T13:11:00.001+01:00

Does not Support advanced options (fill factor, options....)
Supports Include

SELECT
Identity(int, 1,1) Id,
OBJECT_NAME([ixs].[object_id]) 'table_name'
,[ixs].[type] 'index_type'
,[ixs].[type_desc]
,[ixs].[name] 'index_name'
,[ixs].[is_unique]
,[ixs].[fill_factor]
,[ix_col].[key_ordinal]
,[col].[name] 'column_name'
,[ix_col].[is_descending_key]
,[is_included_column]

INTO  #Indexes
FROM
[sys].[tables] tbls
INNER JOIN [sys].[indexes] ixs
ON [tbls].[object_id] = [ixs].[object_id]
AND [tbls].[type] = 'U'
INNER JOIN [sys].[index_columns] ix_col
ON [ixs].[object_id] = [ix_col].[object_id]
AND [ixs].[index_id] = [ix_col].[index_id]
INNER JOIN [sys].[columns] col
ON [col].[object_id] = [ix_col].[object_id]
AND [col].[column_id] = [ix_col].[column_id]
WHERE
[ixs].[type] IN ( 1 , 2 )
ORDER BY
OBJECT_NAME([ixs].[object_id])
,[ixs].[type]
,[ixs].[name]
,[is_included_column]
,[ix_col].[key_ordinal]

Declare @index_name varchar(500)

Declare @tmp table (index_name varchar(500) primary key, isProcessed tinyInt default 0)
insert into @tmp (index_name)
select distinct Index_name from #Indexes

Declare @cols varchar(max), @include varchar(max)
Declare @sql varchar(max)

while (1 = 1)
Begin
select top 1 @index_name = index_name from @tmp where isProcessed = 0
if (@@ROWCOUNT = 0) break;
update @tmp set isProcessed = 2 where index_name = @index_name
select @cols = '', @include = ''

select @cols = @cols + CASE WHEN is_included_column = 0 then
', [' + column_name + '] ' + case when is_descending_key = 0 then 'ASC' else 'DESC' end
else '' end,
@include = @include + CASE WHEN is_included_column = 1 then ', [' + column_name + ']' else '' end
from #indexes where index_name = @index_name order by Id

Select top 1 @sql = 'IF NOT EXISTS (SELECT [Name] FROM [sys].[indexes] WHERE [name] = ''' + @index_name + ''') ' +
'CREATE ' + case when is_unique = '1' THEN 'UNIQUE ' ELSE '' END + type_desc + 'INDEX [' + @index_name + '] ON [' + table_name COLLATE Latin1_General_CI_AS + '] (' + SUBSTRING(@cols, 2, 8000) + ' ) '
+ case when @include <> '' then 'INCLUDE ( ' + SUBSTRING(@include, 2, 8000) + ' )' else '' end
from #Indexes where index_name = @index_name
Print @sql
End

Drop table #Indexes

Query Excel data from Sql server

2012-06-21T14:19:00.002+01:00

1. Download drivers and install in Sql server
http://download.microsoft.com/download/2/4/3/24375141-E08D-4803-AB0E-10F2E3A07AAA/AccessDatabaseEngine_x64.exe

2.
select * from
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\temp\temp.xlsx;',
'SELECT * FROM [Sheet1$]')

3. if user is not sysadmin, locate the provider in following registry path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\

and add

Value name: DisallowAdHocAccess
   Data type:  REG_DWORD
   Radix:      Hex
   Value: 0

Search a string in all tables

2012-06-13T19:23:00.001+01:00

TODO:
1. Handle schema, currently assumes all tables to be in dbo schema
2. Check for table existence before execution

declare @searchString varchar(1000), @sql varchar(max)

select @searchString = '**search string **', @sql = ''

Declare @sqlTable table (id int identity Primary Key, selectsql varchar(7000), isComplete bit)

Insert into @sqlTable
select '

Select top 1 ''' + st.name + ''' _sc12456_TableName, * into #temp
from [' + st.name + '] where [' + sc.name + '] = ''' + @searchString + '''
if (@@rowcount > 0)
select * from #temp
go
', 0
from sys.columns sc inner join sys.tables st
on sc.object_id = st.object_id
inner join sys.types sc_type on sc.user_type_id = sc_type.user_type_id
where sc_type.name in ('nvarchar', 'nchar', 'varchar', 'char') and sc.max_length >= len(@searchString)
order by st.name

set nocount on

Declare @id int
while (1 = 1)
begin
set @id = null
select top 1 @id = id, @sql = selectsql from @sqlTable where isComplete = 0
if (@id is null) break;
exec (@sql)
Update @sqlTable set isComplete = 1 where id = @id
End

List all invalid views

2012-06-11T17:05:00.001+01:00

declare @t TAble (Id int identity(1, 1) Primary key, vName varchar(max), isComplete bit)
declare @badViews Table (vName varchar(max), errNumber int, errMsg varchar(max))
Insert into @t
SELECT [name], 0
FROM sysObjects
WHERE xType = 'V'
AND [name] NOT IN ('syssegments', 'sysconstraints')
AND category=0
ORDER BY crdate

Declare @id int, @tView varchar(max)

set nocount on

while (1 = 1)
Begin
set @id = null
select top 1 @id = Id, @tView = vName from @t
where isComplete = 0

if (@id is null) break;

BEGIN TRY
exec sp_refreshview @tView
Print @tView + ' Valid'
END TRY
BEGIN CATCH
if (ERROR_NUMBER() != 15165) -- Ignore views with Schema binding
Begin
INSERT into @badViews VALUES (@tView, ERROR_NUMBER(), ERROR_MESSAGE())
Print @tView + ' InValid'
End
Else
Print @tView + ' Ignored'
END CATCH

update @t set isComplete = 1 where id = @id
End

select * from @badViews

makeCert

2012-06-08T11:47:00.000+01:00

Source: http://www.source-code.biz/snippets/vbasic/3.htm

How to create a self-signed certificate that can be used to sign MS-Office VBA projects (Excel/Word macros) on multiple computers

Problem: When a certificate is created by using selfcert.exe, it's private key cannot be exported. The export wizard of the Windows certificate console says "the associated private key is marked as not exportable".

Solution version 1: Use makecert.exe with the "-pe" option to create and store the certificate with an exportable private key:

makecert -r -pe -n "CN=Your Name" -b 01/01/2000 -e 01/01/2099 -eku 1.3.6.1.5.5.7.3.3 -ss My
Then you can export the certificate from the Windows certificate store, including the private key.

Note: Old versions of makecert.exe do not support the "-pe" option. The .NET Framework SDK 2.0 and the October 2002 version of the Platform SDK (build 3718.1) contain a new version of makecert.exe (5.131) that supports the "-pe" option.
(The .NET Framework SDKs 1.0 and 1.1 both contain old versions of makecert.exe that do not support the "-pe" option).

Solution version 2: The following commands can be used to create a PFX file (PKCS #12) that contains the a self-signed certificate together with the associated private key:

makecert -r -n "CN=Your Name" -b 01/01/2000 -e 01/01/2099 -eku 1.3.6.1.5.5.7.3.3 -sv selfcert.pvk selfcert.cercert2spc selfcert.cer selfcert.spcpvkimprt -pfx selfcert.spc selfcert.pvk
The last command (pvkimprt -pfx) creates the file selfcert.pfx. This PFX file can then be imported into the Windows certificate store and used for code signing.
(makecert.exe and cert2spc.exe are part of several Microsoft SDKs, e.g. the Platform SDK or the DotNet SDKs, which can be downloaded from microsoft.com).

Author: Christian d'Heureuse (www.source-code.biz, www.inventec.ch/chdh)

Quick way to populate Time Dimension

2012-06-07T15:06:00.002+01:00

CREATE TABLE [dbo].[DimPeriod](
[PK_Date] [datetime] Primary key NOT NULL,
Day_Name varchar(150) not null,
[Year] datetime not null,
[Year_Name] varchar (50) not null,
[Quarter] datetime not null,
[Quarter_Name] [varchar](50) not null,
[Month] datetime not null,
[Month_Name] [varchar](50) not null,
[Day_Of_Year] [int] not null,
[Day_Of_Month] [int] not null
)
go

Declare @stdate datetime, @enddate datetime
select
@stdate = '2006-01-01',
@enddate = '2020-12-31'

set @stdate = DATEADD(dd, -1, @stdate)

Insert into DimPeriod
select dt,
DateName(WEEKDAY, dt) + ', ' + CONVERT(varchar(11), dt, 100),
--CONVERT(varchar(100), dt,
cast(yy as datetime),
'Year ' + cast(YEAR(dt) as CHAR(4)),
dateadd(q, DATEPART(Q, dt) - 1, yy),
'Quater ' + CAST(DATEPART(q, dt) as CHAR(1)),
cast(cast(YEAR(dt) as CHAR(4)) + '-' + cast(DATEPART(Month, dt) as CHAR(2)) + '-1' as datetime),
DATENAME(Month, dt) + ' ' + cast(YEAR(dt) as CHAR(4)),
DATEPART(DAYOFYEAR, dt), DAY(dt)

from (
SELECT TOP (DATEDIFF(DD, @stdate, @enddate))
cast(DatePart(YEAR, DATEADD(dd, row_number() over(order by t1.number), '2005-12-31')) as CHAR(4))
+ '-01-01' yy,
DATEADD(dd, row_number() over(order by t1.number), '2005-12-31') as dt
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
)sq

Quick dump of All table Sizes of a database

2012-03-26T10:13:00.002+01:00

Option #1 - Clear documented and robust

create table tempdb.dbo.AllTableSize058(name varchar(150), rows int, reserved varchar(100), data varchar(100), index_size varchar(100), unused varchar(100))
go
sp_msforeachtable 'Insert into tempdb.dbo.AllTableSize058 Exec sp_spaceused "?"'

Update tempdb.dbo.AllTableSize058 set reserved = replace(reserved, 'KB', ''), data = replace(data, 'KB', ''), index_size = replace(index_size, 'KB', ''), unused = replace(unused, 'KB', '')

select * from tempdb.dbo.AllTableSize058 order by cast(reserved as int) desc

--Drop table tempdb.dbo.AllTableSize058

Option #2

Undocumented way, but efficient and clean. Does not look for internal tables (service broker queues etc)

select Name, rows, reserved, used, unused, data, used - data index_size from
(
select a.object_id, a.name,
Sum(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END) rows,
Sum(reserved_page_count * 8) reserved,
Sum(used_page_count * 8) used,
Sum((reserved_page_count - used_page_count) * 8) unUsed,
Sum(CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END * 8) data
from sys.tables a inner join sys.dm_db_partition_stats b
on a.object_id = b.object_id
Group by a.object_id, a.name
) a
order by reserved desc

Difference in Identity Functions

2012-01-27T16:45:00.002+00:00

Small script to demo the usage of different Identites

Create table testIdentity (Id int Identity Primary Key, name varchar(100))
go

Create Table testIdentityLog (Id int Identity(1000, 1) Primary Key, dt dateTime)
go

Create Trigger trigtestIdentity on testIdentity
after insert
as
Insert into testIdentityLog Values (getdate())

Insert into testIdentity Values ('One')
select @@IDENTITY id, SCOPE_IDENTITY() ActualId

Insert into testIdentityLog Values (getdate())
select @@IDENTITY id, SCOPE_IDENTITY() ActualId, IDENT_CURRENT('testIdentity'), IDENT_CURRENT('testIdentityLog')

Asterisk GUI Not found

2011-03-26T02:30:00.001+00:00

After installing Asterisk GUI 2.0 I was getting the "http 404 not found" error.

remove the /usr/share/asterisk/'http-static' directory
ln -s /var/lib/asterisk/staic-http/ /usr/share/asterisk/

PGP Chat Google Talk (Gtalk)

2010-07-23T18:02:00.005+01:00

-- No Longer works - due to change in Gtalk Api change. --

Wanted to create this software for a very long time. Atlast, it is done.

Copied the basic application from codeProject (http://www.codeproject.com/KB/gadgets/googletalk.aspx)

Then added the functionality to get Secure.

1. As soon as a chat session is opened with the operand, a RSA key pair is generated. The key pair is kept until the applicaiton quits.

2. Sends the public key and expects one from the operand

3. If key recieved, then secure Chat is turned on

4. if not, the chat session will be unsecure

5. To check, if it is really secure, you can open Gtalk, and monitor. Gtalk allows multiple sessions. So you can see encrypted messages passisng arround.

Download it from:
https://docs.google.com/file/d/0By6OGZauI7FvZjllOTJkMDItODQ1OC00OGZhLWJiMGQtN2M1Yjk5NzI1ZmU5/edit?authkey=CLfFv98B

Source:
http://dl.dropbox.com/u/1784903/Gtalk_Source.zip

Shortest route, non-weighted and bidirectional

2010-06-09T17:38:00.001+01:00

create table Friendship(id int identity(1,1) primary key, user1ID int, user2ID int)

Insert into Friendship
select 1, 2 Union all
select 3, 4 Union all
select 2, 5 Union all
select 4, 5 Union all
select 6, 7 Union all
select 8, 9 Union all
select 1, 5 Union all
select 6, 9 Union all
select 3, 7 Union all
select 7, 6

Exec dbo.usp_ShortestRoute 9, 1, 8

Create Procedure usp_ShortestRoute (@StartNode int, @EndNode int, @depth int = null)
as
Begin

Declare @routes Table (RouteMap varchar(150), EndNode int, depth Int)
Declare @isRouteFound bit, @rows int, @msg varchar(150), @activedepth int

Select @isRouteFound = 0, @activedepth = 1

if (@StartNode = @EndNode)
select @msg = 'Start and End node are same'
else if not exists (select top 1 Id from Friendship Where user1ID = @StartNode or user2ID = @StartNode)
or not exists (select top 1 Id from Friendship Where user1id = @endNode or user2ID = @EndNode)
select @msg = 'No possible Route'
else
Begin
Insert into @routes
select CAST(user1ID as varchar(3)) + ' - ' + CAST(user2ID as varchar(3)), user2ID, 1 from Friendship where user1ID = @StartNode
Union
select CAST(user2ID as varchar(3)) + ' - ' + CAST(user1ID as varchar(3)), user1ID, 1 from Friendship where user2ID = @StartNode

set @rows = @@ROWCOUNT

While (1 = 1)
Begin
if (@rows = 0)
Begin
Select @isRouteFound = 0, @msg = 'Passed the end of routes. Route Incomplete'
break;
End

if exists (select top 1 * from @routes where EndNode = @EndNode)
Begin
select @isRouteFound = 1, @msg = 'Route Found'
break;
End

if (@activedepth >= isNull(@depth, @activedepth))
Begin
set @msg = 'Depth Reached - Route not found'
break;
End

/*
1. Forget lower depth routes
2. Make sure we dont loop the same route
2.1 Node will either be in the begining of the route or in the mid.
2.2 The node that is at the end the recent node, so we should not check that
3. Insert records with active depth + 1
*/
Insert into @routes
select RouteMap + ' - ' + CAST(b.user2ID as varchar(3)), b.user2ID, @activedepth + 1 from @routes a left join Friendship b on a.EndNode = b.user1ID
where depth = @activedepth and RouteMap not like '%- ' + CAST(b.user2ID as varchar(3)) + ' -%' and RouteMap not like CAST(b.user2ID as varchar(3)) + ' -%'
Union
select RouteMap + ' - ' + CAST(b.user1ID as varchar(3)), b.user1ID, @activedepth + 1 from @routes a left join Friendship b on a.EndNode = b.user2ID
where depth = @activedepth and RouteMap not like '%- ' + CAST(b.user1ID as varchar(3)) + ' -%' and RouteMap not like CAST(b.user1ID as varchar(3)) + ' -%'

select @rows = @@ROWCOUNT, @activedepth = @activedepth + 1

--select *from @routes
End
End

--select * from @routes
--select @isRouteFound Result, @depth Depth

if (@isRouteFound = 1)
select RouteMap, depth, @msg Result from @routes where EndNode = @EndNode
else
select null RouteMap, -1 depth, @msg Result
End

Find TimeZone of server

2010-04-19T11:13:00.000+01:00

Declare @temp Table (Cmdoutput varchar(max))

Insert into @temp
Exec xp_cmdshell 'reg query HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\TimeZoneInformation /v StandardName'
select Ltrim(REPLACE(CmdOutput, 'StandardName' + CHAR(9) + 'REG_SZ', '')) from @temp where Cmdoutput like '%standardName%'

send a CSV attachment by Email (ANSI attachment)

2010-04-16T11:39:00.001+01:00

Declare @filename varchar(100), @cmd varchar(max)
set @filename = 'output-' + convert(varchar(10), getdate(), 121) + '.csv'

set nocount off
set @cmd = 'exec xp_cmdShell ''bcp "query" queryout ' + @fileName + ' -S server -U user -P pass -f"bcp.fmt"'', no_output'
Exec (@cmd)

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'email@domain.com',
@body='Hi,
this is email body
',
@subject ='this is email sub',
@profile_name ='profile',
@file_attachments= @filename

Excel Destination - using Script

2010-04-13T08:47:00.003+01:00

Recently faced a problem with the Excel Destination Data Flow component. It does not write formulas properly.
So developed this script that will use a File Connection and write directly to Excel.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Reflection;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
string filename;
Microsoft.Office.Interop.Excel.Application excelApp;
Microsoft.Office.Interop.Excel.Workbook wbook;
Microsoft.Office.Interop.Excel.Worksheet wsht;

int row;

public override void PreExecute()
{
row = 1;
filename = (string)this.Connections.OutputFileConnection.AcquireConnection(null);
excelApp = new Microsoft.Office.Interop.Excel.Application();
wbook = excelApp.Workbooks.Add(Missing.Value);
wsht = (Microsoft.Office.Interop.Excel.Worksheet)wbook.Worksheets.get_Item(1);

base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}

public override void PostExecute()
{

//wbook.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
wbook.Close(true, filename, false);
excelApp.Quit();
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
wsht.Cells[row, "A"] = Row.ID;
wsht.Cells[row, "B"] = Row.CountryName;
//wsht.Cells[row, "C"] = "=INDIRECT(ADDRESS(ROW(),1, 4)) * 2";
row++;
}
}

SQL XML Modify

2010-04-09T12:17:00.001+01:00

Insert / Delete inside XML

Declare @xml xml
SET @xml = 'Test/Test'

set @xml.modify('insert attribute txt {/Report/text()} into (/Report/Folder)[1]')
set @xml.modify('delete /Report/text()')
select @xml

Configure Network Trace in your application

2010-04-09T10:33:00.006+01:00

<system.diagnostics>

</listeners>

</source>

</listeners>

</source>

</listeners>

</source>

</sources>

</switches>

<add name="System.Net"

type="System.Diagnostics.TextWriterTraceListener"

initializeData="network.log"

</sharedListeners>

</system.diagnostics>

</configuration>

Registering additional assemblies in SQL CLR

2010-04-06T16:48:00.003+01:00

By default not all assemblies are included for the CLR to use with. To add other assemblies,

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON

CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE

Extract Numbers / Text from String

2010-03-23T12:04:00.003+00:00

Create Function Extract(@NumStr Varchar(1000), @option int = 0)
returns Varchar(1000)
as
Begin
Declare @tmpStr Varchar(1000), @c int, @n int
Declare @Result Varchar(1000)
select @Result = '', @n = 1
While @NumStr != ''
Begin
select @c = CHARINDEX(' ', @NumStr, @n)
if (@c = 0) Break;
select @tmpStr = SUBSTRING(@NumStr, @n, @c)

if (@option = 0)
Begin
if (@tmpStr like '%[0-9]%')
set @Result = @Result + @tmpStr
End
else
Begin
if (@tmpStr Not like '%[0-9]%')
set @Result = @Result + @tmpStr
End
select @NumStr = SUBSTRING(@NumStr, @c + 1, 1000)
End
if (@option = 0)
Begin
if (@numStr like '%[0-9]%')
set @Result = @Result + @numStr
End
else
Begin
if (@numStr Not like '%[0-9]%')
set @Result = @Result + @numStr
End
Return (@Result);
End

go

-- Test
select dbo.Extract('This string contains 5 words and3 now 9 words. And also 9ahs asd', 1)

Service Availability Calculation

2009-12-23T12:28:00.005+00:00

Service Availability % = [(TM – SM – DM)/(TM – SM)] * 100
Where:
TM = Total Minutes in the calendar month (i.e.: #days*24hrs*60mins)
SM = Scheduled Maintenance Time (in minutes)
DM = Unscheduled Downtime.

Eg:
Scheduled Maintenance - 10 mintues in a month
Downtime: 5 minutes in a month
(43200 - 10 - 5) / (43200 - 10) = 99.98%