Tag: Windows Admin

  • How to Delete Files in PowerShell

    How to Delete Files in PowerShell

    This is a post on how to delete files in PowerShell, which will be useful when you need to delete files as part of a script in Windows.

    This post covers the following:
    # PowerShell: Delete a File
    # PowerShell: Delete a Folder
    # PowerShell: Delete Files in Subfolders Recursively

    I have a similar post on How to Create Files & Folders in PowerShell if of interest, and more random tips can be found in the PowerShell Tips Tag.

    PowerShell: Delete a File

    Remove-Item is the cmdlet to remove a directory or file in PowerShell. We can delete files within the current directory, or add the path parameter.

    I’m showing some syntax variations below. If you are passing a directory with spaces in the name then you will have to add quotes to the path.

    # Delete file in current directory
    Remove-Item -Name testFile.txt
    
    # Delete file in specified directory
    rm -Path C:\temp\demoFolder\testFile.txt
    
    # Delete all files in specified directory with .tmp file extension
    rm "C:\temp\*.tmp"
    Remove-Item PowerShell

    PowerShell: Delete a Folder

    We do not need to change any parameters to delete a file, just run the same command as above. The consideration when deleting folders is the files within them.

    The following demo script first deletes a folder within the current directory, and the second part checks if folders exist before deleting them.

    # Delete a folder in current directory
    rm .\demoFolder\
    
    # Delete folder if exists, force delete all files in folder
    # Does not delete files in sub-directories
    $path = "c:\temp\demoFolder"
    If((Test-Path $path) ){
        rm $path -Force -Recurse
    }
    PowerShell Delete Folder If Exists

    PowerShell: Delete Files in Subfolders Recursively

    For when you need to delete files in a folder and subfolders, the script below should help. Amend path and file type wildcard as needed.

    # Delete all .txt files in folder & subfolders
    $path = "c:\temp\demoFolder"
    If((Test-Path $path) ){
        Get-ChildItem $path -Include *.txt -Recurse | rm
    }
    PowerShell Delete Files in Folder and Subfolders

    Last additional note on this, I haven’t used gci within any of the PowerShell scripts above in this post. It’s an Alias of Get-ChildItem.

  • How to Check PowerShell Version

    How to Check PowerShell Version

    This post is a guide on how to check your PowerShell version on a Windows computer. I’m using $PSVersionTable to get this info.

    It’s important to keep all software as up-to-date as possible, we all know it. Being on the latest PowerShell version is especially required if you are utilising the latest features/cmdlets. For example, you might one day attempt to copy and run a script, and realise that it contains a command that is not recognised on a lower version of PowerShell.

    To check your PowerShell version, it’s as simple as running PSVersionTable on its own.

    -- Check PowerShell Version
    $PSVersionTable
    PSVersionTable

    I’m running version 5.1.19041 of Windows PowerShell here.

    At the time of writing, version 5.1 is the latest Windows PowerShell version, but not the latest PowerShell version available to us. To get version 7, we can download and install it as per MS Docs: Installing PowerShell on Windows.

    Before looking at rolling out upgrades across several Windows hosts, we should look at the current Supported PowerShell Versions for Windows compatibility table from Microsoft.

    That’s it for now on this one. Cheers again.

  • How to Automate PowerShell Scripts with Task Scheduler

    How to Automate PowerShell Scripts with Task Scheduler

    This post is useful if you need to run PowerShell Scripts automatically with a set schedule in Windows. In the demos below, we’re creating new Scheduled Tasks via GUI and with PowerShell.

    Task Scheduler is a Windows application that we can use to create and schedule any task. We can set our Task Actions to start programs, send emails, and display messages, or we can run any script – likely a PowerShell script since we’re on Windows. These Actions can be triggered on any schedule you configure your Scheduled Task to use.

    As a Systems Administrator, adding a Scheduled Task in Windows is something we might need to do regularly. As a SQL Database Administrator, we’d use the SQL Server Agent to schedule PowerShell scripts and other tasks. And Linux Sysadmins, they’ll use Cron.

    This post covers the following :
    # How to Create a Scheduled Task using PowerShell (CLI Option)
    # How to Create a Scheduled Task for a PowerShell Script (GUI Option)

    How to Create Scheduled Task using PowerShell

    First, we need to create the PowerShell script that we want to be executed o a schedule. In the screenshot below, I’m creating an example .ps1 script in my c:\temp directory for this demo.

    The script below updates the content of a text file, logging the current Average CPU of the computer with date/time. You could have anything in your PowerShell script, e.g. Restart-Service *ServiceName* or Restart-Computer.

    Create PowerShell Script

    Now, I’m creating a new Scheduled Task that will run the above PowerShell script on a daily Schedule. The following script uses the New-ScheduledTask and Register-ScheduledTask cmdlets.

    $actions = (New-ScheduledTaskAction -Execute 'C:\temp\PowerShell_Scripts\avg_cpu_collector.ps1')
    $principal = New-ScheduledTaskPrincipal -UserId 'Administrator' -RunLevel Highest
    $trigger = New-ScheduledTaskTrigger -Daily -At '8:05 AM'
    $settings = New-ScheduledTaskSettingsSet -WakeToRun
    $task = New-ScheduledTask -Action $actions -Principal $principal -Trigger $trigger -Settings $settings
    $taskPath = '\Admin\'
    # create new scheduled task as per parameters above
    Register-ScheduledTask 'DailyPSCollector' -InputObject $task -TaskPath $taskPath
    PowerShell Create New Scheduled Task

    How to Create a Scheduled Task for a PowerShell Script (GUI)

    This is the GUI way of creating Scheduled Tasks in Windows. Open the Task Scheduler application to get started, and right-click in the empty area as shown to Create New Task.

    Populate details in the General tab as shown in the screenshot below.

    Task Scheduler Create New Task

    Go to the Triggers tab to configure the desired schedule for our new job.

    Task Scheduler Triggers Tab

    Next, in the Actions Tab click to create a new task:

    Action: Start a program
    Program/script: powershell
    Arguments: -File “C:\temp\PowerShell_Scripts\avg_cpu_collector.ps1”

    Task Scheduler Actions Tab

    Have a quick review of the Settings tab and click OK once done.

    Task Scheduler Settings Tab

    We can now review our new task in the Task Scheduler main window.

    Task Scheduler PowerShell Script

    And that’s us sorted. The PowerShell script will run daily at 6 pm as we specified during Task creation.

    Feel free to check out the Windows Admin and/or PowerShell Tips tags for more random informational blog posts.

  • SQL Server Default Port

    SQL Server Default Port

    The default port for the SQL Server Engine is 1433, which is a useful thing to know if you’re working as a Database Administrator (DBA) or Database Reliability Engineering (DBRE) type role.

    Though-out my career I’ve seen SQL Server run on the default 1433 port 99% of the time. For the other 1 percent, it didn’t matter much to me what port it was using.

    Occasionally as a DBA, you will need to arrange network line of sight (firewall rule changes) to be carried out by Network teams for MSSQL host-to-host connectivity. This is done via some request form a lot of the time, and we need to send source/destination addresses. Including IP addresses, we would also need to verify which port the SQL Server is using. Always best to double-check which MSSQL is running before submitting these types of requests, the demo below will help get this info.

    We also need to consider the local Windows Firewall on each SQL Server host. The rule should be scoped to the individual IP connecting, or subnet (10.20.30.0\24, all 256 addresses).

    SQL Server uses a lot of ports for various features & services, e.g. SQL Browser 1434 / SSIS 135. For anything beyond this basic SQL port info, MS Docs will provide you with what’s needed – there’s a lot, which I’m currently digesting at the time of writing.

    Anyway, with network stuff out of the way, this post contains the following demos:
    # How to Check Default SQL Server Port
    # How to Add New Windows Firewall Rule for Default MSSQL Port (1433)

    How to Check Default SQL Server Port

    Open SQL Server Configuration Manager, which is accessible via the Start Menu on any SQL Server host.

    Navigate as shown on the screenshot below, to SQL Server Network Configuration > Protocols for MSSQLSERVER > TCP/IP Properties > IP Address Tab

    SQL Server Check Default Port

    You can amend ports here too, but read MS Docs and test first if doing on Production, of course.

    How to Add New Windows Firewall Rule for Default MSSQL Port

    This is an added note on how to add the SQL Server 1433 Port & SQL Server Browser 1434 Ports. We can use the GUI >

    Windows Firewall Add New Rule

    And we can do the same with PowerShell, using the New-NetFirewallRule cmdlet.

    # Add SQL Server Local Firewall Rule
    New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
    # Add SQL Server Browser Local Firewall Rule
    New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow

    I hope this was a useful SQL Tip. I have another post that relates to Windows Ports- How to Find Which Process is Listening on a Port in Windows

  • PowerShell Restart Services on Multiple Hosts

    PowerShell Restart Services on Multiple Hosts

    In this post, we’ll walk through a script that restarts services on multiple remote hosts using PowerShell. A single PowerShell command will be run on several computers at the same time.

    If you need to run a command on multiple remote machines, the PowerShell Invoke-Command cmdlet is a great option. We use this to send commands for execution on remote computers.

    The command being passed in this demo is restarting the SQL Server Agent Service using the Restart-Service.

    Restart Services Multiple Hosts with PowerShell

    To use the script, simply populate the ComputerName array with your hostnames and then run the script. Here’s an example:

    # Restart service on multiple hosts
    $parameters = @{
      ComputerName = 
      	"Hostname1",
      	"Hostname2",
      	"Hostname3"
      ScriptBlock = { Restart-Service *SQLSERVERAGENT* }
    }
    Invoke-Command @parameters
    PowerShell Restart-Service

    The ScriptBlock argument can be amended to run a different PowerShell command, or you can add error checking if you’re building something more complex.

    Remember that you should always be careful when running scripts that modify services on remote hosts. It’s a good idea to test the script on a single host before running it on multiple hosts to ensure that it works as expected.

    As always, hope to follow this up with more of the same random blog posts. Feel free to check out my other PowerShell Tips.

  • How to Rename a Computer that Hosts SQL Server

    How to Rename a Computer that Hosts SQL Server

    When changing the hostname of a computer that is running SQL Server, we have to update system metadata so that remote connections and applications do not lose connectivity. The hostname system metadata is stored in sys.servers and reported by @@SERVERNAME.

    This post is a guide on how to update MS SQL metadata after a hostname change. This is often needed soon after a fresh SQL Server installation – you’ve installed SQL but then realise you need to change the Windows Server Computer Name.

    Considerations when changing a SQL Server hostname include:
    # Failover Clusters, the renaming process differs.
    # If Replication is configured on the host, I’d question why you are doing this.
    # If Mirroring, turn off Mirroring pre-change and then re-establish with new computer name. This could be high risk.
    # If the host is running SSRS, a configuration change may be required.
    # Hardcoded hostnames for all scripts/apps will have to be updated.

    This is all as described on Microsoft’s Documentation page. As always, give MS Docs a proper read and test your changes before performing in a Production environment. Hopefully, this is a new install and a single instance host you’re renaming.

    The demo below includes:
    # Change Windows Computer Name
    # Update MSSQL with the new Hostname
    # Verify Change

    Change Windows Computer Name

    To change the hostname of a Windows computer, we can do it via GUI or command (PowerShell).

    Rename Computer

    The PowerShell cmdlet for this is Rename-Computer, ensure you are running PowerShell as Administrator:

    PowerShell Rename-Computer

    A reboot is required post-change as per the warning on the terminal.

    See MS Docs as linked above for parameters required if you are connected to a Domain, or for an example of renaming remote hosts.

    Update MSSQL with the new Hostname

    Now that the computer has been renamed, when we try login into SQL Server via SSMS using the old hostname, the following error will appear:

    We need to change the server name to ‘localhost‘ or the new server name for us to get connected.

    SSMS Localhost

    Next, I’m going to run a new query to get the currently registered hostname and actual hostname.

    -- Get currently registered hostname 
    SELECT @@SERVERNAME AS 'Old Server Name' 
    
    -- Get actual hostname (undocumented proc)
    EXEC xp_getnetname
    Get Hostname SQL Server

    Next, drop the currently registered name (sp_dropserver) and then register the new hostname (sp_addserver).

    -- Remove server from list of known remote and linked servers on the local instance
    EXEC sp_dropserver 'pw-pc-pah';  
    GO
    
    -- Define name of local instance after computer rename
    EXEC sp_addserver 'WIN-PW-01', local;  
    GO
    SQL Server Rename Host Procedure

    For the change to be applied, we need to restart the SQL Server service.

    Verify Change in SQL Server

    Once the SQL Server Engine has been restarted, run the same command as before to return the currently registered hostname. It should display the new computer name:

    SQL Server Hostname Change

    Now you can verify any other apps or MSSQL features as per your test plan. Any existing connection strings will need to be updated with the new hostname if using it.

    I hope this all worked out for you if you followed along.

  • How to Find Which Process is Listening on a Port in Windows

    How to Find Which Process is Listening on a Port in Windows

    This post is a guide on how to check what service is running for a TCP or UDP port on a Windows host computer.

    You might need this information to verify what port your service is running on, or you are proactively reviewing your dodgy open ports!

    In this post, I’m demoing 2 ways of getting the connection & listening port information in Windows –
    # Show Listening Ports in Resource Monitor (easy GUI option)
    # TCPView, Sysinternals Application (quick & small download)

    Show Listening Ports in Resource Monitor

    We can view see detailed listings of all TCP and UDP endpoints on our machine using Resource Monitor.

    Open Resource Monitor (resmon.exe), or open TaskManager → Performance tab

    Open Resource Monitor

    In Resource Monitor, open the Network tab and expand Listening Ports

    Resource Monitor Listening Ports

    TCPView, Sysinternals Application

    TCPView is a Sysinternals tool that can be downloaded within seconds. TCPView shows all TCP and UDP endpoints on your system, and it’ll give you a better view of what you are looking for.

    This might be the best option if you’re going to be spending some time reviewing this information as it has filtering and search functionality.

    TCPView ->

    This is an example of how it looks –

    TCPView
  • How to Check Windows Firewall Status with PowerShell

    How to Check Windows Firewall Status with PowerShell

    This post will help you to check your local Windows Firewall status using PowerShell (whether it’s off or on for each Profile).

    PowerShell commands follow standards and use verb-noun pairs for cmdlets. The verb at the start of the command describes the action the cmdlet performs, and the noun part is the action being performed. Here’s a list of Common Verbs, another Microsoft Docs link. The place of truth as I call it.

    In this demo, we’re running Get-NetFirewallProfile with Format-Table
    We’re getting the Firewall Profile status, and also formatting it into a table after a pipe ‘|’ –

    # get local firewall status powershell
    Get-NetFirewallProfile | Format-Table Name, Enabled
    Get-NetFirewallProfile

    The Windows Firewall on this machine is enabled, for all Profiles <- have a look at this MS Docs link to know more about Windows Firewall Profiles.

    Internal follow-up links:
    # How To Disable Windows Firewall With PowerShell
    # How To Enable Windows Firewall With PowerShell

  • How to Enable Windows Firewall with PowerShell

    How to Enable Windows Firewall with PowerShell

    This post is contains a demo on how to enable Windows Firewall with PowerShell in Windows Server.

    This might be needed if you have discovered Windows Firewall is disabled on your or multiple computers, or you might want to re-enable it after disabling this for a test (see my other post on disabling instead of enabling).

    Enabling Windows Firewall with PowerShell can be done by going through the following steps:

    # Understand Windows Firewall Profiles
    # Get-NetFirewallProfile PowerShell
    # Set-NetFirewallProfile PowerShell

    Understand Windows Firewall Profiles

    Before running any command or script on your machine, it’s important for us to understand everything that it’s doing. In the command below we are disabling all 3 profiles:

    # Domain – For when you are connected to a Domain Controller (computer connected to AD).
    # Private – For your home or private networks.
    # Public – For public WIFI locations such as coffee shops etc.

    For more information on this, see this link – Microsoft Docs: Windows Firewall Profiles

    Also remember, all of this can be viewed and changed via GUI to help with understandings >

    Enable Windows Firewall

    Get-NetFirewallProfile PowerShell

    PowerShell commands follow standards and use verb-noun pairs for cmdlets. The verb at the start of the command describes the action the cmdlet performs, and the noun part is the action being performed. Here’s a list of Common Verbs, another Microsoft Docs link. The place of truth as I call it.

    In this demo, we’re running Get-NetFirewallProfile with Format-Table
    We’re getting the Firewall Profile status, and also formatting it into a table after a pipe ‘|’ –

    # get local firewall status
    Get-NetFirewallProfile | Format-Table Name, Enabled
    Get-NetFirewallProfile

    In the example above, the Windows Firewall is disabled, showing as ‘False‘.

    Set-NetFirewallProfile PowerShell

    We’re switching the cmdlet we ran above from Get to Set here (following on from verb-noun cmdlet descriptions).

    To be able to run this; we have to open PowerShell or Windows Terminal as Administrator.

    Set-NetFirewallProfile is being executed below, which enables all Profiles of the Local Windows Firewall –

    # disable local firewall
    Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled True
    
    # get local firewall status
    Get-NetFirewallProfile | Format-Table Name, Enabled
    Set-NetFirewallProfile

    I followed the change by another run of Get-NetFirewallProfile to verify the change.

  • How to Disable Windows Firewall with PowerShell

    How to Disable Windows Firewall with PowerShell

    This post contains a demo on how to disable Windows Firewall with PowerShell on a Windows computer.

    Disabling the Local Firewall on Windows is not a recommended move, and it will ultimately make your computer less secure. Sometimes it’s necessary though, for example, during troubleshooting a difficult issue you might want to disable it for a quick test to rule the FW out as being the root cause. But in this example, you will have already tried to ensure the Firewall Rule exists before turning it off completely.

    Anyway, disabling Windows Firewall with PowerShell can be done by going through the following steps:

    # Understanding Firewall Profiles in Windows
    # Get Status of Windows Firewall with PowerShell
    # Disable Windows Firewall with PowerShell

    Understanding Firewall Profiles in Windows

    Before running any command or script on your machine, it’s important for us to understand everything that it’s doing. In the command below we are disabling all 3 profiles:

    # Domain – For when you are connected to a Domain Controller (computer connected to AD).
    # Private – For your home or private networks.
    # Public – For public WIFI locations such as coffee shops etc.

    For more information on this, see this link – Microsoft Docs: Windows Firewall Profiles

    Also remember, all of this can be viewed and changed via GUI to help with understandings >

    Disable Windows Firewall via GUI

    Get Status of Windows Firewall with PowerShell

    PowerShell commands follow standards and use verb-noun pairs for cmdlets. The verb at the start of the command describes the action the cmdlet performs, and the noun part is the action being performed. Here’s a list of Common Verbs, another Microsoft Docs link. The place of truth as I call it.

    In this demo, we’re running Get-NetFirewallProfile with Format-Table
    We’re getting the Firewall Profile status, and also formatting it into a table after a pipe ‘|’ –

    # get local firewall status
    Get-NetFirewallProfile | Format-Table Name, Enabled
    Get-NetFirewallProfile

    Disable Windows Firewall with PowerShell

    We’re switching the cmdlet we ran above from Get to Set here (following on from verb-noun cmdlet descriptions).

    To be able to run this; we have to open PowerShell or Windows Terminal as Administrator.

    Set-NetFirewallProfile is being executed below, which disables all Profiles of the Local Windows Firewall –

    # disable local firewall ps1
    Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False
    
    # get local firewall status ps1
    Get-NetFirewallProfile | Format-Table Name, Enabled
    Set-NetFirewallProfile

    I followed the change by another run of Get-NetFirewallProfile to verify the change.

    If this is a temporary change for you, to re-enable the Windows Firewall, amend the ‘-Enabled False‘ statement to ‘-Enabled True‘.