For all databases query sys.sysprocesses to find open transactions. It is useful to find locks in DB:
SELECT * FROM sys.sysprocesses WHERE open_tran = 1
For the current database use:
DBCC OPENTRAN
To find row counts for all tables. Very useful when we don’t know where to start when working in a new DB.
https://learn.microsoft.com/en-us/answers/questions/1073600/get-row-counts-for-all-tables-in-all-databases-in
— kill process when someone forgets to close Adobe file that you may need to edit:
taskkill /f /s PCNAME_HOSTNAME /FI “IMAGENAME eq Adobe*”
taskkill /s <net_name> /PID 1234 /f
–show details when PC was for example rebooted last time:
systeminfo /s PCNAME_HOSTNAME
–opened ports
netstat -an | findstr 8016
–starting IIS from command line
inetmgr
— how to check when my password expires
net user /domain username(AD)
–list of windows updates
wmic qfe list
wmic /node:’computer name‘ qfe GET description, hotfixid, installedby, installedon,servicepackineffect > Computer.txt
— Powershell – get process name/details based on description value:
PS X:\Pablo> Get-Process | where {$_.description -match ‘Cisco’}
grep command – it is used to find pattern/string in the files:
https://www.man7.org/linux/man-pages/man1/grep.1.html
I use this one pretty often:
$ grep -rnie “string” filename.txt
r- recursive – read all the files under each directory
n- prefix each line of output with line number where pattern was found
i – ignore case
e – search for given pattern
another example:
grep -n “YOUR SEARCH STRING” * > output-file
sed command
Stream editor for filtering and transforming text
example to show lines between 11115149 and 11145149 for filename input.
$ sed ‘11115149,11145149!d’ filename.txt
A great workaround if telnet is not installed on MS server but PowerShell is available. Just open PowerShell and use the following command to test if port is accessible.
Test-NetConnection -ComputerName SERVERNAME -InformationLevel “Detailed”
The Test-NetConnection cmdlet displays diagnostic information for a connection. It supports ping test, TCP test, route tracing, and route selection diagnostics. Depending on the input parameters, the output can include the DNS lookup results, a list of IP interfaces, IPsec rules, route/source address selection results, and/or confirmation of connection establishment.
Search Tables:
SELECT c.name AS ‘ColumnName’
,t.name AS ‘TableName’
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE ‘%MyName%’
ORDER BY TableName
,ColumnName;
Search Tables & Views:
SELECT COLUMN_NAME AS ‘ColumnName’
,TABLE_NAME AS ‘TableName’
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE ‘%MyName%’
ORDER BY TableName
,ColumnName;
This is an example that I created to open a file, manipulate the text and output to the file.
$path_in = “C:testinput.txt”
$path_out = “C:testoutput.txt”
$endMessage = “=============== END ==============”
$reader = [System.IO.File]::OpenText($path_in)
$get_time_message = “program executed on ” + [datetime]::now.ToString(‘yyyy-MM-dd / HH:mm:ss’)
try {
add-content $path_out $get_time_message
add-content $path_out “to unix: “
$long_string_to_excel =””
while($true){
$line = $reader.ReadLine()
if ($line -eq $null) { break }
# divide the input line into array – remove white space
# it is hard coded here below for the lines that consist two and three space characters
$better_line = $line.replace(‘ ‘,’ ‘)
$best_line = $better_line.replace(‘ ‘,’ ‘).split(‘ ‘)
$stringToOutput = “rm -fr ” + $best_line[8]
$long_string_to_excel = $long_string_to_excel + $best_line[8] + “`r`n”
add-content $path_out $stringToOutput
}
add-content $path_out “`n”
add-content $path_out “to excel log: ”
add-content $path_out $long_string_to_excel
add-content $path_out $endMessage
}
finally {
$reader.Close()
}
write-host “program execution:`ncompleted”
Hello,
Let me share the code that I have used to automatically sent out emails using PowerShell. You can use Windows Task Scheduler to automate its execution.
#Program to send test emails
$counter = 0;
$LogPath = “C:SendEmailsToolSend Test EmailsLog.txt”
“Program started at $(Get-Date)” | Out-File -FilePath $LogPath -Append
$emailfrom = “Richmond Times-Dispatch <no-reply@richmond.com>”
$emailTo = “email@destination.com”
$smtpserver = “internal-smtp”
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
while ($counter -le 0){
try{
$emailSubject = “Testing email #” + $counter + “”;
$body = “This is another test # ” + $counter + ” Please ignore.`r`n”
$smtp.Send($emailfrom, $emailTo, $emailSubject, $body)
$counter++;
Start-Sleep -Milliseconds 1000
}
catch{
“Caught an exception:” | Out-File -FilePath $LogPath -Append
“Exception Type: $($_.Exception.GetType().FullName)” | Out-File -FilePath $LogPath -Append
“Exception Message: $($_.Exception.Message)” | Out-File -FilePath $LogPath -Append
$caughtException = $true
}
}
“Program completed at $(Get-Date) =======” | Out-File -FilePath $LogPath -Append