Microsoft Teams – Webhook Template Designer

With Microsoft Teams, it is possible to create webhooks. A webhook is an URL which is opened up to the internet and allows other applications to interact with the application.

In the case of teams, these webhooks allow notifications to be shown in Microsoft Teams.

The document which is sent to Teams via the webhook is in JSON format, and teams then takes this JSON and displays accordingly.

A basic notification may look like this:

This is created using the following JSON

{
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "type": "AdaptiveCard",
    "version": "1.0",
    "body": [
        {
            "type": "TextBlock",
            "id": "cccdb0fd-73f3-e358-6147-c7ed2254793e",
            "text": "This is a test",
            "wrap": true
        }
    ],
    "padding": "None"
}

To the more advanced:

Which is the following JSON:

{
	"type": "AdaptiveCard",
	"$schema": "https://adaptivecards.io/schemas/adaptive-card.json",
	"version": "1.0",
	"padding": "none",
	"body": [
		{
			"type": "Container",
			"style": "emphasis",
			"items": [
				{
					"type": "ColumnSet",
					"columns": [
						{
							"type": "Column",
							"width": "stretch",
							"items": [
								{
									"type": "TextBlock",
									"text": "**SATISFACTION SURVEY**",
									"weight": "bolder"
								}
							]
						},
						{
							"type": "Column",
							"items": [
								{
									"type": "Image",
									"horizontalAlignment": "Right",
									"verticalContentAlignment": "center",
									"url": "https://qms3.blob.core.windows.net/test/servicenow.png",
									"height": "20px",
									"altText": "Service Now Logo"
								}
							]
						}
					]
				}
			]
		},
		{
			"type": "Container",
			"padding": {
				"right": "padding",
				"left": "padding"
			},
			"items": [
				{
					"type": "TextBlock",
					"text": "Help us improve by taking our short satisfaction survey.",
					"weight": "bolder",
					"wrap": true
				}
			]
		},
		{
			"type": "Container",
			"padding": {
				"right": "padding",
				"left": "padding",
				"bottom": "padding"
			},
			"items": [
				{
					"type": "TextBlock",
					"text": "1\\. How satisfied were you with the response time to your incident?",
					"wrap": true
				},
				{
					"type": "Input.ChoiceSet",
					"id": "responseTime",
					"isRequired": true,
					"placeholder": "Select a rating",
					"choices": [
						{
							"title": "Very Satisfied",
							"value": "1"
						},
						{
							"title": "Not Satisfied",
							"value": "2"
						}
					]
				},
				{
					"type": "TextBlock",
					"text": "2\\. How courteous and respectful was the technician who responded to your query?",
					"wrap": true
				},
				{
					"type": "Input.ChoiceSet",
					"id": "technicianQuery",
					"isRequired": true,
					"placeholder": "Select a rating",
					"choices": [
						{
							"title": "Excellent",
							"value": "1"
						},
						{
							"title": "Very Good",
							"value": "2"
						}
					]
				},
				{
					"type": "TextBlock",
					"text": "3\\. Was the technician able to resolve your issue during their first consultation?",
					"wrap": true
				},
				{
					"type": "Input.ChoiceSet",
					"id": "firstConsultation",
					"isRequired": true,
					"isMultiSelect": false,
					"style": "expanded",
					"choices": [
						{
							"title": "Yes",
							"value": "yes"
						},
						{
							"title": "No",
							"value": "no"
						}
					]
				},
				{
					"type": "TextBlock",
					"text": "4\\. How satisfied are you with the overall service experience?",
					"wrap": true
				},
				{
					"type": "Input.ChoiceSet",
					"id": "OverallRating",
					"isRequired": true,
					"placeholder": "Select a rating",
					"choices": [
						{
							"title": "Excellent",
							"value": "1"
						},
						{
							"title": "Very Good",
							"value": "2"
						}
					]
				},
				{
					"type": "TextBlock",
					"text": "5\\. Is there any other comment you would like us to know?",
					"wrap": true
				},
				{
					"type": "Input.Text",
					"id": "comment",
					"isMultiline": true,
					"placeholder": "Add a comment"
				},
				{
					"type": "ActionSet",
					"actions": [
						{
							"type": "Action.Http",
							"method": "POST",
							"title": "Submit",
							"isPrimary": true,
							"body": "{\"responseTime\": \"{{responseTime.value}}\", \"technicianQuery\":\"{{technicianQuery.value}}\",  \"firstConsultation\":\"{{firstConsultation.value}}\", \"OverallRating\":\"{{OverallRating.value}}\" }",
							"url": "https://actionsplayground.azurewebsites.net/workspaces/IREXSqpHRk-hkfa7y7CAAw"
						}
					]
				}
			]
		}
	]
}

