How to check the Active Directory user password policy When Get-ADUserResultantPasswordPolicy Returns Nothing

Recently, I have had to troubleshoot a variety of SQL login issues, and often the problem was related to the user's Active Directory account.

I am aware that my organization implements security policies that include, among other things, an Active Directory password policy. Of course, there's also the SQL Server user security policy, which I am quite familiar with.

To better support my users, I decided it would be beneficial to familiarize myself with the current Active Directory password policy. There is a document that outlines this policy, but I want to verify the settings that are actually in effect. After all, documentation can sometimes be outdated.

For this purpose, I am using PowerShell to retrieve the password policy values. Note that you will need to have the ActiveDirectory PowerShell module installed to do this.

# To check if the AD module is installed

Get-Module -Name ActiveDirectory

# Lets now check if a password policy is assigned to a user
Get-ADUserResultantPasswordPolicy -Identity aduser1

In this case, the 'Get-ADUserResultantPasswordPolicy' command returns nothing. This likely indicates that the password policy is not assigned on an individual user basis, known as Fine-Grained Password Policies. If your AD environment doesn't have Fine-Grained Password Policies (FGPP) defined or if they are not applied to the specific user you're querying, the command will return no result. FGPP allows different password policies within the same domain, but if it's not set up, users will just follow the default domain password policy

This approach makes sense for most organizations. Assigning unique password policies to each user can be impractical. Instead, for groups of users who require stricter (e.g., users with administrative rights) or more lenient password policies, it's more efficient to segment them into different domains or organizational units (OUs). These segments can have tailored settings to mitigate any associated risks.

I am now going to verify the default password policy in the domain.

Get-ADDefaultDomainPasswordPolicy

When you run this command, it returns various properties of the default domain password policy, such as:

• ComplexityEnabled: Indicates whether password complexity requirements are enabled.
• LockoutDuration: The duration for which an account remains locked after reaching the specified number of failed login attempts.
• LockoutObservationWindow: The time window in which consecutive failed login attempts are counted towards the lockout threshold.
• LockoutThreshold: The number of failed login attempts that will trigger an account lockout.
• MaxPasswordAge: The maximum age of a password before it must be changed.
• MinPasswordAge: The minimum age of a password before it can be changed.
• MinPasswordLength: The minimum number of characters required in the password.
• PasswordHistoryCount: The number of unique new passwords a user must use before an old password can be reused.

For example:

ComplexityEnabled           : True
DistinguishedName           : DC=internal,DC=external,DC=org
LockoutDuration             : 00:30:00
LockoutObservationWindow    : 00:30:00
LockoutThreshold            : 6
MaxPasswordAge              : 90.00:00:00
MinPasswordAge              : 1.00:00:00
MinPasswordLength           : 8
objectClass                 : {domainDNS}
objectGuid                  : xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
PasswordHistoryCount        : 8
ReversibleEncryptionEnabled : False

To get the default policy in a different AD domain than you are currently logged into:

Get-ADDefaultDomainPasswordPolicy -Server MyADDomain 

And, if you are curious what all these values mean, please refer to the following MS document:


https://learn.microsoft.com/en-us/powershell/module/activedirectory/set-addefaultdomainpasswordpolicy

How do you use PowerShell to check if an active directory user locked out, disabled etc.?

If your organization uses a password policy (there are very good odds these days that it does) and, especially stricter password requirement for administrative users, your might have experienced instances where yours or your users Active Directory user might be locked out.

How do you check if that is the case? Well, for one thing the Windows will tell you so when you try to login and/or failed login attempts are logged in to sql log, event logs etc.  What if user does not logout or have more than one user account, one for regular use and one for administrative tasks? There maybe other scenarios where you have a need to check status of a user account in the Active Directory.

I don't have admin privileges in Active Directory and presumably you don't either.  However, I do have read permission on the AD so I could have used Active Directory Users and Groups snap-in i.e. GUI tool.

But, here I am going to show you the PowerShell way.

You will need to have the ActiveDirectory PowerShell module installed for the following cmdlets to work. To check if you already have it:

Get-Module -Name ActiveDirectory

To cherck if it is available to import into your current session:

Get-Module  -ListAvailable -name ActiveDirectory

