取得 Power BI 後的 Model

感謝微軟 Egg 告知，Power BI Desktop 是在背後起一個 AS 服務，以如下的 Power BI Desktop 為例：

透過 PowerShell 查該 msmdsrv 的 Processid 後，透過 netstat –o 查聽的 port

而後透過 SSMS 連結 AS

P

就可以透過物件總管觀察該 Tabular Model

透過 SSDT 從 Server 讀回該 Model 定義會有如下的錯誤

可以產生 TMSL Script

而後再到 AS Tabular 貼上語法執行時，因為會多出 .tables[1].isPrivate、 model.tables[2].columns[3].variations…等屬性而無法部署到獨立的 AS 上，得到的錯誤訊息如下：

正在執行查詢 …
JSON DDL 要求失敗，並出現下列錯誤: Unrecognized JSON property: isPrivate. Check path ‘model.tables[1].isPrivate’, line 251, position 25.。
執行完成

透過 PowerShell 讀 MSMQ，以及在資料庫內，跨資料表與欄位找尋是否包含某個字串

朋友的需求：

$q= new-object System.Messaging.MessageQueue ‘機器名稱\private$\erptowf’
$msg=$q.Peek()

$s=new-object System.IO.StreamReader $msg.BodyStream

$s.ReadToEnd()

 

declare cur cursor
for select schema_name(o.schema_id) schName, object_name(c.object_id) tbName,c.name from sys.columns c
join sys.objects o on c.object_id=o.object_id
where TYPE_NAME(user_type_id) in (‘varchar’,’nvarchar’,’char’,’nchar’) and o.type=’u’ and o.is_ms_shipped=0

declare @sch sysname,@tb sysname,@col sysname
create table t(schemaname sysname,tablename sysname,columnname sysname)
open cur
fetch cur into @sch,@tb,@col
declare @sql nvarchar(max), @search nvarchar(100)=’58.’
while @@FETCH_STATUS =0
begin
    set @sql=’if exists(select * from [‘ + @sch + ‘].[‘ + @tb + ‘] where [‘ + @col + ‘] like “%’ + @search + ‘%") insert t values(“‘ +@sch + “‘,"‘ + @tb + “‘,"‘ + @col + “‘)’
    –print @sql
    exec(@sql)
    fetch cur into @sch,@tb,@col
end
close cur
deallocate cur
select * from t
drop table t

透過 PowerShell 測試網路穩定性，並記錄到檔案中

改寫自以下的 QA，因為它似乎有點問題

https://community.spiceworks.com/topic/337701-ping-via-powershell-log-results-with-timestamp

 

[CmdletBinding()]
Param (
    [int32]$Count = 5,
    [int32]$Waittime=1,
    [Parameter(ValueFromPipeline=$true)]
    [String[]]$Computers = @("www.hinet.net","127.0.0.1"),
    [String]$LogPath = "c:\temp\pinglog.csv"
)

#若是透過 Pipeline 進來，會每個元素執行 Process 一次
process
{
    $Ping = @()
    #Test if path exists, if not, create it
    If (-not (Test-Path (Split-Path $LogPath) -PathType Container))
    {   Write-Verbose "Folder doesn’t exist $(Split-Path $LogPath), creating…"
        New-Item (Split-Path $LogPath) -ItemType Directory | Out-Null
    }

    #Test if log file exists, if not seed it with a header row
    If (-not (Test-Path $LogPath))
    {   Write-Verbose "Log file doesn’t exist: $($LogPath), creating…"
        Add-Content -Value ‘"TimeStamp","Source","Destination","IPV4Address","Status","ResponseTime"’ -Path $LogPath
    }

    #Log collection loop
    Write-Verbose "Beginning Ping monitoring of $Comptuer for $Count tries:"
    for($i=0;$i -lt $Count; $i++)
    {
        foreach($Computer in $Computers)
        {
            $Ping = Get-WmiObject Win32_PingStatus -Filter "Address = ‘$Computer’" | Select @{Label="TimeStamp";Expression={Get-Date}},@{Label="Source";Expression={ $_.__Server }},@{Label="Destination";Expression={ $_.Address }},IPv4Address,@{Label="Status";Expression={ If ($_.StatusCode -ne 0) {"Failed"} Else {"Ok"}}},ResponseTime
            $Result = $Ping | Select TimeStamp,Source,Destination,IPv4Address,Status,ResponseTime | ConvertTo-Csv -NoTypeInformation
            $Result[1] | Add-Content -Path $LogPath
            Write-verbose ($Ping | Select TimeStamp,Source,Destination,IPv4Address,Status,ResponseTime | Format-Table -AutoSize | Out-String)
        }   
        Start-Sleep -Seconds $Waittime
    }
}

 

執行範例如下：

.\PingTest.ps1 -LogPath "C:\temp\pingres.txt" -Computers @("www.hinet.net","127.0.0.1") -verbose

透過 PowerShell FTP 下載某個目錄內的檔案

朋友的需求

FTP 利用了 .NET 產生了自我簽章的憑證，要求 FTP 存取時需啟動 SSL

#讀取定義設定
param(
[string]$Username = “SQL2014\a",
[string]$Password = “P@ssw0rd1234″,
[string]$folderPath="ftp://localhost/",
[string]$target = “c:\temp\ftp\",
[string]$ErrorLogFile="c:\temp\ftp\errorlog.txt",
[bool]$logDetail=$true, #是否要存 Exception 的細節
[bool]$ssl=$true
)

function Get-FtpDir ($url,$credentials,[bool]$ssl) {
$request = [Net.WebRequest]::Create($url)
if($ssl)
{
#不信任根憑證也強制下載
[Net.ServicePointManager]::ServerCertificateValidationCallback = {$true}
$request.EnableSsl = $True #ftp 可以啟動 SSL
}
$request.Method = [System.Net.WebRequestMethods+FTP]::ListDirectory
if ($credentials) { $request.Credentials = $credentials }
$response = $request.GetResponse()
$reader = New-Object IO.StreamReader $response.GetResponseStream()
$reader.ReadToEnd()
$reader.Close()
$response.Close()
}

$credential=new-object System.Net.NetworkCredential($Username, $Password)
$files=Get-FTPDir $folderPath $credential $ssl
$webclient = New-Object System.Net.WebClient
$webclient.Credentials =$credential
Foreach ($file in ($files.Split(“`r`n")  |  `

where {$_ -like “*.txt*"}))
{
$source=$folderPath+$file
$dest=$target+$file
try{
$WebClient.DownloadFile($source, $dest)
}
catch
{
$d=Get-Date
$sb= New-Object -TypeName “System.Text.StringBuilder"
[Void]$sb.Append(“***" + $source + “`t" + $d.ToString() + “`t" + $Message + “***" + “`r`n")
if($error -ne $null -and $logDetail)
{
foreach($err in $error)
{
if($err -ne $null){[Void]$sb.Append($err.ToString()+ “`r`n")}
if($err.Exception -ne $null){[Void]$sb.Append($err.Exception.ToString() + “`r`n")}
}
$error.Clear()
}
($sb.ToString() + “`r`n") | Out-File $ErrorLogFile -Append
Write-Host $sb.ToString() -ForegroundColor Red
}
}

查詢系統當下的 Process 並存到 DB

Param
(
    [string]$cnn="Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI",
    [int]$period=300
)
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $cnn
$sqlConnection.Open()

$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandText="
if not exists(select * from sys.objects where name=’tbProcess’ and type=’u’)
    create table tbProcess(pk int identity primary key,
    id int,
    name nvarchar(500),
    path nvarchar(1000),
    pollingTime datetime2(3) default(sysdatetime())
    )
"
$sqlCommand.Connection=$sqlConnection
$sqlCommand.ExecuteNonQuery()

$sqlCommand.CommandText="insert tbProcess(id,name,path) values(@id,@name,@path)"
$sqlPar=new-object Data.SqlClient.SqlParameter("id",[System.Data.SqlDbType]::Int)
$sqlCommand.Parameters.Add($sqlPar) | Out-Null
$sqlPar2=new-object System.Data.SqlClient.SqlParameter("name",[System.Data.SqlDbType]::NVarChar,500)
$sqlCommand.Parameters.Add($sqlPar2) | Out-Null
$sqlPar3=new-object System.Data.SqlClient.SqlParameter("path",[System.Data.SqlDbType]::NVarChar,1000)
$sqlCommand.Parameters.Add($sqlPar3) | Out-Null

$d=get-date
for([int]$i=0;$i -le $period;$i++ )
{
    $processes=Get-Process
    foreach($process in $processes)
    {
        $sqlCommand.Parameters[0].Value=$process.Id
        $sqlCommand.Parameters[1].Value=$process.Name
        if($process.Path -eq $null)
        {
            $sqlCommand.Parameters[2].Value=""
        }
        else
        {
            $sqlCommand.Parameters[2].Value=$process.path
        }
        $sqlCommand.ExecuteNonQuery() | Out-Null
    }
    [System.Threading.Thread]::Sleep(1000)
    Write-Host $i
}
$sqlConnection.Close()

將簡單的 SSIS 2014 封裝轉成 2012 版本

有 30 來個相同的封裝，僅是內含執行封裝工作呼叫其他的封裝，但用的是 VS2013/BIDS 2014 開發的，但實際的 SQL Server 卻是 2012，所以簡單寫段 PowerShell 取代

$source=’C:\temp\ssis\2014\Integration Services 專案1\’
$files=dir ($source + ‘*.dtsx’)
$dest=Join-Path $source ‘temp\’
foreach($file in $files)
{
(((((Get-Content $file.FullName -encoding utf8 ) `
-replace “Microsoft.Package", “SSIS.Package.3″) `
-replace “12.0.2456.0″,"11.0.5058.0″) `
-replace ‘DTS:VersionBuild="2″‘,’DTS:VersionBuild="1″‘) `
-replace ‘DTS:Name="PackageFormatVersion">8</DTS:Property>’,’DTS:Name="PackageFormatVersion">6</DTS:Property>’ `
-replace ‘Microsoft.ExecutePackageTask’,’SSIS.ExecutePackageTask.3′) `
| Out-File (join-path $dest $file.Name) -encoding utf8
}

若封裝複雜，或是 2012(Attribute centric)換 2008 R2(Element centric)就完全不可能這麼做了，升級或選用封裝開發版本務必小心

透過 PowerShell 部署 AS DB 和 rdl 到 SharePoint

SQL 制式教材內，透過 PowerShell 部署 AS DB 和 rdl 到 SharePoint 的 Script 可以參考：

#執行 xmla 部署 AS DB

Import-module sqlps
Import-module sqlascmdlets
Invoke-ASCmd -Inputfile:"$env:SUBDIR\SetupFiles\SetupSSAS.xmla" -Server:"localhost"

 

#部署  rdl 到 SharePoint

Add-PSSnapin Microsoft.SharePoint.PowerShell
$siteName = "http://localhost/sites/adventureworks"
$docLibName = "Reports"

$web = Get-SPWeb $siteName
$docLib = $web.Lists[$docLibName]
$rootFolder = $docLib.RootFolder
$folderURL = $rootFolder.Url
$folder = $web.GetFolder($folderURL)
foreach ($file in $folder.Files) {
  $docLib.Items.DeleteItemById($file.Item.Id)
}

$subs = @()

foreach($subfolder in $folder.SubFolders) {
  if ($subfolder.Name -ne "Forms") {
      $subs = $subs + $subfolder.Url
  }
}

foreach ($url in $subs) {
  $folder.SubFolders.Delete($url)
}

$folderName = "$env:SUBDIR\SetupFiles\Reports"
$files =  ([System.IO.DirectoryInfo] (Get-Item $folderName)).GetFiles() | ForEach-Object {
    $fileStream = ([System.IO.FileInfo] (Get-Item $_.FullName)).OpenRead()
    $content = New-Object byte[] $fileStream.Length
    $fileStream.Read($content, 0, [int]$fileStream.Length);
    $fileStream.Close()
    $spFile = $rootFolder.Files.Add($rootFolder.Url + "/" + $_.Name, $content, $true)
}

透過 PowerShell 搭配 WebDAV 與 HTTPS 上傳資料

設定 WebDAV:http://www.iis.net/configreference/system.webserver/webdav

IIS  建立自我簽署憑證並啟動 HTTPS

PowerShell：

$wc = new-object System.Net.WebClient
$wc.Credentials=new-object System.Net.NetworkCredential("account","password","domain")
$uri=https://server:port/
ls C:\temp\*.zip | foreach {
    $wc.UploadFile($($uri+$_.Name) ,"PUT", $_.FullName )
}

幾個透過 PowerShell 與 SMO 查詢 SQL 的範例

http://gallery.technet.microsoft.com/ScriptCenter/en-us/site/search?f%5B0%5D.Type=RootCategory&f%5B0%5D.Value=databases&f%5B1%5D.Type=SubCategory&f%5B1%5D.Value=sqlserver&sortBy=date&pageIndex=1

CodePlex 中 SQL Server PowerShell Extensions