CMD: Parse DATE to get a string for searching files

\ by

Sometimes we want to look for files with dates like yyyyMMdd in the name. So for given this code we can run in DOS CMD:

for /f "tokens=1-4 delims=/-. " %i in ('date /t') do @ECHO %i %j %k %l

Thu 02 21 2019

We can make our batch file to search for files with today’s date in name, using multiple %%s.

@ECHO OFF
 
rem First list everything sorted by date.
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do DIR  /OD "\\FileServer\?M*%%l%%j*.txt"

@ECHO.
@ECHO **SEARCHING for six ?M_ and one ?M_ files ...
for /f "tokens=1-4 delims=/-. " %%i in ('date /t') do @ECHO ...for date ?M*%%l%%j%%k*.txt && @ECHO. && DIR /OD "\\FileServer\?M*%%l%%j%%k*.txt"
Posted in: Coding, DOS, Files

PowerShell: Who RDP’d into our Servers and didn’t log out

\ by

Who used remote desktop to log into our servers but didn’t log out?

##########
# Remote desktop users - who is RDPed into SQL Servers?

$AllComputersList |%{ 
    $ComputerName = $_ ; 
    # $ComputerName = 'SQLPROD'
    write-host -ForegroundColor Cyan $ComputerName
    #(qwinsta /SERVER:$ComputerName)
     
    $RDPUser = (quser /SERVER:$ComputerName 2>$null )  # Don't show us any DOS errors.
    if (!$RDPUser) {
        return # continue
    }
    $RDPUser
}

Useful tip in this script is how to redirect output. Here we redirect to NUL like this

2>$null
Posted in: PowerShell, Reports

SQL What is the second Monday of this month?

\ by

What is the second Monday of this month?

;WITH [cteDate]
AS
 (
 -- @first + 7 * (@nth - 1) + (7 + @dow - DATEPART(WEEKDAY, @first + 7 * (@nth - 1))) % 7 -- Get @nth @dow 2 = Monday for us. Formula from Steve Kass.
 SELECT
      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) + 7 * (2 - 1)
      + (7 + 2 - DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) + 7 * (2 - 1))) % 7 AS [SecondMonday])
SELECT
    [cteDate].[SecondMonday],
    DATENAME(WEEKDAY, [cteDate].[SecondMonday]) AS SearchWeekDay,
	DATEDIFF(DAY,GETDATE(),[cteDate].[SecondMonday]) AS DaysFromNow
FROM [cteDate]
Posted in: Coding, SQL, SQL Server

PowerShell: Get last reboot times for all servers.

\ by 
# Last Boot Times for all servers, rebooted. Bounced.
#

$Report = @() 
foreach ($machine in $SQLInstanceDev) {
    # Change SQL Instances to ComputerNames.
    $Computer = $machine.Split('\')[0]
    $object = Get-WmiObject win32_operatingsystem -ComputerName $Computer 
    $Report += [pscustomobject] @{
        Computer       = $object.PSComputerName
        LastBootUpTime = $object.ConverttoDateTime($object.lastbootuptime)
        DayOfWeek      = ($object.ConverttoDateTime($object.lastbootuptime)).DayOfWeek
        DaysAgo        = (NEW-TIMESPAN –Start (Get-Date) –End ($object.ConverttoDateTime($object.lastbootuptime))).Days
    }
}

$Report |Sort-Object LastBootUpTime
Posted in: PowerShell, Reports

SQL: Fast Table RowCount

\ by

We aren’t looking for accuracy, so this works fine for us. The NOLOCKs helped e.g. when indexes were in the process of creating.

Fast way to count large tables.

SELECT
    SCHEMA_NAME([Tables].[schema_id]) AS [SchemaName],
    [Tables].[name] AS [TableName],
    SUM([Partitions].[rows]) AS [TotalRowCount]
FROM [sys].[tables] AS [Tables] WITH (NOLOCK)
JOIN [sys].[partitions] AS [Partitions] WITH (NOLOCK)
    ON [Tables].[object_id] = [Partitions].[object_id]
       AND [Partitions].[index_id] IN (
               0, 1
           )
WHERE [Tables].name = N'TRANSACTIONS'
--	and [Partitions].[rows] > 0
GROUP BY SCHEMA_NAME([Tables].[schema_id]),
         [Tables].[name];
Posted in: Coding, Performance, SQL

What is System Reserved Partition

\ by

Nice easy description.

What is System Reserved Partition
The System Reserved Partition holds the Boot Configuration Database, Boot Manager Code, Windows Recovery Environment and reserves space for the startup files which may be required by BitLocker, in case you use the BitLocker Drive Encryption feature.

It is created during a clean fresh installation of Windows 8, Windows 7, Windows Server 2012 and Windows Server 2008.

Posted in: Documentation, Windows

Copy Files and SubDirectories and keep folder structure

\ by

Copies a set of files recursively and recreates subfolders and files in another location.

# Copy XML files from subfolders to another drive and create subfolders.

$SourcePath = 'G:\Prod\'
$TargetPath = 'E:\SQLScripts\'
$FileFilter = '*.xml'

Get-ChildItem $SourcePath -filter $FileFilter -recurse |
    ForEach-Object { 
        $targetFile = $TargetPath + $_.FullName.SubString($SourcePath.Length); 
        $DestFolder = Split-Path $targetFile
        if (-not (Test-Path $DestFolder)) {
            New-Item -ItemType Directory -Path $DestFolder #-WhatIf
        }

        Copy-Item $_.FullName -destination $targetFile #-WhatIf
}
Posted in: Files, PowerShell

Dates Explained

\ by

Here are some of the ways dates can be output.

declare @date datetime
set @date = getdate()
select 
	@date	as SampleDate,
	datepart(year,@date)	as 'year',
	datepart(month,@date) 	as 'month',
	datepart(day,@date) 	as 'day',
	datepart(week,@date) 	as 'week',
	datepart(quarter,@date) as 'quarter',
	datepart(dy,@date) 	as 'day of year',
	datepart(weekday,@date) as 'weekday',
	datepart(hour,@date) 	as 'hour',
	datepart(minute,@date) 	as 'minute' ,

	datename(month,@date) as 'month name',
	datename(weekday,@date) as 'weekday name',

	'fiscal year' =   
	case            
		when datepart(month,@date) >= 7
		then datepart(year,@date) + 1
		else datepart(year,@date)
	end,
	CONVERT(varchar(12), getdate() ,112) 	as 'today date as integer',
	CONVERT(varchar(12),DATEADD(d, -7 ,getdate()),112) 	as 'lastweek date as integer',
	CONVERT(varchar(12), getdate() ,108) 			as 'time',
	REPLACE(CONVERT(varchar(12), getdate() ,108),':','') 	as 'time as integer',
	dateadd(d,-1, cast(month(dateadd(mm,1,getdate())) as varchar) + 
		'/01/' + cast(year(getdate()) as varchar)) as [Last day of month],
	DATEADD(D,1, 
		cast(month( getdate() ) as varchar) + '-' +
		cast(day( getdate()) as varchar) + '-' +
		cast(year(getdate() ) as varchar) ) as [Midnight Tonight]

declare @tim varchar(8)
set @tim = REPLACE(CONVERT(varchar(12), getdate() ,108),':','')
select @tim,
case when len(@tim) >= 5 then
	substring(@tim,len(@tim)-5,2 ) + ':' +
	substring(@tim,len(@tim)-3,2 ) + ':' +
	substring(@tim,len(@tim)-1,2 )
else
	@tim
end
Posted in: Coding, SQL