If it is available, you can import it using the following command. You will need to be running the elevated PowerShell for this:

Import-Module -Name ActiveDirectory

Let's check if the user account is disabled:

# Is account disabled?

get-aduser aduser1 -Properties enabled | ft Enabled

Enabled
-------
   True 

# Is account locked out?

get-aduser aduser1 -Properties LockedOut | ft LockedOut

LockedOut
---------
    False

# When does the password expire?

Get-ADUser aduser1 -properties msDS-UserPasswordExpiryTimeComputed | select @{N="PasswordExpiryDate";E={[DateTime]::FromFileTime($_."msDS-UserPasswordExpiryTimeComputed")}}


PasswordExpiryDate  
------------------  
2/13/2020 2:58:26 PM
 

# Finally, view all properties for a user account

get-aduser aduser1 -Properties *

If the user account is in a different AD domain:

get-aduser aduser1 -Server ad_domain_name -Properties *

To find out more about the Get-ADUser command:

Get-Help  Get-ADUser -ShowWindow

That will pop up a window with the Get-ADUser help file.

And to see all the commands available in ActiveDirectory module:

Get-Command  -Module ActiveDirectory | Select-Object Name

Name                                                
------------------------------------                                                
Add-ADCentralAccessPolicyMember                     
Add-ADComputerServiceAccount                        
Add-ADDomainControllerPasswordReplicationPolicy     
Add-ADFineGrainedPasswordPolicySubject              
Add-ADGroupMember                                   
Add-ADPrincipalGroupMembership                      
Add-ADResourcePropertyListMember                    
Clear-ADAccountExpiration                           
Clear-ADClaimTransformLink                          
Disable-ADAccount                                   
Disable-ADOptionalFeature                           
Enable-ADAccount                                    
Enable-ADOptionalFeature                            
Get-ADAccountAuthorizationGroup                     
Get-ADAccountResultantPasswordReplicationPolicy     
Get-ADAuthenticationPolicy                          
Get-ADAuthenticationPolicySilo                      
Get-ADCentralAccessPolicy                           
Get-ADCentralAccessRule                             
Get-ADClaimTransformPolicy                          
Get-ADClaimType                                     
Get-ADComputer          
....
....
....

 

SQL Server Event Notifications Example

Event notifications are kinda like a trigger in the sense that they respond to specific event, specifically in response to DDL statements and SQL Trace events.

The major difference between the triggers and event notifications is that the triggers are fired in synchronous mode to execute a specific code within the same session and transaction. Whereas, the event notification do not execute any code, it only sends information in asynchronous mode which then can be logged and acted upon later on.


Here is a snippet from the Microsoft Documentation that explains the differences:

Event Notifications vs. Triggers

The following table compares and contrasts triggers and event notifications.

Triggers	Event Notifications
DML triggers respond to data manipulation language (DML) events. DDL triggers respond to data definition language (DDL) events.	Event notifications respond to DDL events and a subset of SQL trace events.
Triggers can run Transact-SQL or common language runtime (CLR) managed code.	Event notifications do not run code. Instead, they send xml messages to a Service Broker service.
Triggers are processed synchronously, within the scope of the transactions that cause them to fire.	Event notifications may be processed asynchronously and do not run in the scope of the transactions that cause them to fire.
The consumer of a trigger is tightly coupled with the event that causes it to fire.	The consumer of an event notification is decoupled from the event that causes it to fire.
Triggers must be processed on the local server.	Event notifications can be processed on a remote server.
Triggers can be rolled back.	Event notifications cannot be rolled back.
DML trigger names are schema-scoped. DDL trigger names are database-scoped or server-scoped.	Event notification names are scoped by the server or database. Event notifications on a QUEUE_ACTIVATION event are scoped to a specific queue.
DML triggers are owned by the same owner as the tables on which they are applied.	The owner of an event notification on a queue may have a different owner than the object on which it is applied.
Triggers support the EXECUTE AS clause.	Event notifications do not support the EXECUTE AS clause.
DDL trigger event information can be captured using the EVENTDATA function, which returns an xml data type.	Event notifications send xml event information to a Service Broker service. The information is formatted to the same schema as that of the EVENTDATA function.
Metadata about triggers is found in the sys.triggers and sys.server_triggerscatalog views.	Metadata about event notifications is found in the sys.event_notifications and sys.server_event_notifications catalog views.

