PowerShell Get Local Machine FQDN Function

/ PowerShell Scripting /

The example function below retrieves the FQDN of the machine on which the script is running. The switch option swIsEmail can be activated to construct a noreply email address.

function fnGetFQDN() {
  param (
    [switch]$swIsEmail = $false
  )
  $oNETIP = [System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()
  if ($swIsEmail -eq $true) {
    [string]$Return1 = "noreply@" + $oNETIP.HostName + "." + $oNETIP.DomainName
  } else {
    [string]$Return1 = $oNETIP.HostName + "." + $oNETIP.DomainName
  }  
  return $Return1
}

PowerShell SQL Query Function

/ PowerShell Scripting /

The function below can be used to a query to a database. The script returns any error as a result (2 lines).

function fnSQLCmd([string]$sInstance, [string]$sQuery, [string]$sDatabase) {
  # Register SQPPS Module...
  $oSQLPSModule = Get-Module | Where {$_.name -eq "SQLPS" }
  if ($oSQLPSModule.Count -eq 0) {
    Import-Module "SQLPS" -WarningAction SilentlyContinue
  }

  if ($sDatabase) {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -Database " + $sDatabase + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  } else {
    [string]$sQuery = "Invoke-SqlCmd -ServerInstance " + $sInstance + " -QueryTimeout 30 -Query `"" + $sQuery + "`" ; if (`$? -eq `$false) { @{`"ERROR0`" = `$error[0]}; @{`"ERROR1`" = `$error[1]} }" 
  }
  return Invoke-Expression $sQuery
}

 

PowerShell Write Windows Eventlog

/ PowerShell Scripting /

The following function can be used to write to the Windows EventLog in a standard way. Some of the scripts on this site use this custum function.

function fnWriteEventLog() {
  param (
    [string]$sNode, 
    [string]$sMessage, 
    [string]$sEventLogSource = "DEFAULTLOGSOURCE", 
    [int]$iEventID = 1001, 
    [string]$sType = "Error"
  )
  if ( ($sNode) -and ($sMessage) ) {
    [string]$sFormatMessage = "Node: " + $sNode + "`r`n`r`n" + $sMessage
    Write-EventLog -LogName Application -Source $sEventLogSource -EntryType $sType -EventID $iEventID -Message $sFormatMessage
  } else {
    return "ERROR: fnWriteEventLog missing arguments..."
  }
}

 

PowerShell WebService Calls

/ PowerShell Scripting /

Performing a SOAP web service call using PowerShell. The methods can be requested by means of the command: $oWebSvc1 | Get-Member.

#WebService values...
[string]$sWebSvcWSDL = "https://host.domain.fqdn/Webservice1?wsdl"
[string]$sPostValue1 = "Waarde"
[string]$sPostValue2 = $true

#Create webservice object...
$oWebSvc1 = New-WebServiceProxy -Uri $sWebSvcWSDL

#POST values w. result in een object...
$oResult1 = $oWebSvc1.method1($sPostValue1, $sPostValue2)

#POST values w. result to RAW XML...
[xml]$xResult = $oWebSvc1.method1($sPostValue1, $sPostValues2) | ConvertTo-Xml
$xResult.get_OuterXML() | Out-File C:\Pad\Naar\Bestand.xml

PowerShell Add SQL Modules

/ PowerShell Scripting /

Modules can now be added by using Import-Module. The following example list all the available.

Get-Module -ListAvailable

Load the SQLPS module (if available)….

Import-Module -Name "SQLPS"

Before you can import the SQLPS powershell module you need to install SQL Management Studio or install the SQLSysCLRTypes, SharedManagementObjects and PowerShellTools packages from the SQL Server Feature Pack.

MSSQL Get Settings and Counters w. PowerShell

/ MSSQL Server / 
# Query Server Configuration...
Invoke-Sqlcmd -Query "SELECT * FROM sys.configurations WHERE description LIKE '%server memory%'"

# Server performance counter Page life expectancy (>(300s/4G))...
# Server performance counter Lazy writes/sec (take 2 samples with 1s between samples)...
Invoke-Sqlcmd -Query "SELECT object_name, counter_name, cntr_value from sys.dm_os_performance_counters WHERE counter_name IN ('Total Server Memory (KB)', 'Page life expectancy', 'Lazy writes/sec')"

MSSQL 2012 New T-SQL Features

/ MSSQL Server /

The example query below uses a new feature in SQL Server 2012 to divide the result of a query in pages. This particular example will start from the second record and retrieves the following 2 records.

SELECT [name], [value] FROM Table1
ORDER BY id
OFFSET 2 ROWS
FETCH NEXT 2 ROWS

The next query adds a derived column to the result with a cumulative sum based on a specific column. When using PARTITION BY the sum is only applied to the specific group.

SELECT [name], [value], 
SUM(value) OVER (
    --PARTITION BY [name] --SUM by column name only...
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cummulativeValue
FROM Table1

MSSQL combine SUMS from 2 tables

/ MSSQL Server /

Below query combines the SUMS of two tables into a total SUM for a specific column.

WITH combiTable ([Table], xName, xValue)
AS ( SELECT 1 AS [Table], name, SUM(value) FROM Table1 GROUP BY name
     UNION
     SELECT 2 AS [Table], name, SUM(value) FROM Table2 GROUP BY name )
SELECT xName AS Name, SUM(xValue) AS Total FROM combiTable
GROUP BY xName
ORDER BY Total DESC
GO