SqlServerDsc SqlServerDsc: Reduce the amount of verbose messages generated in logs

A suggestion was raised to change many Write-Verbose calls to Write-Debug and just keep relevant messages for the user as Write-Verbose. Basically that would mean changing all Write-Verbose to Write-Debug in helper functions.

We should also change the issue template telling users to run the configuration using the following to get both the verbose and debug output.

Start-DscConfiguration -Path C:\temp\SqlDscTest -Wait -Force -Debug -Verbose

Jun 07 '20 09:06 johlju

A suggestion for the resource SqlDatabaseObjectPermission is to change verbose messages from this (making the others debug messages):

VERBOSE: Perform operation 'Invoke CimMethod' with following parameters, ''methodName' = SendConfigurationApply,'className' = MSFT_DSCLocalConfigurationManager,'namespaceName' = root/Microsoft/Windows/DesiredStateConfiguration'.
VERBOSE: An LCM method call arrived from computer SQLTEST10 with user sid S-1-5-21-2246212248-426023572-517575333-1441.
VERBOSE: [SQLTEST10]: LCM:  [ Start  Set      ]
VERBOSE: [SQLTEST10]: LCM:  [ Start  Resource ]  [[SqlDatabaseObjectPermission]Table1Permission]
VERBOSE: [SQLTEST10]: LCM:  [ Start  Test     ]  [[SqlDatabaseObjectPermission]Table1Permission]
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Determining the current state of the permissions for the database object 'dbo.Table1' of type 'Table' in the database 'AdventureWorks' for the instance 'sql2014'
 on the server 'testclu01a'. (SDOP0002)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Getting the current state of the permissions for the database object 'dbo.Table1' of type 'Table' in the database 'AdventureWorks' for the instance 'sql2014' on
the server 'testclu01a'. (SDOP0001)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Preferred module SqlServer found. (SQLCOMMON0023)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Importing PowerShell module 'SqlServer' with version '21.1.18080' from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18080\SqlServer.psm1'. (SQ
LCOMMON0025)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Connecting as current user 'CONTOSO\sqladmin' using integrated security. (SQLCOMMON0054)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Connected to SQL instance 'testclu01a\sql2014'. (SQLCOMMON0018)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Evaluating the state of the property 'Permission'. (SQLCOMMON0059)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Testing CIM instance 'DSC_DatabaseObjectPermission' with the key properties 'State="Grant"'. (SQLCOMMON0071)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Testing CIM instance 'DSC_DatabaseObjectPermission' with the key properties 'State="Deny"'. (SQLCOMMON0071)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] String value does not match. Current value is 'Present', but expected the value 'Absent'. (SQLCOMMON0063)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] The parameter 'Permission' is not in desired state. (SQLCOMMON0061)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] The permissions for the database object 'dbo.Table1' is not in desired state. (SDOP0003)
VERBOSE: [SQLTEST10]: LCM:  [ End    Test     ]  [[SqlDatabaseObjectPermission]Table1Permission]  in 2.6710 seconds.
VERBOSE: [SQLTEST10]: LCM:  [ Start  Set      ]  [[SqlDatabaseObjectPermission]Table1Permission]
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Getting the current state of the permissions for the database object 'dbo.Table1' of type 'Table' in the database 'AdventureWorks' for the instance 'sql2014' on
the server 'testclu01a'. (SDOP0001)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Connecting as current user 'CONTOSO\sqladmin' using integrated security. (SQLCOMMON0054)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Connected to SQL instance 'testclu01a\sql2014'. (SQLCOMMON0018)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Evaluating the state of the property 'Permission'. (SQLCOMMON0059)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Testing CIM instance 'DSC_DatabaseObjectPermission' with the key properties 'State="Grant"'. (SQLCOMMON0071)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Testing CIM instance 'DSC_DatabaseObjectPermission' with the key properties 'State="Deny"'. (SQLCOMMON0071)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] String value does not match. Current value is 'Present', but expected the value 'Absent'. (SQLCOMMON0063)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] The parameter 'Permission' is not in desired state. (SQLCOMMON0061)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Setting the desired permissions for the database object 'dbo.Table1'. (SDOP0009)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Found PowerShell module SqlServer already imported in the session. (SQLCOMMON0026)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Connecting as current user 'CONTOSO\sqladmin' using integrated security. (SQLCOMMON0054)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Connected to SQL instance 'testclu01a\sql2014'. (SQLCOMMON0018)
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] The permission state 'Grant' is in desired state for database object 'dbo.Table1'.
VERBOSE: [SQLTEST10]:                            [[SqlDatabaseObjectPermission]Table1Permission] Revoking permissions 'Delete,Alter' for the user 'TestAppRole' and the state 'Deny' for the database object 'dbo.Table1' of type 'Table' in the database 'Adventu
reWorks' (SDOP0008)
VERBOSE: [SQLTEST10]: LCM:  [ End    Set      ]  [[SqlDatabaseObjectPermission]Table1Permission]  in 0.3910 seconds.
VERBOSE: [SQLTEST10]: LCM:  [ End    Resource ]  [[SqlDatabaseObjectPermission]Table1Permission]
VERBOSE: [SQLTEST10]: LCM:  [ End    Set      ]
VERBOSE: [SQLTEST10]: LCM:  [ End    Set      ]    in  3.7810 seconds.

to this:

Importing PowerShell module 'SqlServer' with version '21.1.18080' from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18080\SqlServer.psm1'. (SQ
LCOMMON0025)
Connecting as current user 'CONTOSO\sqladmin' using integrated security. (SQLCOMMON0054)
Connected to SQL instance 'testclu01a\sql2014'. (SQLCOMMON0018)
The parameter 'Permission' is not in desired state. (SQLCOMMON0061)
The permissions for the database object 'dbo.Table1' is not in desired state. (SDOP0003)
The permissions for the database object 'dbo.Table1' is not in desired state. (SDOP0003)

Jun 07 '20 10:06 johlju

personally i would like to see all the changes a script makes. so something like :

Importing PowerShell module 'SqlServer' with version '21.1.18080' from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18080\SqlServer.psm1'. (SQ
LCOMMON0025)
Connecting as current user 'CONTOSO\sqladmin' using integrated security. (SQLCOMMON0054)
Connected to SQL instance 'testclu01a\sql2014'. (SQLCOMMON0018)
The parameter 'Permission' is not in desired state. (SQLCOMMON0061)
The permissions for the database object 'dbo.Table1' is not in desired state. (SDOP0003)
The permissions for the database object 'dbo.Table1' is not in desired state. (SDOP0003)
Revoking permissions 'Delete,Alter' for the user 'TestAppRole' and the state 'Deny' for the database object 'dbo.Table1' of type 'Table' in the database 'Adventu'

but i agree, a lot of the information that now is visible, should be in debug logging mode, or in normal logging mode.

maybe adapt the way dbatools does logging? everything goes to Write-Message, but with every message you have to specify an importance level (1-9)

Sep 14 '20 06:09 Fiander

maybe when doing all to Write-debug, also change the Write-verbose in Get-TargetResource? From Write-Verbose -Message ( $script:localizedData.GetConfiguration -f $InstanceName )

to if ( $((Get-Variable MyInvocation -Scope 1).Value.MyCommand.Name) -in ('Set-TargetResource', 'Test-TargetResource')) { Write-Verbose -Message ( $script:localizedData.GetConfiguration -f $InstanceName ) }

This would also reduce the number of messages. no more double entrys

Dec 14 '20 08:12 Fiander

@Fiander that is definitely an option to only see it connecting once, and have a Write-Debug that always output everything. Worth thinking about. And yes, it should show all actions it takes, like revoking.

The goal is limit what is outputted to the drives (C:\Windows\System32\Configuration\ConfigurationStatus) in a normal scenario but still me informative.

Dec 24 '20 10:12 johlju