---

In the following sample SQL script, I am creating an Extended Event to capture  ALTER TABLE events in a database.

-- Create a brand new database for the testing purpose
use master
go
if db_id('temp_event_notification_test_929368242990-321836') is not null
  drop database [temp_event_notification_test_929368242990-321836]
go
CREATE DATABASE [temp_event_notification_test_929368242990-321836]
GO


-- Enable the service broker if its not already
if not exists 

(select * from sys.databases 
 where name = '[temp_event_notification_test_929368242990-321836]' 
   and is_broker_enabled = 1
)

ALTER DATABASE [temp_event_notification_test_929368242990-321836] SET ENABLE_BROKER; 
go

-- set the trustworth property ON
if not exists 
(select * from sys.databases 
 where name = '[temp_event_notification_test_929368242990-321836]' 
   and is_trustworthy_on = 1
)

ALTER DATABASE [temp_event_notification_test_929368242990-321836] 
SET TRUSTWORTHY ON;
go

-- check if there is already a service broker end point running
if not exists (select * from sys.service_broker_endpoints 
               where type_desc = 'SERVICE_BROKER' 
			     and state_desc = 'STARTED' 
			  )
BEGIN
    -- check if there is a SB endpoint with same name
    if not exists 
	(select * from sys.service_broker_endpoints 
	 where NAME = 'en_service_broker_929368242990-321836')
 
 BEGIN
    -- check to make sure the tcp port is not already in use
    if not exists (SELECT * FROM SYS.tcp_endpoints where port = 5122)
	   CREATE ENDPOINT [en_service_broker_929368242990-321836]
	   STATE = STARTED
	   AS TCP (  LISTENER_PORT = 5122)
	   FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS  );

    else
		raiserror(
		'Error: An end point cannot be created. 
		 Please check if there is already one with same port.', 16,1)
 END
 else
    raiserror(
	'Error: An end point cannot be created. 
	 Please check if there is already one with same name.', 16,1)
END
GO
USE [temp_event_notification_test_929368242990-321836]

go
CREATE QUEUE [ent_929368242990-321836] ;  
GO  
CREATE SERVICE [ens_929368242990-321836]  
ON QUEUE [ent_929368242990-321836]  
(  
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]  
);  
GO  

CREATE ROUTE [enr_929368242990-321836]  
WITH SERVICE_NAME = 'ens_929368242990-321836',  
ADDRESS = 'LOCAL';  
GO  

CREATE EVENT NOTIFICATION [enen_929368242990-321836]  
ON DATABASE  
FOR ALTER_TABLE  
TO SERVICE 'ens_929368242990-321836',  'current database'

-- Test
-- Generate the events
if object_id('entt_929368242990-321836') is not null
drop table [entt_929368242990-321836]
go
create table [entt_929368242990-321836] (i int)
go
alter table [entt_929368242990-321836] add b int
go

-- verify/display that the event notification was captured
SELECT TOP (1000) *, casted_message_body = 
CASE message_type_name WHEN 'X' 
  THEN CAST(message_body AS NVARCHAR(MAX)) 
  ELSE message_body 
END 
FROM 
[temp_event_notification_test_929368242990-321836].[dbo].[ent_929368242990-321836] 

if @@ROWCOUNT = 0
   RAISERROR(
   'Error: Something is not right. Event notification was not captured.', 16,1)

else 
   SELECT 'Success!' Msg
go


-- clear the records from the queue
-- RECEIVE display the event as well as removes it from the queue

RECEIVE * FROM [ent_929368242990-321836]
go
-- verify that the queue is now empty

SELECT TOP (1000) *, casted_message_body = 
	CASE message_type_name WHEN 'X' 
	  THEN CAST(message_body AS NVARCHAR(MAX)) 
	  ELSE message_body 
	END 
FROM 
[temp_event_notification_test_929368242990-321836].[dbo].[ent_929368242990-321836]

/*
Since I am only testing, I am using the following code to clean up afterwards
*/

/* CLEAN UP
USE [temp_event_notification_test_929368242990-321836]
go
if exists 
(SELECT * FROM sys.event_notifications 
 where name = '[enen_929368242990-321836]' and parent_class_desc = 'DATABASE')

	DROP EVENT NOTIFICATION [enen_929368242990-321836]  ON DATABASE;  

go

if exists (select  * from sys.routes 
           where name = '[enr_929368242990-321836]' and address = 'LOCAL')

	DROP ROUTE [enr_929368242990-321836] 

if exists (SELECT * FROM sys.services 
           where name = 'ens_929368242990-321836')

	DROP SERVICE [ens_929368242990-321836]
GO
if exists (SELECT * FROM sys.service_queues 
           where name = 'ent_929368242990-321836' 
		     and schema_id = 1)

	DROP QUEUE [dbo].[ent_929368242990-321836]
GO
use master
go
if db_id('temp_event_notification_test_929368242990-321836') is not null
  drop database [temp_event_notification_test_929368242990-321836]
go

*/

Download this script at the GitHub:

SQL Server Event Notifications Example

What SQL Server Agent Alerts Do I have setup?

I am in a situation where I have to incorporate SQL Server Agent alerts in my monitoring and alerting strategy.

I needed a query (DMV) to get details on what alerts are setup on each server. And the result is the following query that I will run as a multi-server query.

SELECT a.[id]                        [alert_id], 
       a.[name]                      [alert_name], 
       a.[enabled]                   [is_alert_enabled], 
       o.[enabled]                   [is_operator_enabled], 
       o.[email_address]             [email_address], 
       o.[pager_address]             [pager_address], 
       o.[netsend_address]           [netsend_address],
       j.[name]                      [job_name], 
       a.[event_source]              [alert_event_source], 
       a.[event_category_id]         [alert_event_category_id], 
       sc.[name]                     [alert_category_name], 
       CASE sc.[category_class] 
         WHEN 1 THEN 'JOB' 
         WHEN 2 THEN 'ALERT' 
         WHEN 3 THEN 'OPERATOR' 
         ELSE '0' 
       END                           [alert_class_name], 
       sm.[description]              [alert_message_description],
       a.[event_id]                  [alert_event_id], 
       a.[message_id]                [alert_message_id], 
       a.[severity]                  [alert_severity], 
       a.[enabled]                   [alert_enabled], 
       a.[delay_between_responses]   [alert_delay_between_responses], 
       a.[last_occurrence_date]      [alert_last_occurrence_date], 
       a.[last_occurrence_time]      [alert_last_occurrence_time], 
       a.[last_response_date]        [alert_last_response_date], 
       a.[last_response_time]        [alert_last_response_time], 
       a.[notification_message]      [alert_notification_message], 
       a.[include_event_description] [alert_include_event_description], 
       a.[database_name]             [alert_database_name], 
       a.[event_description_keyword] [alert_event_description_keyword], 
       a.[occurrence_count]          [alert_occurrence_count], 
       a.[count_reset_date]          [alert_count_reset_date], 
       a.[count_reset_time]          [alert_count_reset_time], 
       a.[job_id]                    [alert_job_id], 
       a.[has_notification]          [alert_has_notification], 
       a.[flags]                     [alert_flags], 
       a.[performance_condition]     [alert_performance_condition], 
       a.[category_id]               [alert_category_id] 
FROM   msdb.dbo.sysalerts a 
       LEFT OUTER JOIN msdb.dbo.syscategories sc ON a.category_id = sc.category_id 
       LEFT OUTER JOIN msdb.dbo.sysnotifications sn ON ( a.id = sn.alert_id ) 
       LEFT OUTER JOIN msdb.dbo.sysoperators o ON ( o.id = sn.operator_id ) 
       LEFT OUTER JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id 
       LEFT OUTER JOIN msdb.dbo.sysmessages sm ON sm.error = a.message_id
   and sm.msglangid = SERVERPROPERTY('LCID')
ORDER  BY 1 

And here is the sample result:

Find The Most Cached Database In The Buffer Cache - The DMV Way

While there are many ways and criteria to find out what database is the most used, under optimized, trouble maker etc....., here is one more to find out the database/s using the most buffer cache.

SELECT 
CASE database_id   
        WHEN 32767 THEN 'ResourceDb'   
        ELSE db_name(database_id)   
        END AS database_name,
COUNT(*)AS cached_pages_count,
COUNT(*) / 128 / 1024 AS cache_size_gb
    
FROM sys.dm_os_buffer_descriptors  
GROUP BY DB_NAME(database_id) ,database_id  
ORDER BY cached_pages_count DESC; 

Here is the results I got on one of the production SQL Servers. 

Lucky for me, it turned out to be an extreme case. Now I knew where I should focus optimization efforts or even if that database belongs with rest of the databases on that same server.

I have tested this query for SQL 2008 (SP3) and up.

Hope you find this useful.

A simple powershell script to look up server hardware and OS information

This is a simple (IMHO) PowerShell script to query and display hardware and OS information from a remote computer.

It uses the CIM (Common Information Model) at first, as that is available since Powershell version 3 and is the recommended direction.  Please see the following article on why "we" should use CIM instead of the WMI.

https://devblogs.microsoft.com/scripting/should-i-use-cim-or-wmi-with-windows-powershell/

---

# Specify the server name here

$server         = "server1"


# pull all the information
$hardware         = Get-CimInstance -ClassName Win32_ComputerSystem -ComputerName $server
$OS               = Get-CimInstance -ClassName Win32_OperatingSystem -ComputerName $server
$CPU              = Get-CimInstance -ClassName Win32_Processor -ComputerName $server
$PhysicalMemory   = Get-CimInstance -ClassName CIM_PhysicalMemory -ComputerName $server
$Bios             = Get-CimInstance -ClassName Win32_BIOS -ComputerName $server

$total_memory = ($PhysicalMemory | measure-object -Property Capacity -sum).sum
$total_memory_gb = $total_memory / 1024 / 1024 / 1024

# build custom array to get some key properties in a single row
$server_summary = New-Object PSObject

Add-Member -inputObject $server_summary -memberType NoteProperty -Name Manufacturer -value $hardware.Manufacturer
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Model -value $hardware.Model
Add-Member -inputObject $server_summary -memberType NoteProperty -Name HypervisorPresent -value $hardware.HypervisorPresent
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Bios -value $Bios.Name
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OS -value $OS.Caption
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSArchitecture -value $OS.OSArchitecture
Add-Member -inputObject $server_summary -memberType NoteProperty -Name CPUs -value $CPU.count
Add-Member -inputObject $server_summary -memberType NoteProperty -Name PhySicalMemory_GB -value $total_memory_gb
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSVersionNumber -value $OS.Version
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMajorVersion -value $OS.ServicePackMajorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMinor -value $OS.ServicePackMinorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name LastBootUpTime -value $OS.LastBootUpTime

# Display the values

# First, lets up the buffer size first so we can see the complete output on the screen
$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 3000)

"summary"
"======="

$server_summary | ft -AutoSize

""
"Detailed Properties"
"==================="

"Hardware:"
$hardware       | ft -Property *

"Bios:"
$Bios           | ft -Property * 

"Operating System:"
$OS             | ft -Property *

"CPUs:" 
$CPU            | ft -Property * 

"Physical Memory:"
$PhysicalMemory | ft -property *

---

Caveat:

That worked on most servers but on some I ran into an error with the CIM.

So I tried the solution suggested by the error message, which is to run the winrm quickconfig on the remote computer.  That threw message that the "WinRM service is already running on this machine", so maybe there is a firewall that is blocking it.

So I decided to go back to the good old faithful WMI for those servers. The powershell methods are still interchangeable between CIM and WMI so all it took was to do a global search/ replace for  Get-CimInstance / Get-WmiObject.

# Specify the server name here

$server         = "server1"


# pull all the information
$hardware         = Get-WmiObject -ClassName Win32_ComputerSystem -ComputerName $server
$OS               = Get-WmiObject -ClassName Win32_OperatingSystem -ComputerName $server
$CPU              = Get-WmiObject -ClassName Win32_Processor -ComputerName $server
$PhysicalMemory   = Get-WmiObject -ClassName CIM_PhysicalMemory -ComputerName $server
$Bios             = Get-WmiObject -ClassName Win32_BIOS -ComputerName $server

