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