John Bevan JohnLBevan

🏠

Working from home

Technical Architect & Software Developer. Primarily develops in PowerShell, T-SQL and C#.

JohnLBevan / RunPSAsAdmin.ps1

Created November 6, 2014 12:23

Start-Process powershell.exe -Credential "TestDomain\Me" -NoNewWindow -ArgumentList "Start-Process powershell.exe -Verb runAs"

JohnLBevan / Credential.ps1

Last active March 6, 2019 13:48

Get Current User's Credentials

	if ($promptForCredentialsInInteractive)
	{
	$user = $env:USERDOMAIN, $env:USERNAME -join '\'
	$cred = (get-credential -UserName $user -Message "Please enter the credentials you wish this script to use when accessing network resources")
	Write-Verbose ("User entered username '{0}'" -f $cred.UserName)
	}
	else
	{
	#this doesn't work as some people claim
	#$cred = [System.Net.CredentialCache]::DefaultCredentials

JohnLBevan / sp_axwho.sql

Created January 7, 2015 19:01

Investigate performance issues in AX. https://holsopple.wordpress.com/2011/02/18/sp_axwho-that-i-use/

	--https://holsopple.wordpress.com/2011/02/18/sp_axwho-that-i-use/

	USE [DynamicsTest]
	GO
	/**** Object: StoredProcedure [dbo].[SP_AXWHO] Script Date: 02/18/2011 14:32:11 ****/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	ALTER procedure [dbo].[SP_AXWHO] AS

JohnLBevan / GetRecentSQL.sql

Created January 9, 2015 18:33

Get Recently Executed SQL (in date range / ignoring some common bits)


	--get sql statements just executed; filtering out common junk
	SELECT top 100 deqs.last_execution_time AS [Time]
	, dest.TEXT AS [Query]
	, deqs.*
	FROM sys.dm_exec_query_stats AS deqs
	CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
	where deqs.last_execution_time between '2015-01-08 16:20:00' and '2015-01-08 16:22:00'
	and total_logical_writes > 0
	and dest.TEXT not like '--SQL%'

JohnLBevan / BatchFileDateFormat.bat

Last active August 29, 2015 14:14

Batch File Date Format

	::See http://www.robvanderwoude.com/datetimentparse.php for lots of useful info on dates in batch files.
	::The script below assumes a locale of UK (i.e. %date% gives DD/MM/YYYY).

	@echo off

	::Split date & time into component parts; assumes dd/mm/yyyy
	FOR /F "TOKENS=1,2,3 DELIMS=/ " %%A IN ('echo %Date%') DO (SET dd=%%A&SET mm=%%B&SET yyyy=%%C)
	FOR /F "TOKENS=1,2,3 DELIMS=:. " %%A IN ('echo %Time%') DO (SET hh=%%A&SET mi=%%B&SET ss=%%C)

	::join in order of greatest affector to least (i.e. a year is longer than a month, so comes first)

JohnLBevan / ExportDynamicsAX2012Security.sql

Created February 4, 2015 11:59

Export Dynamics AX 2012 Security


	SELECT UI.Name, UI.ID, SR.AOTName, SRC.DATAAREA
	FROM HarmonyLive.dbo.SecurityUserRole UR
	LEFT JOIN HarmonyLive_Model.dbo.SecurityRole SR ON SR.RecId = UR.SecurityRole
	LEFT JOIN HarmonyLive.dbo.UserInfo UI ON UI.Id = UR.User_ and UI.PARTITION = UR.PARTITION
	left join HarmonyLive.dbo.SecurityUserRoleCondition SRC on SRC.SECURITYUSERROLE = UR.RECID and SRC.PARTITION = UR.PARTITION
	WHERE UI.Enable = '1'
	ORDER BY UI.Name, SR.AOTName

JohnLBevan / Write-EventLog2.ps1

Created February 10, 2015 15:36

A wrapper for write-eventlog which given the -force parameter will first check and if needed create the log/source prior to writing to it. Also logged here: http://codereview.stackexchange.com/questions/80154/write-to-the-event-log-without-needing-to-check-whether-or-not-it-exists-by-use

	cls
	$myLog = 'Application'
	$mySource = 'My PS Script'
	$myEventId = 1
	[System.Diagnostics.EventLogEntryType]$myEntryType = [System.Diagnostics.EventLogEntryType]::Error
	$myMessage = 'This is a test message'
	$myMessage2 = 'This is a test message (2)'
	$myMessage3 = 'This is a test message (3)'

	function Write-EventLog2

JohnLBevan / Shorthand Unique Identifier.sql

Created February 19, 2015 14:33

T-SQL Shorthand Empty Unique Identifier From: http://stackoverflow.com/questions/3092999/check-empty-guid-in-sql


	declare @emptyGuid uniqueidentifier;
	--instead of having to type:
	set @emptyGuid= '{00000000-0000-0000-0000-000000000000}'
	--you can do:
	set @emptyGuid = cast(cast(0 as binary) as uniqueidentifier)
	--or even:
	set @emptyGuid = cast(0x0 as uniqueidentifier)

	--REF: http://stackoverflow.com/questions/3092999/check-empty-guid-in-sql

JohnLBevan / CalculatedProperties.ps1

Created February 26, 2015 11:59

Powershell Calculated Properties

Select-Object Name, @{Name="LabelHere";Expression={$_.Length * 100}}

JohnLBevan / TimeToRestore.sql

Created March 11, 2015 17:32

How long until a restore completes (NB: Script is easily adaptable to work for other running queries too)

	SELECT getutcdate() TimeNowUTC
	,d.PERCENT_COMPLETE AS [%Complete]
	,d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin
	,d.ESTIMATED_COMPLETION_TIME/60000 AS TimeRemainingMin
	,d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours
	,d.ESTIMATED_COMPLETION_TIME*0.00000024 AS TimeRemainingHours
	,s.text AS Command
	FROM sys.dm_exec_requests d
	CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) s
	WHERE d.COMMAND LIKE 'RESTORE DATABASE%'