$total_memory = ($PhysicalMemory | measure-object -Property Capacity -sum).sum
$total_memory_gb = $total_memory / 1024 / 1024 / 1024

# build custom array to get some key properties in a single row
$server_summary = New-Object PSObject

Add-Member -inputObject $server_summary -memberType NoteProperty -Name Manufacturer -value $hardware.Manufacturer
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Model -value $hardware.Model
Add-Member -inputObject $server_summary -memberType NoteProperty -Name HypervisorPresent -value $hardware.HypervisorPresent
Add-Member -inputObject $server_summary -memberType NoteProperty -Name Bios -value $Bios.Name
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OS -value $OS.Caption
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSArchitecture -value $OS.OSArchitecture
Add-Member -inputObject $server_summary -memberType NoteProperty -Name CPUs -value $CPU.count
Add-Member -inputObject $server_summary -memberType NoteProperty -Name PhySicalMemory_GB -value $total_memory_gb
Add-Member -inputObject $server_summary -memberType NoteProperty -Name OSVersionNumber -value $OS.Version
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMajorVersion -value $OS.ServicePackMajorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name ServicePackMinor -value $OS.ServicePackMinorVersion
Add-Member -inputObject $server_summary -memberType NoteProperty -Name LastBootUpTime -value $OS.LastBootUpTime

