matt40k’s gists

matt40k / RenameFiles_ChangeSchema

Created May 27, 2015 21:26

Create SQL objects with the file name of NNNN_SCHEMA_OBJECTNAME.sql - this script allows you to bulk change the schema name on the files

	$dir = 'C:\svn\SQL'

	$files = Get-ChildItem $dir -Filter *.sql

	Foreach ($file in $files) {
	$old = $file.FullName
	$new = $old.Replace("_OldSchema_", "_NewSchema_")
	Rename-Item -path $old -NewName $new
	}

matt40k / Get-SVNLog

Created May 27, 2015 21:30

	([xml](svn log -v --xml)).log.logentry \| % {
	$entry = $_;
	$_.paths.path \| foreach {
	$obj = 1 \| select -Property Revision,Author,Date,Message,Action,FilePath;
	$obj.Revision = [int]$entry.Revision;
	$obj.Author = $entry.Author;
	$obj.Date = Get-Date $entry.Date;
	$obj.Message = $entry.msg;
	$obj.Action = $_.action;
	$obj.FilePath = $_.InnerText;

matt40k / ClearCache

Created May 27, 2015 21:34

Clears the Jasper / iTrent cache on the web tier

	$webserver = "localhost"
	$environment = "dev"
	$username = "user"
	$password = "pass"

	$url = 'http://' + $webserver + '/' + $enviroment + '_web/report/meta'

	Function ClearCache($type)
	{
	return Invoke-WebRequest -Uri $url/$type -Method GET -Headers @{"Authorization" = "Basic "+[System.Convert]::ToBase64String([System.Text.Encoding]::UTF8.GetBytes($username+":"+$password ))}

matt40k / UpdateToSmallInt

Created May 28, 2015 10:09

Loop through folder and update the files - changes from int to smallint - save a few bits ;)

	$dir = "C:\SVN\SQL"

	$files = Get-ChildItem $dir -Filter *.sql

	Foreach ($file in $files) {
	(Get-Content $file.FullName).replace('[ETL_Run_ID] [int]','[ETL_Run_ID] [smallint]') \| Set-Content $file.FullName
	}

matt40k / CognosReports_fromDb

Created May 28, 2015 13:15

Queries the Cognos BI database for the report and packages

	select
	names.NAME as 'ReportName'
	,packageNames.NAME as 'PackageName'
	,convert(nvarchar(512),reportpath.PATHS) as 'ReportPath'
	from
	CMOBJNAMES names
	inner join cmrefnoord1 ref on
	names.CMID = ref.CMID
	inner join CMOBJNAMES packageNames on
	packageNames.CMID = ref.REFCMID

matt40k / CognosBI_Audit_Reports

Created May 28, 2015 13:17

Queries the Cognos BI Audit DB for the reports executed

	SELECT
	CASE
	WHEN rr.[COGIPF_REPORTNAME] = 'null' THEN 'Unsaved report'
	WHEN rr.[COGIPF_REPORTNAME] = '' THEN 'Unsaved report'
	WHEN rr.[COGIPF_REPORTNAME] IS NULL THEN 'Unsaved report'
	WHEN rr.COGIPF_REPORTNAME = 'adHocReport' THEN 'Ad-Hoc'
	WHEN SUBSTRING(rr.[COGIPF_REPORTNAME], 1, 10) = 'reportView' then (SUBSTRING (rr.[COGIPF_REPORTNAME], 19, LEN (rr.[COGIPF_REPORTNAME]) - 20))
	WHEN SUBSTRING(rr.[COGIPF_REPORTNAME], 1, 6) = 'report' then (SUBSTRING (rr.[COGIPF_REPORTNAME], 15, LEN (rr.[COGIPF_REPORTNAME]) - 16))
	WHEN (SUBSTRING(rr.[COGIPF_REPORTNAME], 1, 5) = 'query') then (SUBSTRING (rr.[COGIPF_REPORTNAME], 14, LEN (rr.[COGIPF_REPORTNAME]) - 15 ))
	ELSE 'Unsaved report'

matt40k / CognosBI_Audit_QueryStudio

Created May 28, 2015 13:18

Queries the Cognos BI Audit DB for the QueryStudio executions

	SELECT
	CASE
	WHEN eq.[COGIPF_PACKAGE] = '' THEN ' '
	WHEN eq.[COGIPF_PACKAGE] = 'null' THEN ' '
	WHEN CHARINDEX('package', eq.[COGIPF_PACKAGE]) >0 THEN
	(SUBSTRING (eq.[COGIPF_PACKAGE], (CHARINDEX('package', eq.[COGIPF_PACKAGE]) +15), (CHARINDEX(']', (SUBSTRING (eq.[COGIPF_PACKAGE], (CHARINDEX('package', eq.[COGIPF_PACKAGE]) +17), 500))))))
	ELSE (SUBSTRING (eq.[COGIPF_PACKAGE], 1, LEN(eq.[COGIPF_PACKAGE])))
	END AS [PACKAGE]
	,eq.[COGIPF_TARGET_TYPE]
	,ul.[COGIPF_USERID]

matt40k / CensusExportRename

Created May 28, 2015 13:57

Rename the census export files so it can be loaded into Capita One

	using System;
	using System.IO;

	namespace BulkRename
	{
	class Program
	{
	static void Main(string[] args)
	{
	string inputDir = Properties.Settings.Default.InputDirectory;

matt40k / reset_sysman

Created May 28, 2015 14:00

Reset SIMS \ FMS sysman password

exec sims.db_p_reset_sysman_password

matt40k / UpdateS3AgentDS

Created May 28, 2015 14:01

Updates your SOLUS3 Agent config with the new Deployment server

	''
	' Name: UpdateS3AgentDS.vbs
	'
	' Description:
	' Updates your SOLUS3 Agent config with the new Deployment server
	'
	' Version: 1.1
	'
	' Usage: UpdateS3AgentDS.vbs <oldservername> <newservername>
	'

Matt Smith matt40k