ssh failed logins

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

-- 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

-- 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

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

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

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

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