# Display the values

# First, lets up the buffer size first so we can see the complete output on the screen
$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 3000)

"summary"
"======="

$server_summary | ft -AutoSize

""
"Detailed Properties"
"==================="

"Hardware:"
$hardware       | ft -Property *

"Bios:"
$Bios           | ft -Property * 

"Operating System:"
$OS             | ft -Property *

"CPUs:" 
$CPU            | ft -Property * 

"Physical Memory:"
$PhysicalMemory | ft -property *

Simple enough?  Nah... but I am sticking with the title!

Download both versions of this script from GitHub:

PowerShell-WMI-Get-Hardware-OS-Specs

PowerShell-CMI-Get-Hardware-OS-Specs

Get email alert when number of queries waiting for CPU exceeds thresold

You may have situations where the CPU % usage is well below the alert threshold but still queries are running slow because they are waiting for CPU to be available.

This script creates an alert to send out email if the number of queries waiting for CPU exceeds the threshold.  Please update the value for the @operator variable to whatever is the email operator you have setup in the SQL Server Agent.

And since I am testing I am using the threshold value of 10. You may want to lower that after testing in your environment.

Lastly, Since I did not want to get bombarded with emails, I am using the 900 seconds (15 minutes) delay of between alert emails. Please feel free to adjust it to your needs.

USE [msdb]
GO
declare @operator varchar(500)             -- email operator name
declare @threshold int                     -- number of queries waiting for the CPU
declare @delay_between_email_alerts int    -- this value is in seconds 
declare @drop_alert_if_exists bit          -- drops and recreates the alert if already exists

--  Assign default values to variables
set @operator                   = 'DBA'
set @threshold                  = 10
set @delay_between_email_alerts = 900
set @drop_alert_if_exists       = 0


declare @sql_add_alert nvarchar(4000)
declare @sql_add_notification nvarchar(4000)
declare @sql_drop_alert_if_exists nvarchar(4000)

if @drop_alert_if_exists = 1
begin
  if exists (select * from msdb..sysalerts where name = 'Alert: Number of processes waiting for CPU exceeded thresold')
    EXEC msdb.dbo.sp_delete_alert @name=N'Alert: Number of processes waiting for CPU exceeded thresold'
end

set @sql_add_alert =
'EXEC msdb.dbo.sp_add_alert @name=N''Alert: Number of processes waiting for CPU exceeded thresold'', 
  @message_id=0, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=' + cast(@delay_between_email_alerts as nvarchar(10)) + ', 
  @include_event_description_in=1, 
  @category_name=N''[Uncategorized]'', 
  @performance_condition=N''Wait Statistics|Wait for the worker|Waits in progress|>|' + cast(@threshold as nvarchar(10)) + ''', 
  @job_id=N''00000000-0000-0000-0000-000000000000''
