Category Archives: Programmering

Two SQL Server Tools I Always Use for Troubleshooting and Performance Analysis

When troubleshooting Microsoft SQL Server environments—especially under pressure—having the right tools can make all the difference. Over time, I’ve found myself consistently relying on two lightweight but incredibly powerful tools that provide insight into what’s happening right now and help build historical context for analysis.

In this post, I’ll walk through the two tools I use the most:

  • sp_WhoIsActive (by Adam Machanic)
  • Extended logging setup using sp_WhoIsActive (inspired by Thomas S. Williams)

sp_WhoIsActive – Real-time insight into SQL Server

👉 http://whoisactive.com/

If you’re working with SQL Server and not using sp_WhoIsActive, you’re making life harder than it needs to be.

This stored procedure gives you a real-time snapshot of what’s happening inside your SQL Server instance:

  • Currently running queries
  • Wait types
  • Blocking chains
  • CPU and memory usage
  • Query execution plans

Example usage:

EXEC sp_WhoIsActive;

But where it really shines is when you start adding parameters:

EXEC sp_WhoIsActive
@get_plans = 1,
@get_blocking_info = 1,
@get_outer_command = 1;

Building History with sp_WhoIsActive Logging

This gives you a much deeper understanding of what’s going on—especially useful when dealing with blocking or long-running queries.

👉 https://thomasswilliams.github.io/sqlserver/2020/12/02/spwhoisactive.html

Real-time data is great—but when troubleshooting intermittent issues, you need history.

That’s where logging sp_WhoIsActive output becomes extremely valuable.

Using the approach described by Thomas S. Williams, you can:

  • Store snapshots of activity in a table
  • Schedule it via SQL Server Agent
  • Build a historical dataset for analysis

This allows you to answer questions like:

  • What happened last night at 02:00?
  • Which queries were causing blocking?
  • Was there unusual CPU or IO activity?

Example setup:

EXEC sp_WhoIsActive
@get_plans = 1,
@destination_table = 'dbo.WhoIsActiveLog';

Once you log data regularly (e.g. every minute), you essentially create your own lightweight monitoring system.


🧠 Why I use these tools

These two tools together give me:

  • 🔎 Real-time visibility when something is wrong
  • 📊 Historical insight when investigating past incidents
  • Fast troubleshooting without needing heavy monitoring tools

They’re simple, flexible, and incredibly effective.

💡 Final thoughts

You don’t always need expensive monitoring solutions to troubleshoot SQL Server effectively.

With sp_WhoIsActive and a simple logging setup, you can get:

  • Deep insight into query behavior
  • Visibility into blocking and waits
  • A historical baseline for performance

If you’re not already using these tools—I highly recommend adding them to your toolbox.


👉 I regularly use setups like this when troubleshooting large queries and performance issues across environments.

Linux Docker contrainer and PRTG

Simple script to monitor that all docker container is running, and report the status back to PRTG
It return 1 if the docker is running, or else it will return 0.

#!/bin/bash
echo  "<prtg>"
#dockerlist_arr=( $(docker ps -a) )
readarray -t dockerlist_arr < <( docker ps -a | awk '{print $NF}' | tail -n +2 )
for i in "${dockerlist_arr[@]}"
do
   echo "<result>"
   echo "<channel>$i</channel>"
   if [ "$( docker container inspect -f '{{.State.Running}}' $i )" = "true" ]; then
        echo "<value>1</value>"
   else
        echo "<value>0</value>"
   fi
   echo "<LimitMode>1</LimitMode>"
   echo "<LimitMaxError>1</LimitMaxError>"
   echo "<LimitMinError>1</LimitMinError>"
   echo "</result>"
# or do whatever with individual element of the array
done
echo "</prtg>"

Problems in connection to a Webservice from Windows Server 2016 / 2019

Today, I have this issue, that i could not connect to a webservices, that are hosted by the Danish Tax Service, the URL where: https://emcstest.skat.dk
I got this warning:

Invoke-WebRequest : The request was aborted: Could not create SSL/TLS secure channel.

When running these commands

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$WebResponse = Invoke-WebRequest "https://emcstest.skat.dk"

No matter what I did. But final I tested the webpage against SSLLabs
https://www.ssllabs.com/ssltest/analyze.html?d=emcstest.skat.dk
Found these ciphers

I then run these commands:

Enable-TlsCipherSuite TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256
Enable-TlsCipherSuite TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
Enable-TlsCipherSuite TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256
Enable-TlsCipherSuite TLS_AES_256_GCM_SHA384
Enable-TlsCipherSuite TLS_AES_128_GCM_SHA256
Enable-TlsCipherSuite TLS_CHACHA20_POLY1305_SHA256
Enable-TlsCipherSuite TLS_AES_256_GCM_SHA384

And finally I where able to access the webpage though powershell.

Connect to 365 Exchange mailbox with IMAP and OAuth

Source: https://stackoverflow.com/questions/73370661/php-connect-mailbox-office-365-with-oauth

1 – Configure your mail box in Azure

(I didn’t do this part so i can’t help you more than that ! )

Edit : Thanks to parampal-poonithis link explains how to configurate in azure.

You will need :

  • The client Id
  • The tenant Id
  • The secret client
  • The redirect Uri (Set it to http://localhost/test_imap)

2 – Grab a code to get a token

Construct this url :

$TENANT="5-48...";
$CLIENT_ID="c-9c-....";
$SCOPE="https://outlook.office365.com/IMAP.AccessAsUser.All";
$REDIRECT_URI="http://localhost/test_imap";

$authUri = 'https://login.microsoftonline.com/' . $TENANT
           . '/oauth2/v2.0/authorize?client_id=' . $CLIENT_ID
           . '&scope=' . $SCOPE
           . '&redirect_uri=' . urlencode($REDIRECT_URI)
           . '&response_type=code'
           . '&approval_prompt=auto';

echo($authUri);

Go to the link, connect to the mail box with the passeword. Once it done, you will be redirect to : http://localhost/test_imap?code=LmpxSnTw…&session_state=b5d713….

Save the code (remove the ‘&’ at the end !) and the session state inside the url. These codes expired after a few hours !

When you are on this new page look inside the url, you should have htp:/localhost/test_imap?code=MyCodeIShouldSave&session_state=MySessionIShouldSave This is the $CODE and the $SESSION you are looking for for the step 3

3 – Get an access token

$CLIENT_ID="c-9c-....";
$CLIENT_SECRET="Y~tN...";
$TENANT="5-48...";
$SCOPE="https://outlook.office365.com/IMAP.AccessAsUser.All offline_access";
$CODE="LmpxSnTw...";
$SESSION="b5d713...";
$REDIRECT_URI="http://localhost/test_imap";

echo "Trying to authenticate the session..";

$url= "https://login.microsoftonline.com/$TENANT/oauth2/v2.0/token";

$param_post_curl = [ 
 'client_id'=>$CLIENT_ID,
 'scope'=>$SCOPE,
 'code'=>$CODE,
 'session_state'=>$SESSION,
 'client_secret'=>$CLIENT_SECRET,
 'redirect_uri'=>$REDIRECT_URI,
 'grant_type'=>'authorization_code' ];

$ch=curl_init();
curl_setopt($ch,CURLOPT_URL,$url);
curl_setopt($ch,CURLOPT_POSTFIELDS, http_build_query($param_post_curl));
curl_setopt($ch,CURLOPT_POST, 1);
curl_setopt($ch,CURLOPT_RETURNTRANSFER, true);

$oResult=curl_exec($ch);

echo "result : \n";

var_dump($oResult);

The access_token given in response is going to work only for a few hours. ( If your script is going to be launch on a daily basic you need to recreate a token. I’m going to show you how in the part 5 ! Save the refresh_token inside $oResult. (It are in the middle of the output).
If you don’t have the “refresh_token” you have forgot to put “offline_access” in the scope)

4 – Connect to the mail box

Now choose your favorite library 😉 ! We will use webklex/php-imap for this example (https://github.com/Webklex/php-imap)

include __DIR__.'/vendor/autoload.php'; 
    
use Webklex\PHPIMAP\ClientManager;

$access_token="EH.j8s5z8...";
    
//$cm = new ClientManager($options = ["options" => ["debug" => true]]);                     
$cm = new ClientManager();                      
$client = $cm->make([
    'host'          => 'outlook.office365.com',                
    'port'          => 993,
    'encryption'    => 'ssl',
    'validate_cert' => false,
    'username'      => '[email protected]',
    'password'      => $access_token,
    'protocol'      => 'imap',
    'authentication' => "oauth"
]);

try {
    //Connect to the IMAP Server
    $client->connect();
    $folder = $client->getFolder('INBOX');
    $all_messages = $folder->query()->all()->get();
    //DONE ! :D     
} catch (Exception $e) {
    echo 'Exception : ',  $e->getMessage(), "\n";

5 – Connecting to the mail box everyday

include __DIR__.'/vendor/autoload.php'; 
    
use Webklex\PHPIMAP\ClientManager;

$CLIENT_ID="c-9c-....";
$CLIENT_SECRET="Y~tN...";
$TENANT="5-48...";
$REFRESH_TOKEN="EebH9H8S7...";

$url= "https://login.microsoftonline.com/$TENANT/oauth2/v2.0/token";

$param_post_curl = [ 
 'client_id'=>$CLIENT_ID,
 'client_secret'=>$CLIENT_SECRET,
 'refresh_token'=>$REFRESH_TOKEN,
 'grant_type'=>'refresh_token' ];

$ch=curl_init();

curl_setopt($ch,CURLOPT_URL,$url);
curl_setopt($ch,CURLOPT_POSTFIELDS, http_build_query($param_post_curl));
curl_setopt($ch,CURLOPT_POST, 1);
curl_setopt($ch,CURLOPT_RETURNTRANSFER, true);
//ONLY USE CURLOPT_SSL_VERIFYPEER AT FALSE IF YOU ARE IN LOCALHOST !!!
curl_setopt($ch,CURLOPT_SSL_VERIFYPEER, false);// NOT IN LOCALHOST ? ERASE IT !

$oResult=curl_exec($ch);

echo("Trying to get the token.... \n");

if(!empty($oResult)){
    
    echo("Connecting to the mail box... \n");
    
    //The token is a JSON object
    $array_php_resul = json_decode($oResult,true);
    
    if( isset($array_php_resul["access_token"]) ){

        $access_token = $array_php_resul["access_token"];

        //$cm = new ClientManager($options = ["options" => ["debug" => true]]);                     
        $cm = new ClientManager();                      
        $client = $cm->make([
            'host'          => 'outlook.office365.com',                
            'port'          => 993,
            'encryption'    => 'ssl',
            'validate_cert' => false,
            'username'      => '[email protected]',
            'password'      => $access_token,
            'protocol'      => 'imap',
            'authentication' => "oauth"
        ]);
        
        try {
            //Connect to the IMAP Server
            $client->connect();
        }catch (Exception $e) {
            echo 'Exception : ',  $e->getMessage(), "\n";
        }

    }else{
        echo('Error : '.$array_php_resul["error_description"]); 
    }
}

It will only connect to the mailbox, you then need to write some more code to get access to the mail. Look at https://github.com/Webklex/php-imap

Compare two folders with Powershell

This script checks first that folders exist, then that filename are the same, and then that file Hash is the same

#$folder1 = "C:\Temp\test"
#$folder2 = "C:\Temp\test2"


if ((Test-Path -Path $folder1) -and (Test-Path -Path $folder2)) {
    echo "The folders exist"
    echo "Folder1: $folder1"
    echo "Folder2: $folder2"
    $sourceFiles = Get-ChildItem $folder1 -Recurse
    $destFiles = Get-ChildItem $folder2 -Recurse
    if (Compare-Object $sourceFiles.Name $destFiles.Name) {
        echo "The folders is not the same"
    } else {
        echo "Check of the folders show us that there have the same content - OK"
        $SourceDocs = Get-ChildItem –Path $folder1 -Recurse | foreach  {Get-FileHash –Path $_.FullName}
        $DestDocs = Get-ChildItem –Path $folder2 -Recurse | foreach  {Get-FileHash –Path $_.FullName}
        if ($SourceDocs.Hash -ne $destDocs.Hash) {
            echo "There are difference in the the files"
            echo "folder1 Hash: $SourceDocs.hash"
            echo "folder2 Hash: $DestDocs.hash"
        } else  {
            echo "The folders are the same!"
        }
    }
} else {
    Echo "One of the folders or both, dosn't exist"
}

Delete specific mail in Sophus Exim queu

To show the queue in sophus, from CLI type:

chroot /var/storage/chroot-smtp /bin/exim -bp

To delete a specific mail type

chroot /var/storage/chroot-smtp /bin/exim -Mrm {mailid}

example:

chroot /var/storage/chroot-smtp /bin/exim -Mrm 1daHZv-000EaR-0A

To see how many mails, there are in queue, you can type:

chroot /var/storage/chroot-smtp /bin/exim -bpc

Give access to calendar based on the user is member of the group.

If you need to give auser access to users calendar, based on if there are member of an group, this is the way.
Start Exchange Management Shell

$users = Get-ADGroupMember [groupname] | select -ExpandProperty name
foreach ($user in $users) {
    $Mailbox = Get-Mailbox $user
	#The Calendar, is name "Kalender" in danish
	add-MailboxFolderPermission -identity “$($Mailbox.Name):\kalender” -AccessRights Editor -User [username]
	set-MailboxFolderPermission -identity “$($Mailbox.Name):\kalender” -AccessRights Editor -User [username]

	#English calendar
	add-MailboxFolderPermission -identity “$($Mailbox.Name):\calendar” -AccessRights Editor -User [username]
	set-MailboxFolderPermission -identity “$($Mailbox.Name):\calendar” -AccessRights Editor -User [username]
} 

Tomcat Web Server SSL Certificate Installation

How to generate a CSR in Tomcat with Keytool

Create a New Keystore

You will need to create a new Keystore, this is done by this command:

keytool -genkey -alias domainname.prefix -keyalg RSA -keysize 2048 -keystore /path/domainname.prefix.jks

When the commands ask for the firstname and lastname, please type domainname.prefix instead of you name, else the certificate will issues to you name, instead of the domain name.

Generate a CSR from Your New Keystore

  1. Next, you need to generate a CSR file, this is done by this command
    keytool -certreq -alias domainname.prefix -file certreq.cer -keystore /path/domainname.prefix.jks
  2. Type the keystore password that you chose earlier and hit Enter.
  3. Please upload the certreq.cer, to you standard certificate supplier.

Installing the SSL Certificates to the Keystore

  1. You will then get a certificate, save it as signcert.cer
  2. Download aswell the correct intermediate certificate, aswell as the correct root certificate.
  3. Import them with this command:
    keytool -import -trustcacerts -alias root -file root.cer -keystore /path/domainname.prefix.jks
    
    keytool -import -trustcacerts -alias intermediate -file intermediate.cer -keystore /path/domainname.prefix.jks
  1. Import the signed certificate
    keytool -import -trustcacerts -alias domainanme.prefix -file signcert.cer -keystore /path/domainname.prefix.jks

Configuring your SSL Connector

Before Tomcat can accept secure connections, you need to configure an SSL Connector.

  1. In a text editor, open the Tomcat server.xml file.
    The server.xml file is usually located in the conf folder of your Tomcat’s home directory.
  1. Locate the connector that you want to use the new keystore to secure.
    Usually, a connector with port 443 or 8443 is used, as shown in step 4.
  1. If necessary, uncomment the connector.
    To uncomment a connector, remove the comment tags (<!– and –>).
  1. Specify the correct keystore filename and password in your connector configuration.

When you are done, your connector should look something like this:

<Connector port="443" maxHttpHeaderSize="8192" maxThreads="150" minSpareThreads="25" maxSpareThreads="75" enableLookups="false" disableUploadTimeout="true" acceptCount="100" scheme="https" secure="true" SSLEnabled="true"clientAuth="false" sslProtocol="TLS" keyAlias="server" keystoreFile="/path/domainname.prefix.jks " keystorePass="your_keystore_password" />

Note: If you are using a version of Tomcat prior to Tomcat 7, you need to change “keystorePass” to “keypass”.

  1. Save your changes to the server.xml file.
  2. Restart Tomcat.

 

List all enabled user in Active Directory

How to list all enabled user in active directory:

Get-ADUser -filter {Enabled -eq $True} -Properties "DisplayName","emailaddress" | select name,emailaddress

How to list all enabled user in active directory, for a given OU:

Get-ADUser -SearchBase "OU=Accounts,OU=RootOU,DC=ChildDomain,DC=RootDomain,DC=com" -filter {Enabled -eq $True} -Properties "DisplayName","emailaddress","title" | select name,emailaddress