h3nryza

SQL Calculate IP Subnet
(charindex ( '.' , CMP.IPAddress ,charindex( '.' , CMP.IPAddress ,(charindex('.',CMP.IPAddress) +1 ) ) +1)-1)

SQL Spit out SQL Subnet
SUBSTRING( 
					CMP.IPAddress,
					0,
					(charindex ( '.' , CMP.IPAddress ,charindex( '.' , CMP.IPAddress ,(charindex('.',CMP.IPAddress) +1 ) ) +1))
					)

h3nryza

--SQL Round to nearest Minute
select dateadd(mi, datediff(mi, 0, @dt), 0)

--Sql Round to Nearest Hour
select dateadd(hour, datediff(hour, 0, @dt), 0)

--Sql Last day of week
first day - last day

--Sql First Day of Week

h3nryza

CREATE FUNCTION dbo.IntegerToIPAddress (@IP AS bigint)

RETURNS varchar(15)

AS

BEGIN

DECLARE @Octet1 tinyint

h3nryza

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

h3nryza

SELECT  L.request_session_id AS SPID, 
        DB_NAME(L.resource_database_id) AS DatabaseName,
        O.Name AS LockedObjectName, 
        P.object_id AS LockedObjectId, 
        L.resource_type AS LockedResource, 
        L.request_mode AS LockType,
        ST.text AS SqlStatementText,        
        ES.login_name AS LoginName,
        ES.host_name AS HostName,
        TST.is_user_transaction as IsUserTransaction,

h3nryza

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB

h3nryza

USER_LOOKUPS,          
USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S          
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID]               
AND I.INDEX_ID = S.INDEX_ID 
Join sys.Databases d on s.database_id = d.database_id
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 
Order by USER_SEEKS + USER_SCANS + USER_LOOKUPS + USER_UPDATES desc

h3nryza

import os
os.system('cls')  # on windows
os.system('clear')  # on linux / os x

h3nryza

Param([
	parameter(
		Mandatory=$true,
		Position=0,
		ValueFromPipelineByPropertyName=$true,
		ValueFromPipeline=$true,
		HelpMessage="Enter one or more User names separated by commas.")
	]
	[ValidateNotNullOrEmpty()]
	[alias("UN","User")]

h3nryza

#Get all Valid IPV4
Get-CimInstance Win32_NetworkAdapterConfiguration | where{$_.ipenabled -like "True"}| Select -ExpandProperty IPAddress | where{$_ -like "*.*.*"}
#Select first valid V4
Get-CimInstance Win32_NetworkAdapterConfiguration | where{$_.ipenabled -like "True"}| Select -ExpandProperty IPAddress | where{$_ -like "*.*.*"} | Select -first 1

#Quick method
$localIpAddress=((ipconfig | findstr [0-9].\.)[0]).Split()[-1]

#Longer Method
$ipaddress=([System.Net.DNS]::GetHostAddresses('PasteMachineNameHere')|Where-Object {$_.AddressFamily -eq "InterNetwork"}   |  select-object IPAddressToString)[0].IPAddressToString