'
print @sql_add_alert 
exec(@sql_add_alert)
set @sql_add_notification = 
'EXEC msdb.dbo.sp_add_notification 
                @alert_name=N''Alert: Number of processes waiting for CPU exceeded thresold'', 
                @operator_name=N''' + @operator + ''', 
                @notification_method = 1
'
print @sql_add_notification
exec(@sql_add_notification)
go

Powershell one liner to export data directly from SQL Server to Excel

Most of the times I use the CSV files whenever I need to import or export SQL Server data. And then if I need to do further analysis on the data or simply beautify the results to share with the users or colleagues, I simply open the CSV file in Excel and do the rest of the work manually.

But what if I could just skip the CSV and export data directly to Excel format? Wouldn't that save me time and efforts and also help me to automate if I wanted to?

No surprise that there is indeed a powershell module for Excel at the Powershell Gallary site.

https://www.powershellgallery.com/packages/ImportExcel/5.2.0


You can import the module directly from there or do the manual download. I decided to use the import method. For that I would need to have the PSGallary as one of the registered repositories in the PowerShell

If you don't already have registered the Powershell Gallary as one of the repository, there are couple methods depending on the PowerShell version you have. I have the 5.x version and used the following command to register it.

Register-PSRepository -Default

If that does not work then try the full command:

Register-PSRepository -Name "myNuGetSource" -SourceLocation "https://www.myget.org/F/powershellgetdemo/api/v2" -PublishLocation "https://www.myget.org/F/powershellgetdemo/api/v2/Packages" -InstallationPolicy Trusted

Next I checked and it was now registered as an Un-Trusted source. Either I would need to add the -Force parameter to the Import-Module command or it will prompt me to confirm if I trust the source.

I decided to update the InstallationPolicy for it by issuing the command Set-PSRepository -Name "PSGallery" -InstallationPolicy Trusted to update that setting.

So far so good, great.

I copied the powershell command provided at the Powershell Gallary site  to import the module.

Right off the bat I get the following error message.

Now I could relaunch the powershell as Administrator but since I am only testing/playing around, I decided to take up the second suggestion in the error message and add the -Scope Currentuser to the import-module command.

Install-Module -Name ImportExcel -RequiredVersion 5.2.0 -Scope CurrentUser

Success!

And finally, the one liner to export the query output to Excel.

Send-SQLDataToExcel -Connection "Server=SQLServer1\SQL2016AG01;Trusted_Connection=True;" -MsSQLserver -SQL "select @@servername SQLServer, name DBName from sys.databases" -Path sc.xlsx

At the minimum, you need to specify the Connection string, connection type (MsSQLServer), SQL query and the Excel file name.

Here is another example of same with some additional parameters.

Send-SQLDataToExcel -Connection "Server=SQLServer1\SQL2016AG01;Trusted_Connection=True;" -MsSQLserver -SQL "select @@servername SQLServer, name DBName from sys.databases" -AutoSize -BoldTopRow -FreezeTopRow -TableName Test -TableStyle "Light13" -WorkSheetname Test -Path sc.xlsx

If you would like to explore all the parameters, options etc. of Send-SQLDataToExcel look up the complete help file in the powershell:

get-help Send-SQLDataToExcel

Partial Screen Shot:

And if you would like to explore all the things you can do with the ImportExcel module:

Get-Command -Module ImportExcel

OR

(Get-Module ImportExcel).ExportedCommands

I must admit that this is a two or three pages blog for something I claim to be a one-liner. But we need Excel module in order for the one liner to work. I did not have it in my PowerShell so I also included the steps I followed to get it.

SQL Server Metadata using Powershell

If you are new at your job or a new client you would like to gather and review the sql server meta data to understand the environment and get up to speed as quickly as possible.

Most of us are already familiar with the DMVs, system functions, procedures etc. to gather the SQL server metadata.

And if you want to gather the information for all your SQL servers, you could run a multi-server query against all through the Central Management Server. In fact, in newer versions of SSMS you don't even need the CMS, you just register all your sql instances in the Local Server Groups.

So from that perspective this post is not adding much values except maybe that it is another alternative to SSIS or other ETL tools to capture the meta data on regular basis.