Designing these cards can be quite complex and time consuming, however the following websites are very useful, as these allow you to experiment with the JSON and visually show the results:

https://messagecardplayground.azurewebsites.net/

https://amdesigner.azurewebsites.net/

MS SQL – Normalise Case

Wow not wrote any notes for a very long time…!

Recently I’ve had an issue where some text was presented in a table, and I needed to normalise the text.

For example:

TESTiNG 123

Would need to become:

Testing 123

With thanks to Justin Cooney this was possible using the SQL below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION NormalizeCase (@InputString VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @OutputString VARCHAR(500)
DECLARE @Location INT

SET @Location = 1

-- Pre-set to a character string if the input string exists and has a length. otherwise the out string remains a NULL
IF DATALENGTH(@InputString) > 0
BEGIN
SET @OutputString = ''
END

WHILE @Location <= DATALENGTH(@InputString)
BEGIN
DECLARE @CheckCharacter VARCHAR(1)
DECLARE @PrevCheckCharacter VARCHAR(1)
DECLARE @OutCharacter VARCHAR(1)

-- Set the current character to lower case in case a capitalization condition is not met.
SELECT @CheckCharacter = LOWER(SUBSTRING(@InputString, @Location, 1))

SELECT @PrevCheckCharacter = SUBSTRING(@InputString, @Location - 1, 1)

-- Set the current letter to uppercase if the preceeding letter is a non-letter character
-- and the current character is a letter
IF @PrevCheckCharacter NOT LIKE '[a-z]'
AND @CheckCharacter LIKE '[a-z]'
BEGIN
SELECT @OutCharacter = UPPER(@CheckCharacter)
END
ELSE
BEGIN
SELECT @OutCharacter = @CheckCharacter
END

SET @OutputString = @OutputString + @OutCharacter
SET @Location = @Location + 1
END

RETURN @OutputString
END
GO

The code can be used as follows:

SELECT dbo.NormalizeCase('THIS IS a reaLLY GooD example')

With the result being:

This Is A Really Good Example

Migrating Servers to Azure – Activating Windows

Recently I have migrated several servers from Vultr to Microsoft Azure.

As part of this migration, windows became un-activated – most likely because Vultr runs it’s own KMS service and it is no longer able to contact the server:

In order to resolve this, I changed the product key to Microsoft’s KMS key.

Then the KMS server was changed to Microsoft’s, and activation was forced: (run from administrative command prompt)

cscript c:\windows\system32\slmgr.vbs /ipk <product key>
cscript c:\windows\system32\slmgr.vbs /skms kms.core.windows.net:1688
cscript c:\windows\system32\slmgr.vbs /ato

SQL Server – Identify Unused Indexes

Within SQL Server an index is used to optimise the reading of data from the database.

However the side effect of having such an index is reduced write speed for inserts and updates. This is because SQL Server will need to update and maintain the index.

Running the below SQL will highlight indexes which have not been used recently, allowing you to remove these indexes (if these are no longer required). This is sorted by the number of updates, as tables with high number of updates will most likely benefit from removal of this index.

SELECT TOP 200 o.NAME AS ObjectName
	,i.NAME AS IndexName
	,i.index_id AS IndexID
	,dm_ius.user_seeks AS UserSeek
	,dm_ius.user_scans AS UserScans
	,dm_ius.user_lookups AS UserLookups
	,dm_ius.user_updates AS UserUpdates
	,p.TableRows
	,'DROP INDEX ' + QUOTENAME(i.NAME) + ' ON ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (
	SELECT SUM(p.rows) TableRows
		,p.index_id
		,p.OBJECT_ID
	FROM sys.partitions p
	GROUP BY p.index_id
		,p.OBJECT_ID
	) p ON p.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID, 'IsUserTable') = 1
	AND dm_ius.database_id = DB_ID()
	AND i.type_desc = 'nonclustered'
	AND i.is_primary_key = 0
	AND i.is_unique_constraint = 0
	AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0
ORDER BY UserUpdates DESC

The following line can also be altered in order to include indexes which are rarely used (The code above is for unused), as there may be some indexes which are expensive to maintain, but only used once a day for example!

AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0

Example of result from this is shown below:

Windows 10 – Missing Taskbar Icons

Had a very odd issue with Windows 10 and taskbar menu items missing:

After lots of research, I’ve partially fixed this by running the following from an elevated command prompt:

for /R "%APPDATA%\Microsoft\Windows\Start Menu\Programs\" %f in (.lnk) do copy /b "%f"+,, "%f" 1>nul
for /R "%ALLUSERSPROFILE%\Microsoft\Windows\Start Menu\Programs\" %f in (
.lnk) do copy /b "%f"+,, "%f" 1>nul
taskkill /IM explorer.exe /F
explorer.exe

What this does is:

  • Update “Last Update Date” attribute on Start Menu Links
  • Kill Explorer process
  • Start Explorer process

The result being:

The one missing icon is for Microsoft Edge (also other Microsoft Store Apps have missing icons too, such as Calculator etc).

After lots of research, investigation and trial-and-error, I solved the problem by uninstalling “Google Drive File Stream” (plus I dont really use this anyways):

Tada Fixed:

Taskcentre – Recording Success / Failure using “Call Task” Tool

The “Call Task” tool in Taskcentre is a useful tool in calling other tasks.

The task can run in two modes:

  • Synchronous
  • Queue


Queue
This adds the task to the queue, to be scheduled at a future point in time

Synchronous
This queues the task and awaits a response.

Sometimes in Taskcentre you wish to know if a task you have called has failed. This is useful in cases where there’s a queue of objects which require some work completing and you are processing these in a batch. Examples include:

  • Invoices to send
  • Sales Orders to process
  • Records require uploading to website
  • etc

The issue with calling tasks Synchronously is that you cannot easily tell if the task being run has failed or not, however by analysing the output of the task, you can record this information and make decisions accordingly.

Here’s the output of calling this task:

<DataOutput>
	<OutputData>
		<SupplementaryReference />
		<TaskRunStatus>2</TaskRunStatus>
		<Folder>Tasks\Test\Looping options\</Folder>
		<TaskID>383</TaskID>
		<fileName>PO128.doc</fileName>
		<filePath>C:\Temp\File_Test</filePath>
	</OutputData>
</DataOutput>

The “TaskRunStatus” flag is the important part here. If the value is “2”, then the task completed successfully. Value “4” is a failure.

Using the “XML To RecordSet” tool, each results from the “Call Task” can be mapped to a Recordset:

From there, we can then use the “VBScript” tool to loop around all the RecordSet entries:

Here’s the VBScript:

If ThisStep.RecordSource("OutputData_TaskRunStatus") = 2 Then
  Variables("SuccessCount") = Variables("SuccessCount") + 1
Else
  Variables("FailCount") = Variables("FailCount") + 1
End If

From there, you can use these variables within your other steps. For example, here’s a HTML document which is posted to HipChat using code here.

Variables("Message") = "<B>Task Completion</B>"
Variables("Message") = Variables("Message") & "<BR/>Success: " & Variables("SuccessCount")
Variables("Message") = Variables("Message") & "<BR/>Fail: " & Variables("FailCount")

Using Dynamic Management Views to Recommend Indexes in SQL Server

Did you know that SQL Server provides several views which provide information on recommended indexes?

Using the following SQL, you can identify where SQL recommends indexes, ordered by potential performance boost from adding the suggested index.

Obviously you may wish to review these manually, rather than just blindly creating, but it gives you a pointer in the right direction!

It is also worth noting that this view is cleared when SQL Service is restarted, therefore it’s a good idea to restart the service, let the system run for a couple of hours or so, then grab the results and analyse further.

The code below works for all versions of SQL server after 2008, plus also Azure SQL Databases.

