ghotz

$MyIp = 'xxx.xxx.xxx.xxx';

$Log = Get-Content "C:\home\logfiles\http\rawlogs\*.log" | Select -Skip 2 | ConvertFrom-Csv -Delimiter " " -Header 'date','time','s-sitename','cs-method','cs-uri-stem','cs-uri-query','s-port,cs-username','c-ip','cs(User-Agent)','cs(Cookie)','cs(Referer)','cs-host','sc-status','sc-substatus','sc-win32-status','sc-bytes','cs-bytes','time-taken';

$Log | ? { $_.'cs-method' -eq 'POST' -and $_.'cs(User-Agent)' -ne $MyIp } | Group-object -Property 'cs(User-Agent)' | % {[pscustomobject]@{Type=$_.Name;'Total POSTs'=$_.Count;'Total Bytes ↓' = ($_.group | measure-object 'cs-bytes' -Sum).Sum}} | Sort-Object 'Total Bytes ↓' -Descending | Select -first 10 | ft;

ghotz

$Log = Get-Content "C:\home\logfiles\http\rawlogs\*.log" | Select -Skip 2 | ConvertFrom-Csv -Delimiter " " -Header 'date','time','s-sitename','cs-method','cs-uri-stem','cs-uri-query','s-port,cs-username','c-ip','cs(User-Agent)','cs(Cookie)','cs(Referer)','cs-host','sc-status','sc-substatus','sc-win32-status','sc-bytes','cs-bytes','time-taken';
$Log | ? { $_.'cs-method' -eq 'POST' } | Group-object -Property 'cs(User-Agent)' | % {[pscustomobject]@{Type=$_.Name;'Total POSTs ↓'=$_.Count;'Total Bytes' = ($_.group | measure-object 'cs-bytes' -Sum).Sum}} | Sort-Object 'Total POSTs ↓' -Descending | Select -first 10 | ft
$Log | ? { $_.'cs-method' -eq 'POST' } | Group-object -Property 'cs(User-Agent)' | % {[pscustomobject]@{Type=$_.Name;'Total POSTs'=$_.Count;'Total Bytes ↓' = ($_.group | measure-object 'cs-bytes' -Sum).Sum}} | Sort-Object 'Total Bytes ↓' -Descending | Select -first 10 | ft

ghotz

-- based on https://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database/64037173#64037173
DECLARE	@tables TABLE (table_name sysname primary key);
INSERT	@tables VALUES ('schemaname.tablename'), ('schemaname.tablename')

SELECT 
     [object_id]        = t.[object_id]
    ,[schema_name]      = s.[name]
    ,[table_name]       = t.[name]
    ,[index_name]       = CASE WHEN i.[type] in (0,1,5) THEN null    ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore
    ,[object_type]      = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX'  END

ghotz

WITH cte_total_session_waits AS
(
	SELECT	[session_id]
		,	SUM(waiting_tasks_count) AS waiting_tasks_count
		,	SUM(wait_time_ms) AS wait_time_ms
		,	MAX(max_wait_time_ms) AS max_wait_time_ms
		,	SUM(signal_wait_time_ms) AS signal_wait_time_ms
	FROM	sys.dm_exec_session_wait_stats
	GROUP BY [session_id]
)

ghotz

DECLARE @MailProfile sysname = N'';
DECLARE	@AgentOperator sysname = '';
DECLARE	@MailRecipients nvarchar(max) = (select email_address from msdb..sysoperators WHERE [name] = @AgentOperator);
DECLARE	@MailSubject nvarchar(max) = N'Daily users with high privileges on server ' + @@SERVERNAME;
DECLARE	@MailQueryTable nvarchar(max) = 
	N'<h1>Daily users with high privileges on server ' + @@SERVERNAME + N' report</h1>'
	+ N'<table border="1">'
	+ N'<tr><th>Instance Name</th><th>Login Name</th><th>Login Type</th><th>Creation Date</th><th>Permission Type</th></tr>'
	+ CAST((
			SELECT

ghotz

DECLARE	@sqlstmts	nvarchar(max);

WITH cte_sqlstmts AS
(
	SELECT
		N'exec msdb.dbo.sp_stop_job @job_name = ''' + [name] + N'''; ' AS sqlstmt
	FROM	msdb.dbo.cdc_jobs AS J1
	JOIN	msdb.dbo.sysjobs_view AS J2
	  ON	J1.job_id = J2.job_id
	JOIN	msdb.dbo.sysjobactivity AS A1

ghotz

SELECT
	CASE
	WHEN I1.[type_desc] IN ('HEAP', 'CLUSTERED')
	THEN 'ALTER TABLE [' + S1.[name] + '].[' + O1.[name] + ']'
	WHEN I1.[type_desc] = 'NONCLUSTERED'
	THEN 'ALTER INDEX [' + I1.[name] + '] ON [' + S1.[name] + '].[' + O1.[name] + ']'
	END
	+ ' REBUILD' +
	CASE
	WHEN EXISTS (SELECT * FROM sys.partitions AS P2 WHERE P1.[object_id] = P2.[object_id] AND P1.[index_id] = P2.[index_id] AND P2.partition_number > 1)

ghotz

USE master;
GO
-- enable external scripting at instance level
EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

ghotz

Get-NetFirewallRule | ? {$_.Name -like "HNS Container Networking - DNS (UDP-In)*" } | Remove-NetFirewallRule
Get-NetFirewallRule | ? {$_.Name -like "HNS Container Networking - ICS DNS (TCP-In)*" } | Remove-NetFirewallRule

ghotz

-- Search statistics with leading column name
SELECT
		OBJECT_SCHEMA_NAME(C2.[object_id]) AS [schema_name]
	,	OBJECT_NAME(C2.[object_id]) AS [table_name]
	,	S1.[name] AS stats_name
	,	S1.auto_created
	,	P1.*
FROM	sys.stats AS S1
JOIN	sys.stats_columns AS C1 ON S1.[object_id] = C1.[object_id] AND S1.stats_id = C1.stats_id
JOIN	sys.columns AS C2 ON C1.[object_id] = C2.[object_id] AND C1.column_id = C2.column_id