If nothing else I hope you find this educational regarding how to use powershell to interact with sql servers.

<#
Export SQL Server properties, settings and configuration values to CSV files
#>

# name of the sql server instance you would like to gather information about
$instanceName = "SQLServer1\SQL2016AG01"

# the folder where the csv files will be stored, by default it will be the current folder
$folder_path = ""

if ($folder_path -eq "" -or $folder_path -eq $null)
{
        $folder_path = (Get-Item -Path ".\").FullName
}

"Folder Path: " + $folder_path

# variables to hold the names of the csv files where information will be stored

$export_file_name_info     = $folder_path + "\" + ($instanceName -replace '\\', '_') + '_info.csv'
$export_file_name_settings = $folder_path + "\" + ($instanceName -replace '\\', '_') + '_settings.csv'
$export_file_name_config   = $folder_path + "\" +  ($instanceName -replace '\\', '_') + '_config.csv'


# Load the sql server assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# connect to the sql instance and gather some basic info
# Notice that I have not specified SQL credentials as I would like to use the current user's windows credentials
$instance_object = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
$host_name       = $instance_object.NetName
$host_ip         = ([System.Net.Dns]::GetHostAddresses("$host_name") | ? {$_}).IPAddressToString
$db_count=$instance_object.Databases.count

"Host Name: "  + $host_name
"IP Address: " + $host_ip
"Databases: "  + $db_count

# Load up all the information into arrays
$sql_instance_info     = $instance_object.Information.Properties |Select Name, Value 
$sql_instance_settings = $instance_object.Settings.Properties |Select Name, Value 
$sql_instance_config   = $instance_object.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue

$sql_instance_info
$sql_instance_settings
$sql_instance_config

<#
Here I can simply export these arrays to respective CSV files but then it will be in the tabular format.
But I want the information to be displayed in wide (transposed) format.
That way if I want to gather information on multiple servers and stored in the same files, I can do that.

It takes little bit of additional coding but I think the results are worth it.
#>



# SQL Server properties

$info = New-Object -TypeName PSObject

$info | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$info | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$info | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count

foreach ($info_value in $sql_instance_info) 
{


    $Name  = $info_value.Name
    $value = $info_value.Value
    $info  | Add-Member -MemberType NoteProperty -Name $Name -Value $value
}


# SQL Server settings

$settings = New-Object -TypeName PSObject

$settings | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$settings | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$settings | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count


foreach ($settings_value in $sql_instance_settings) 
{
   $Name     = $settings_value.Name
   $value    = $settings_value.Value
   $settings | Add-Member -MemberType NoteProperty -Name $Name -Value $value
}


# SQL Server configuration (sp_configure)
# we need two custom objects here to store the configured as well as run values from sp_configure
$config_value   = New-Object -TypeName PSObject
$run_value      = New-Object -TypeName PSObject


$config_value | Add-Member -MemberType NoteProperty -Name ValueType -Value "Config Value"
$run_value    | Add-Member -MemberType NoteProperty -Name ValueType -Value "Run value"

$config_value | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$config_value | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$config_value | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count

$run_value    | Add-Member -MemberType NoteProperty -Name HostName -Value $host_name
$run_value    | Add-Member -MemberType NoteProperty -Name IPAddress -Value $host_ip
$run_value    | Add-Member -MemberType NoteProperty -Name NumberOfDatabases -Value $db_count


foreach ($c in $sql_instance_config) 
{
   $DisplayName  = $c.DisplayName
   $Description  = $c.Description
   $RunValue     = $c.RunValue
   $ConfigValue  = $c.ConfigValue
   $config_value | Add-Member -MemberType NoteProperty -Name $DisplayName -Value $ConfigValue
   $run_value    | Add-Member -MemberType NoteProperty -Name $DisplayName -Value $RunValue
   
   
   
}

# combine the config and run values in a single array
$config = @($config_value,$run_value)


# export the arrays into respective CSV files

$info     | Export-Csv -Path $export_file_name_info -NoTypeInformation
$settings | Export-Csv -Path $export_file_name_settings -NoTypeInformation
$config   | Export-Csv -Path $export_file_name_config -NoTypeInformation