SELECT   migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
	,  'CREATE INDEX [missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle)   + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']'   + ' ON ' + mid.statement   + ' (' + ISNULL(mid.equality_columns, '')     + CASE 
		WHEN mid.equality_columns IS NOT NULL
			AND mid.inequality_columns IS NOT NULL
			THEN ','
		ELSE ''
		END     + ISNULL(mid.inequality_columns, '')   + ')'   + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
	,  migs.*
	,mid.database_id
	,mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Example results:

Azure SQL Database View Running Transactions / Blocking

With the Azure SQL Database, sometimes you want to view the current running transactions. Unfortunately there is no GUI as such like there is with normal SQL Server (right click, “Activity Monitor”), however it can be grabbed by executing sys.dm_exec_requests.

The following SQL is very useful as it shows details of all running transactions within the database, including highlighting if the transaction is blocked, or is causing blocking.

SELECT r.session_id
	,CASE 
		WHEN r.session_id IN (
				SELECT DISTINCT (blocking_session_id)
				FROM sys.dm_exec_requests
				)
			THEN 'Yes'
		ELSE ''
		END AS blocking
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,CASE r.transaction_isolation_level
		WHEN 0
			THEN 'Unspecified'
		WHEN 1
			THEN 'ReadUncommitted'
		WHEN 2
			THEN 'ReadCommitted'
		WHEN 3
			THEN 'Repeatable'
		WHEN 4
			THEN 'Serializable'
		WHEN 5
			THEN 'Snapshot'
		END AS transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT AS sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
GROUP BY r.session_id
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,r.transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT
ORDER BY r.total_elapsed_time

Azure SQL Database Index Size

Recently I’ve been doing some work on optimising indexes within Azure SQL Database. The following SQL from Daniel is very useful in showing the status of all the current indexes within a SQL Database. The SQL works for both SQL Server 2014 onwards, plus Azure SQL Database. I have not tested it on any other versions.

This code returns the following:

  • Object Type
  • Object Name
  • Index Name
  • Index Type
  • Partition (if any)
  • Compression
  • Data Space
  • Fill Factor
  • Rows
  • Reserved MB
  • In Row Used MB
  • Row Overflow Used MB
  • Out of Row Used MB
  • Total Used MB
SELECT --- Schema, type and name of object and index:
	       REPLACE(obj.type_desc, '_', ' ') AS objectType
	,       sch.[name] + '.' + obj.[name] AS objectName
	,       ISNULL(ix.[name], '') AS indexName
	,       ix.type_desc AS indexType
	,       --- Partition number, if there are partitions:
	      (CASE COUNT(*) OVER (
				PARTITION BY ps.[object_id]
				,ps.index_id
				)             
			WHEN 1
				THEN ''             
			ELSE CAST(ps.partition_number AS VARCHAR(10))             
			END) AS [partition]
	,       --- Storage properties:
	       p.data_compression_desc AS [compression]
	,       ds.[name] + ISNULL('(' + pc.[name] + ')', '') AS dataSpace
	,       STR(ISNULL(NULLIF(ix.fill_factor, 0), 100), 4, 0) + '%' AS [fillFactor]
	,       --- The raw numbers:
	       ps.row_count AS [rows]
	,       STR(1.0 * ps.reserved_page_count * 8 / 1024, 12, 2) AS reserved_MB
	,       STR(1.0 * ps.in_row_used_page_count * 8 / 1024, 12, 2) AS inRowUsed_MB
	,       STR(1.0 * ps.row_overflow_used_page_count * 8 / 1024, 12, 2) AS RowOverflowUsed_MB
	,       STR(1.0 * ps.lob_used_page_count * 8 / 1024, 12, 2) AS outOfRowUsed_MB
	,       STR(1.0 * ps.used_page_count * 8 / 1024, 12, 2) AS totalUsed_MB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.partitions AS p ON     ps.[partition_id] = p.[partition_id]
INNER JOIN sys.objects AS obj ON     ps.[object_id] = obj.[object_id]
INNER JOIN sys.schemas AS sch ON     obj.[schema_id] = sch.[schema_id]
LEFT JOIN sys.indexes AS ix ON     ps.[object_id] = ix.[object_id]
	AND     ps.index_id = ix.index_id
--- Data space is either a file group or a partition function:
LEFT JOIN sys.data_spaces AS ds ON     ix.data_space_id = ds.data_space_id
--- This is the partitioning column:
LEFT JOIN sys.index_columns AS ixc ON     ix.[object_id] = ixc.[object_id]
	AND     ix.index_id = ixc.index_id
	AND     ixc.partition_ordinal > 0
LEFT JOIN sys.columns AS pc ON     pc.[object_id] = obj.[object_id]
	AND     pc.column_id = ixc.column_id
--- Not interested in system tables and internal tables:
WHERE obj.[type] NOT IN (
		'S'
		,'IT'
		)
ORDER BY sch.[name]
	,obj.[name]
	,ix.index_id
	,p.partition_number;

Example Results:

Run Programs as a Domain User from None Domain Account

Following on from my blog post for running Microsoft Dynamics Nav as a different user here, I have done some further investigation of the runas command.

Typically the RunAs command is used for local authentication, however there is a little known switch which bypasses local authentication and uses it for network authentication only.

This switch allows you to run the Microsoft Dynamics NAV Client, Development Environment, or any other tools as a domain user, from a none domain user pc.

Example syntax:

runas.exe /netonly /user:<DOMAIN>\<USER> "C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailored Client\finsql.exe"

For ease of access, it’s also possible to update your shortcuts as appropriate:

Firstly, within the shortcut, amend the target by prefixing with:

runas.exe /netonly /user:<DOMAIN>\<USER>

Also ensure you “Change Icon”, otherwise you will update the icon to a square (not good for users):

After running the application, it now asks you for the credentials, then runs the application as normal: