andreasbotsikas

-- Download the country ip csv file from https://db-ip.com/db/ and save
-- the csv file in C:\temp\dbip-country.csv or change the script below

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GeoIps]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GeoIps](
	[fromIp] [binary](4) NOT NULL,
	[toIp] [binary](4) NOT NULL,
	[fromIpDisplay] [varchar](15) NOT NULL,
	[toIpDisplay] [varchar](15) NOT NULL,

andreasbotsikas

@ECHO OFF
@rem Check for visual studio tools if not already loaded
if defined VCINSTALLDIR goto GenerateCerts
@rem Ensure that visual studio is available
if not defined VS120COMNTOOLS goto msbuild-not-found
if not exist "%VS120COMNTOOLS%..\..\vc\vcvarsall.bat" goto msbuild-not-found
call "%VS120COMNTOOLS%..\..\vc\vcvarsall.bat"
@rem Check that vs is properly loaded
if not defined VCINSTALLDIR goto msbuild-not-found

andreasbotsikas

# Script based on http://stackoverflow.com/questions/383390/create-local-user-with-powershell-windows-vista
# Creates 30 users that are administrators and belong to the Remote Desktop Users group.

function create-account ([string]$accountName) {
 $hostname = hostname
 $comp = [adsi]"WinNT://$hostname"
 $user = $comp.Create("User", $accountName)
 $user.SetPassword("pass@word1")
 $user.SetInfo()
 $User.UserFlags[0] = $User.UserFlags[0] -bor 0x10000 #ADS_UF_DONT_EXPIRE_PASSWD flag is 0x10000

andreasbotsikas

@REM Works on vista+
@REM Set proxy 
netsh winhttp set proxy 127.0.0.1:8080
@REM Remove proxy
netsh winhttp reset proxy

andreasbotsikas

@ECHO OFF
@rem Check for visual studio tools if not already loaded
if defined VCINSTALLDIR goto GenerateCerts
@rem Ensure that visual studio is available
if not defined VS120COMNTOOLS goto msbuild-not-found
if not exist "%VS120COMNTOOLS%..\..\vc\vcvarsall.bat" goto msbuild-not-found
call "%VS120COMNTOOLS%..\..\vc\vcvarsall.bat"
@rem Check that vs is properly loaded
if not defined VCINSTALLDIR goto msbuild-not-found

andreasbotsikas

REM The following is a relic from the visual studio command line era.
call "%VS120COMNTOOLS%..\..\vc\vcvarsall.bat"

REM  https://docs.microsoft.com/en-us/previous-versions/dotnet/netframework-4.0/bfsktky3(v=vs.100)
makecert -n "CN=MyName" -r -h 0 -eku "1.3.6.1.5.5.7.3.3,1.3.6.1.4.1.311.10.3.13" -e 01/01/2200 -sv private.pvk cert.cer

REM In powershell you can just add the windows sdk path for pvk2pfx 
REM $Env:Path += "C:\Program Files (x86)\Windows Kits\10\bin\x64\"
pvk2pfx -pvk private.pvk -spc cert.cer -pfx certificate.pfx

andreasbotsikas

@ECHO OFF
@rem Check for visual studio tools if not already loaded
if defined VCINSTALLDIR goto GenerateCerts
@rem Ensure that visual studio is available
if not defined VS120COMNTOOLS goto msbuild-not-found
if not exist "%VS120COMNTOOLS%..\..\vc\vcvarsall.bat" goto msbuild-not-found
call "%VS120COMNTOOLS%..\..\vc\vcvarsall.bat"
@rem Check that vs is properly loaded
if not defined VCINSTALLDIR goto msbuild-not-found

andreasbotsikas

using System;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
/// <summary>
/// Moves blobs from one container to another
/// Install-Package WindowsAzure.Storage
/// </summary>
namespace MoveBlobs
{
    class Program

andreasbotsikas

# These databases contain a dbo.VfiTagNumHistory and a dbo.VfiTagStrHistory table
$Database = @("VfiTag","VfiTag_0_1427961614083","VfiTag_1427961614084_1397948399843","VfiTag_1427961614084_1403650799757","VfiTag_1427961614084_1409957999664","VfiTag_1427961614084_1415401199875","VfiTag_1427961614084_1420239599988","VfiTag_1427961614084_1423609199863","VfiTag_1427961614084_1425941999989")

# For each database 
$Database | foreach {
# export num history to csv using the bcp tool
$command = "cmd.exe /C ""C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\bcp.exe"" $_.dbo.VfiTagNumHistory out C:\Work\Personal\Optimus\$($_)_Num.csv /S. /c /t, -T"
Invoke-Expression -Command:$command
#export the string history to csv using the bcp tool
$command = "cmd.exe /C ""C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\bcp.exe"" $_.dbo.VfiTagStrHistory out C:\Work\Personal\Optimus\$($_)_Str.csv /S. /c /t, -T"

andreasbotsikas

-- To select min max dates
select 
  dateadd(S, min([time])/1000, '1970-01-01') as startdate, 
  dateadd(S, max([time])/1000, '1970-01-01') as enddate   
FROM HistoricTable

-- To add a calculated column per row
BEGIN TRANSACTION
GO
ALTER TABLE HistoricTable ADD