vlookup with powershell

I want to use the vlookup excel function within powershell and without data manipulation of the CSV itself. So I wrote a script to import a CSV, find some data and compare these data

$CSV = Import-Csv -Path C:\Users\user\some.csv

$trigger = $CSV| Where-Object {$_."Primary Description"  -like "some string with a computername"}
$result = $CSV| Where-Object {$_."Primary Description"  -like "another string with a computername"}

$triggerComputer = foreach ($i in $trigger) {($i."Primary Description" -split " ")[5]}
$resultComputer = foreach ($i in $result) {($i."Primary Description" -split " ")[5]}

Compare-Object $triggerComputer $resultComputer

Have fun!

Install winget without the Microsoft App Store

If you are unable to access the Windows Store app and wish to install winget, you can easily do so by following these steps:

  • First download the latest version of winget: https://aka.ms/getwinget
  • Then use this Powershell command to install winget
Add-AppxPackage -Path Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle

Probably you will get this error:

Windows cannot install package Microsoft.DesktopAppInstaller_1.19.10173.0_x64__8wekyb3d8bbwe because this package depends on a framework that could not be found. Provide the framework “Microsoft.UI.Xaml.2.7” published by “CN=Microsoft Corporation, O=Microsoft Corporation, L=Redmond, S=Washington, C=US”, with neutral or x64 processor architecture and minimum version 7.2109.13004.0, along with this package to install. The frameworks with name “Microsoft.UI.Xaml.2.7” currently installed are: {}

The reason is that you miss the dependency Microsoft.UI.Xaml.2.7

Now you have to go to the nuget website and download the nuget package: https://www.nuget.org/packages/Microsoft.UI.Xaml/2.7.3

Then you have to rename the file extension of the downloaded file to .zip

Open the zip file and go to tools > AppX > X64 > Release and extract the Microsoft.UI.Xaml.2.7.appx file

Install the extracted appx file

Add-AppxPackage -Path .\Microsoft.UI.Xaml.2.7.appx

You may also get this error when you try to install winget

Windows cannot install package Microsoft.DesktopAppInstaller_1.19.10173.0_x64__8wekyb3d8bbwe because this package depen
ds on a framework that could not be found. Provide the framework “Microsoft.VCLibs.140.00.UWPDesktop” published by “CN=
Microsoft Corporation, O=Microsoft Corporation, L=Redmond, S=Washington, C=US”, with neutral or x64 processor architect
ure and minimum version 14.0.30704.0, along with this package to install. The frameworks with name “Microsoft.VCLibs.14
0.00.UWPDesktop” currently installed

You can download Microsoft.VCLibs.140.00.UWPDesktop here: https://aka.ms/Microsoft.VCLibs.x64.14.00.Desktop.appx

Install Microsoft.VCLibs.x64.14.00.Desktop with this command:

Add-AppxPackage -Path .\Microsoft.VCLibs.x64.14.00.Desktop.appx

And now you should install the winget application without errors:

Add-AppxPackage -Path .\Microsoft.DesktopAppInstaller_8wekyb3d8bbwe.msixbundle

Happy installing πŸ™‚

Install and Configure OpenSSH Windows Server 2019 and 2022 and configure key-based authentication

OpenSSH is a free and open-source software that allows secure communication between computers over an unsecured network. It is widely used on Linux and Unix systems, but it is also available for Windows systems. In this article, we will show you how to install and configure OpenSSH on a Windows 2022 server.

Step 1: Install OpenSSH

The first step is to install OpenSSH on your Windows server. To do this, follow these steps:

# Install the OpenSSH Server
Add-WindowsCapability -Online -Name OpenSSH.Server~~~~
# Start the sshd service
Start-Service sshd

# OPTIONAL but recommended:
Set-Service -Name sshd -StartupType 'Automatic'

# Confirm the Firewall rule is configured. It should be created automatically by setup. Run the following to verify
if (!(Get-NetFirewallRule -Name "OpenSSH-Server-In-TCP" -ErrorAction SilentlyContinue | Select-Object Name, Enabled)) {
    Write-Output "Firewall Rule 'OpenSSH-Server-In-TCP' does not exist, creating it..."
    New-NetFirewallRule -Name 'OpenSSH-Server-In-TCP' -DisplayName 'OpenSSH Server (sshd)' -Enabled True -Direction Inbound -Protocol TCP -Action Allow -LocalPort 22
} else {
    Write-Output "Firewall rule 'OpenSSH-Server-In-TCP' has been created and exists."

Step 2: Configure OpenSSH

Once OpenSSH is installed on your server, you need to configure it to allow secure communication. Follow these steps to configure OpenSSH:

  • Open notepad
  • Open the configuration file C:\ProgramData\ssh\sshd_config
  • Remove the “#” at the beginning of the line #PubkeyAuthentication yes to uncomment it:PubkeyAuthentication yes
  • Locate the line that starts with #PasswordAuthentication yes and remove the “#” at the beginning of the line to uncomment it and change it to NO: PasswordAuthentication no
  • Add the “#” at the beginning of the line AuthorizedKeysFile PROGRAMDATA/ssh/administrators_authorized_keys to comment it out:#AuthorizedKeysFile PROGRAMDATA/ssh/administrators_authorized_keys
  • Save the changes and close the configuration file.
  • Restart the service
# Restart the sshd service
Restart-Service sshd

Step 3: Configure the Administrator for key-based authentication

After the configuration is complete, you need to configure the public key.

Optional: If you don’t have a public/private keypair use this command to create the files on your client:

ssh-keygen -t rsa -b 4096
  • Open the Explorer and go to C:\Users\<Username>\
  • Create a folder .ssh
  • Create a text file (without extension!) authorized_keys
  • Open the file in notepad
  • Paste your ssh-rsa public key in this authorized_keys file (this is the content of id_rsa.pub)
  • Save the file
  • Remove the inheritance so that only the user and system has full permission

Step 4: Test OpenSSH

To test if OpenSSH is installed and configured correctly, follow these steps:

  • Open a Command Prompt window.
  • Type “ssh username@<server>” and press Enter.
  • If the connection is successful, you should see a welcome message.

Congratulations! You have successfully installed and configured OpenSSH on your Windows 2022 server and configure it securely with key-based authentication.

Optional: change CMD to Powershell

If you want to connect directly in PowerShell instead of the default command use this PowerShell command

New-ItemProperty -Path "HKLM:\SOFTWARE\OpenSSH" -Name DefaultShell -Value "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" -PropertyType String -Force

Happy connecting πŸ™‚

NTP Error: the computer did not resync because no time data was available

When it comes to configuring your Primary Domain Controller (PDC) or local Network Time Protocol (NTP), you’ll find a a lot of information online.

But in a nutshell this is what you have to do:

w32tm /config /manualpeerlist:"<IPTIMESERVER>,0x1" /syncfromflags:manual /reliable:yes /update
net stop w32time
net start w32time
w32tm /resync /rediscover

But if the timeserver isn’t reliable you get the error:

The fix is really easy. Remove the /reliable:yes option

w32tm /config /manualpeerlist:"<IPTIMESERVER>,0x1" /syncfromflags:manual /update

And then after the w32tm /resync /rediscover:

Off course you only want to remove the /reliable:yes in a lab environment. Otherwise change the NTP server to a reliable one.

TCPDump alternative Windows

Are you in a situation where you need to capture and analyze network traffic, but don’t have Wireshark or TCPDump at your disposal? Don’t worry, there’s still a solution. Enter pktmon.

While it may not be the most sophisticated tool out there, pktmon is a viable option for monitoring traffic in real-time. With its lightweight and efficient design, you can quickly capture and analyze packets without the need for any additional software installations.

So, how exactly does pktmon work? It uses the Windows Filtering Platform (WFP) to capture network traffic at the packet level. This allows you to examine specific details about each packet, such as its source and destination address, protocol, and payload.

But what makes pktmon really stand out is its ability to filter traffic based on specific criteria. For example, you can use it to only capture traffic from a specific IP address, port, or protocol. This makes it a powerful tool for troubleshooting network issues or identifying potential security threats.

While pktmon may not be the most robust traffic monitoring tool out there, it’s certainly a valuable option to have in your arsenal and its already installed πŸ™‚ So the next time you find yourself in need of capturing and analyzing network traffic, give pktmon a try – you might just be surprised by what it can do.

#start the traffic capture
pktmon start -c
#stop the traffic capture
pktmon stop
#convert the file so you can view it in notepad
pktmon etl2txt <etl file>

More info: https://learn.microsoft.com/en-us/windows-server/networking/technologies/pktmon/pktmon-syntax

Happy sniffing πŸ™‚

Debug and Fix Android battery drain (system_server)

I previously write an article about a logd battery drain: https://blog.wapnet.nl/2021/11/android-logd-battery-drain/

But you can also have an system_server battery drain. With some easy debug steps you can find the root cause of this drain.

  • First install ADB
  • Put your phone in development mode
  • Start “adb shell”
  • Start “top”

If you have an high system_server CPU than check the PID.

Note: In my screenshot it is already fixed. But this was 30~35% CPU

In my case the PID is 1756. So now we can check logcat to find out what the reason is of the high CPU.

logcat | grep 1756

In this example you see signal is eating some CPU

With this information you can remove all the apps that is eating your CPU. In my case these apps drained my battery:

  • Spotify
  • Teams
  • MS Drive
  • Oneplus Weather

Happy debugging πŸ™‚

grantRuntimePermission: Neither user 2000 nor current process has android.permission.GRANT_RUNTIME_PERMISSIONS

I’ll was getting this issue when I try to install BetterBatteryStats on my Oneplus device.

There are 3 things to do if you want to fix this:

  1. Enable Developer mode
  2. Disable Permission Monitoring (dutch: Machtigingscontrole uitschakelen). You can find this setting in the debugger options somewhere near the bottom.
  3. You need to force quit the batteryBatteryStats app (or reboot the phone)

Then you can set the permissions:

adb -d shell pm grant com.asksven.betterbatterystats android.permission.BATTERY_STATS
adb -d shell pm grant com.asksven.betterbatterystats android.permission.DUMP
adb -d shell pm grant com.asksven.betterbatterystats android.permission.PACKAGE_USAGE_STATS
adb -d shell settings put global hidden_api_policy 1

Old 2008 Macbook Nvidia Ubuntu black screen fix

All the credits are for Andreas @ https://askubuntu.com/questions/264247/proprietary-nvidia-drivers-with-efi-on-mac-to-prevent-overheating/613573#613573

Nvidia is always a pain in the ass on Linux. Especially with a old white 2008 macbook. I have black screen issues every time so I create this article so I can easy find it when I reinstall this laptop πŸ˜‰

First find the right identifiers.

We need both the IDs for the graphics card and the PCI-E bridge that it is connected to. Issue the following command in a shell:

~$ sudo lshw -businfo -class bridge -class display
pci@0000:00:00.0              bridge         MCP89 HOST Bridge
pci@0000:00:03.0              bridge         MCP89 LPC Bridge
pci@0000:00:0e.0              bridge         NVIDIA Corporation
pci@0000:00:15.0              bridge         NVIDIA Corporation
pci@0000:00:16.0              bridge         NVIDIA Corporation
pci@0000:00:17.0     >!!<     bridge         MCP89 PCI Express Bridge
pci@0000:04:00.0     >!!<     display        MCP89 GeForce 320M

Have a look at (1) the line saying display and (2) the line with bridge right before that display line. Write down the PCI-E bus ids (format XX:YY.Z) of the bridge device (here 00:17.0) and the display device (here 04:00.0) and remember which is which. Note: Those IDs may be different on your machine, depending on your Mac model and revision.

Create a GRUB script for setting the PCI-E registers during boot

Fire up a text editor with sudo nano /etc/grub.d/01_enable_vga.conf and copy/paste the content below. Make sure to paste all 4 lines into that file! Replace 00:17.0 with the PCI-E ID of your bridge device noted in step 1. Replace 04:00.0 with the PCI-E ID of your display device noted in step 1.

cat << EOF
setpci -s "00:17.0" 3e.b=8
setpci -s "04:00.0" 04.b=7

Finally, make the created file executable and update your grub config files using the following TWO commands.

~$ sudo chmod 755 /etc/grub.d/01_enable_vga.conf
~$ sudo update-grub

Install Nvidia drivers and enjoy!

Use PowerShell Invoke-WebRequest to login on a website

To automate some tasks with PowerShell on a website you sometimes need to log in. Today I tried some curl and postman tricks but it isn’t hard if you know what to script with PowerShell and bypass all other tools.

First, find a website to log in to, then check the page source and what the submit button does. In this example, it is β€œInloggen” (dutch for login).

Then start the developer tools (F12), select the network tab, enter the credentials and login.

Now check the POST request in the developer tools.

The important things on this login form are:

  • Gebruikersnaam (username)
  • Wachtwoord (password)
  • __RequestVerificationToken

Now we can write a simple script

$LoginUri = "https://example.website.nl/versie6-0-0/mijnscore/Login"
$BackendUri = "https://example.website.nl/mijnzwemscore/inhaalles.asp"
# ==========================================
$LoginResponse = Invoke-WebRequest -Uri $LoginUri -SessionVariable "Session" 
$LoginBody = @{
    __RequestVerificationToken = $LoginResponse.InputFields[1].value
    Gebruikersnaam             = "EnterUsername"
    Wachtwoord                 = "EnterPassword"
    Submit                     = "Inloggen"
$LoginResponse = Invoke-WebRequest -Uri $LoginUri -WebSession $Session -Body $LoginBody -Method "POST"

Invoke-WebRequest -Uri $BackendUri -WebSession $Session 

Explanation of the script:

  1. Open the website and find out what the __RequestVerificationToken must be
  2. Create a body with the credentials, the login form submit and the __RequestVerificationToken
  3. Do the actual login and save the web session (cookies and stuff).
  4. Now we can use the previous web session to check all the backend stuff

Happy Scripting πŸ™‚

Update Windows Server with powershell

This is for 2016 server. But it will work for other versions of Windows

[Net.ServicePointManager]::SecurityProtocol += [Net.SecurityProtocolType]::Tls12
Install-PackageProvider -Name NuGet -MinimumVersion -Force
Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted
Install-Module PSWindowsUpdate -Confirm:$false
Set-PSRepository -Name "PSGallery" -InstallationPolicy Untrusted
Get-WindowsUpdate -AcceptAll -Install -AutoReboot

Happy Patching πŸ™‚