Powershell Script for Microsoft SQL server

Reference:

Insert Data Into SQL Server Table Using PowerShell

Connect to SQL Server Database from PowerShell

 

#連線資料庫
$serverName = "SQL01"
$databaseName = "wiside"
$tableName = "dbo.raw_data"
$uid = 'sa'
$pwd = 'mypassword'

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$serverName';database='$databaseName';User ID = $uid; Password = $pwd;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection

$insertquery="INSERT INTO $tableName ([ID],[mac],[vendor],[datetime]) VALUES ('$ID','$mac','$vendor','$datetime')"

$Command.CommandText = $insertquery

$Command.ExecuteNonQuery()

$Connection.Close();

catch exception on powershell script

Reference: 

在 PowerShell 中使用 Try/Catch 捕捉錯誤訊息

try {
    # 主要程式邏輯
}
catch [System.NotSupportedException] {
    Write-Output "不支援"
}
catch [System.IO.DirectoryNotFoundException] {
    Write-Output "找不到資料夾"
}
catch {
    Write-Output "通用的例外處理"
}

LInux samba service on CentOS 7

Reference:

CentOS7安裝Samba（以yum的方式）

CentOS 7下Samba伺服器的安裝與配置

透過SAMBA讓CentOS與Windows共享目錄

Samba Server 安裝及設定

 

安裝：

yum install -y samba

設定：

cp /etc/samba/smb.conf /etc/samba/smb.conf.ori

vi /etc/samba/smb.conf

 

[global]
        workgroup = SAMBA
        security = user
        #passdb backend = tdbsam

        map to guest = bad user
        #printing = cups
        #printcap name = cups
        #load printers = yes
        #cups options = raw

[images]
        comment = public images
        path = /path/to/images
        browseable = Yes
        read only = Yes
        writable = Yes
        guest ok = Yes
        force user = smbuser

 

啟動：

systemctl enable smb.service ; systemctl enable nmb.service
systemctl restart smb.service ; systemctl restart nmb.service

 

設定使用者帳號：

user add smbuser

smbpasswd smbuser

chown -R smbuser /path/to/images

Powershell get webapi data

Reference:

Generating client code

PowerShell 輸出結果編碼問題

How to do Base64 Encoding and Decoding in PowerShell?

Encode / Decode .EXE into Base64

Unzip GZ files using Powershell

How to convert JSON file to CSV file using PowerShell?

PowerShell script to convert Complex Nested JSON to CSV file format

 

#檔案輸出指定為ascii
$PSDefaultParameterValues['*:Encoding'] = 'Ascii'

#暫存檔主檔名
$tmpfile = "E:\Tool\tmp"

#7z位置
$7z_path = 'C:\Program Files\7-Zip\7z.exe'

#用postman測試可以抓到資料後，postman可以產生對應的powershell code

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Authorization", "Token XXXXXXor8mWYh259gqa1ZcMa")

$response = Invoke-RestMethod 'https://backend.com.tw/blue/raw_data/?index=blue-04&time=2022-09-11T20:00:00,2022-09-11T21:59:59' -Method 'GET' -Headers $headers

#webapi的回應為 result: value，取出 value (base64格式)
$result_base64 = $response[0].result
$out_b64 = $tmpfile + ".b64"
$result_base64 | Out-File -FilePath $out_b64    #$out_b54 for debug

#decode base64 得到 .gz檔
$result_gzip = [Convert]::FromBase64String($result_base64)
$out_gz = $tmpfile + ".gz"
[IO.File]::WriteAllBytes($out_gz, $result_gzip)    #$out_gz for debug

#解壓縮 .gz
$argumentlist="x -y $($out_gz)"        # x 解壓縮，-y 回答 yes
start-process $7z_path -argumentlist $argumentlist    #解壓縮後的檔案為 $tmpfile

$out_csv = $tmpfile + ".csv"
(Get-Content $tmpfile | ConvertFrom-Json) | ForEach-Object {
    #$_.mac + "," + $_.vendor + "," + $_.datetime
    $_.address + "," + $_.company + "," + $_.upload_time
} | Out-File -FilePath $out_csv

 

Powershell script 登入網頁、執行指令 (未完成)

Reference:

Logging Into webpage with Invoke-WebRequest

Login and download a file in web using powershell

PowerShell Tip of the Week: Create Invoke-WebRequest from Chrome 

 

狀況：

有一支IPCAM的NTP有問題，這家廠商已經不提供韌體更新。

登入它的管理網頁有手動同步時間，這功能正常。
我想用powershell script登入IPCAM，再click "time sync"。

但是這一支IPCAM的登入網頁用javascript，我就卡住了.....
 

使用 Powershell ISE

 

$azr = Invoke-WebRequest -Uri "http://211.72.66.73:8120/" -SessionVariable sbv
$azr
$azr|gm
$azr.Forms
$dbform = $azr.Forms["myForm"]
$dbform.Fields
$dbform.Fields["Username"] = "username"
$dbform.Fields["Password"] = "password"
$dbform.Action
$r = Invoke-WebRequest -Uri "http://211.72.66.73:8120/" -WebSession $sbv -Method Post -Body $dbform.Fields

 

 

 

 

 

 

 

 

python ImportError: cannot import name 'Sequence' from 'collections'

Reference:

[FIXED] in VS Code ImportError: cannot import name 'Mapping' from 'collections'

 

錯誤訊息：

Traceback (most recent call last):
  File "C:\py\py_Server.py", line 27, in <module>
    from Etrigan import Etrigan, Etrigan_parser, Etrigan_check, Etrigan_job
  File "C:\py\Etrigan.py", line 12, in <module>
    from collections import Sequence
ImportError: cannot import name 'Sequence' from 'collections'

 

解法：

修改 C:\py\Etrigan.py 內容

原本

from collections import Sequence 

改為

from collections.abc import Sequence

 

 

 

在 Hyper-V 安裝 Windows 11

Windows 11 系統最低需求：

CPU: 64bit, 1GHz, core x 2

RAM: 4GB

Disk: 64GB

Boot: UEFI, TPM

以 Hyper-V 測試安裝，要選第2代。