Following WebSite Redirects

In order to check header values from a HTTP get sometimes you need to follow a Re-direct to be able to inspect those headers.

This post will show how you how to follow web site redirects:

First we start with creating a Web request object from System.Net.WebRequest

$url = "some url that has redirects"
$request = [System.Net.WebRequest]::Create($url)

With the object for webrequest in the variable request we can set the properties of that object and set the property AllowAutoRedirect to false.

$url = "https://www.google.com"
$request = [System.Net.WebRequest]::Create($url)

Now ask the $request object for a response.

$response=$request.GetResponse()

if($response.Statuscode.toString() -eq "Found")
{}

For each type of response (HttpWebResponse), we can find the methods and properties for that class and in turn take action on them. The first one to take action is the status code. There are a number of response types that you can check for in our case we want to check for any of the status’ that would cause a redirect. Which for the sake of demonstration we’ll start with “Redirect — 302”.

elseif($response.statuscode.tostring() -eq 'Redirect')
{
  $stream = $response.getresponseStream()
  $streamReader = [System.IO.Streamreader]::new($stream)
  $html = $streamReader.ReadtoEnd()
}

When a redirect is detected, the html that is with the redirect must be read to know where the redirect is going to. This is done through creation of a Stream reader to read the stream on the response object. To find the redirect path a small funtion is introduced to read the html object and find the path to where we need to follow the next redirect:

function Get-RedirectPath
{
param($html)
$h = convertfrom-html $html
if((($h.all.tags('Title)) | select-Object -expandProperty text).toString() -eq "Object moved")
{
   ($H.all.tags('a')) | select-object -first 1 -Expandproperty pathname
}
elseif((($h.all.tags('Title)) | select-Object -expandProperty text).toString() -eq "Document moved")
{
  ($H.all.tags('a')) | select-object -first 1 -Expandproperty pathname
}
else
{$null}
}

This function takes the html raw text and converts it to html. When converted to Html we can then look through the tags find the title and look for Document or object moved and get the value in the ‘a’ tag for where the redirect is to. In addition to following the redirect the other requirement was to get the headers in each redirect. Get-Headers is a function created for just this purpose.

Function Get-headers
{
   param([System.Net.HttpWebResponse]$HttpWebResponse)
   $headerHash = @{}
  foreach($header in $HttpWebResponse.headers)
  {
   $headerhash += @{$header = $response.GeteEsponseHeader($header)}
  }
 $headerHash
}

Get-Headers expects and object type of System.Net.HttpWebResponse. Since we have that object type we can call one of its methods to get the response headers. And add each header to a hash table.

If there are no headers then an empty Hashtable will be returned.

Lastly for pass back to the caller the other requirement was to include the $html, $headerhash, $redirect status code, and the date time. In a custom object so decisions could be made on whether or not to follow into the next redirect. Redirect status code is only added when we have a Redirect any other status code that isn’t a redirect will not contain this property.

The full completed scripts are found in this GIST:

Function Get-RedirectedUrl
{
param($url)
function Get-RedirectPath
{
param($html)
$h = convertfrom-html $html
if((($h.all.tags('Title')) | select-Object expandProperty text).toString() -eq "Object moved")
{
($H.all.tags('a')) | select-object first 1 Expandproperty pathname
}
elseif((($h.all.tags('Title')) | select-Object expandProperty text).toString() -eq "Document moved")
{
($H.all.tags('a')) | select-object first 1 Expandproperty pathname
}
else
{$null}
}
Function Get-headers
{
param([System.Net.HttpWebResponse]$HttpWebResponse)
$headerHash = @{}
foreach($header in $HttpWebResponse.headers)
{
$headerhash += @{$header = $response.GetresponseHeader($header)}
}
$headerHash
}
function ConvertFrom-html
{
param([string]$html)
$h = new-object com "HTMLFile"
$H.IHTMLDocument2_write($html)
$h
}
$request = [System.Net.WebRequest]::Create($url)
$response=$request.GetResponse()
$stream = $response.GetREsponseStream()
$streamREader = [System.IO.StreamREader]::new($stream)
$html = $streamREader.ReadToEnd()
if($response.Statuscode.toString() -eq "Found")
{
$headers = get-headers $response
$headers += @{html = Convertfrom-html $html}
$headers +=@{datetime = Get-Date}
[pscustomobject]$headers
}
elseif($response.Statuscode.toString() -eq "MovedPermanently")
{
$headers = get-headers $response
$headers +=@{redirect = $redirect}
$headers += @{html = Convertfrom-html $html}
$headers +=@{datetime = Get-Date}
[pscustomobject]$headers
}
elseif($response.Statuscode.toString() -eq "Redirect")
{
$headers = get-headers $response
$headers +=@{redirect = $redirect}
$headers += @{html = Convertfrom-html $html}
$headers +=@{datetime = Get-Date}
[pscustomobject]$headers
}
elseif($response.Statuscode.toString() -eq "OK")
{
$headers = get-headers $response
$headers +=@{redirect = $redirect}
$headers += @{html = Convertfrom-html $html}
$headers +=@{datetime = Get-Date}
[pscustomobject]$headers
}
if($streamREader)
{$streamREader.close()}
if($response)
{$response.close()}
}
function get-redirectedUrls
{
Param($url)
$urlcheckobject=@{}
$Uri = [uri]$url
$url2check = $url
do{
$value = get-redirectedUrl url $url2check
if($value.redirect)
{
$Url2check = "$(uri.scheme)://$($uri.Host)/$($value.redirect)"
$value |add-member MemberType NoteProperty name url value $Url2check
$urlcheckobject += $value
}
else
{
$url2check = $Null
}
}
until ($url2check -eq $null)
$urlcheckobject
}

Random Word Function

In order to test some websites I had need to have a function that produced random words.

I found this random word Json list. https://raw.githubusercontent.com/RazorSh4rk/random-word-api/master/words.json

And decided to wrap a small function around it:


function Get-RandomWord
{
if(-not $words)
{

$Script:words = (invoke-webrequest -Uri https://raw.githubusercontent.com/RazorSh4rk/random-word-api/master/words.json).content | convertfrom-json

}

$words["$(get-random -Maximum ($words.count))"]
}

 

if you wish to change to your own list of random words just change the json location to a local directory with a json file or a different web url that contains the json you want to Randomize.

 

Until then

I hope this helps someone

 

thom

User & Group information DirectoryServices.AccountManagement

When you are tasked with administering windows machines/servers more often than not you need to have Remote Server access tools for the version of Operating system you are supporting.  What do you do when you can’t get those tools installed for administrative or other reasons.  The best thing to do is to look for a means to do this in PowerShell.  This article describes how to find user and group information via the Dll’s that are available on windows.

All users/groups and objects in active directory have unique Security Identifier’s. To be able to locate and translate SID’s the class System.DirectoryServices.AccountManagement. The current logged in user’s sid can be retrieved using:

Add-Type -AssemblyName System.DirectoryServices.AccountManagement
[System.Security.Principal.WindowsIdentity]::getcurrent()

AuthenticationType : CloudAP
ImpersonationLevel : None
IsAuthenticated    : True
IsGuest            : False
IsSystem           : False
IsAnonymous        : False
Name               : somemachine\xxxxx
Owner              : S-1-5-24-2812812812-3456789123-4444444444-1001
User               : S-1-5-24-2812812812-3456789123-4444444444-1001
Groups             : {S-1-1-0, S-1-5-21-2242821411-3846915716-4272663257-1009, S-1-5-21-2242821411-3846915716-4272663257-1003, S-1-5-99-812...}
AccessToken        : Microsoft.Win32.SafeHandles.SafeAccessTokenHandle
Token              : 3688
UserClaims         : {http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name: somemachine\xxxxx, http://schemas.microsoft.com/ws/2008/06/identity/claims/primarysid: S-1-5-24-2812812812-3456789123-4444444444-1001, http://schemas.microsoft.com/ws/2008/06/identity/claims/groupsid: S-1-1-0, http://schemas.xmlsoap.org/ws/2005/05/identity/claims/denyonlysid: S-1-5-189...}
DeviceClaims       : {}
Claims             : {http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name: somemachine\xxxxx, http://schemas.microsoft.com/ws/2008/06/identity/claims/primarysid: S-1-5-24-2812812812-3456789123-4444444444-1001, http://schemas.microsoft.com/ws/2008/06/identity/claims/groupsid: S-1-1-0, http://schemas.xmlsoap.org/ws/2005/05/identity/claims/denyonlysid: S-1-5-189...}
Actor              :
BootstrapContext   :
Label              :
NameClaimType      : http://schemas.xmlsoap.org/ws/2005/05/identity/claims/name
RoleClaimType      : http://schemas.microsoft.com/ws/2008/06/identity/claims/groupsid

While the sid information has been redacted it is intact in terms of what would be displayed when calling the function.  it’s groups we are looking for turns out this function has a Groups method.


[System.Security.Principal.WindowsIdentity]::getcurrent().groups

BinaryLength AccountDomainSid Value
------------ ---------------- -----
12 S-1-1-0
28 redactedAcountSid RedactedValue

The return value I received was much larger for this on a Corporate network especially if the (current) user is in a number of Groups.

Now that we have the Group SID’s now on to the process of Converting the SID’s into a human readable form.  For the accounts discovered previously if we choose the first item [0] we can then see there is a .Translate on this item


([System.Security.Principal.WindowsIdentity]::getcurrent().groups[0]).translate

OverloadDefinitions
-------------------
System.Security.Principal.IdentityReference Translate(type targetType)

In order to do the translation we’ll need to specify the type that the dotnet class expects.  It expects a type of system.security.principal.ntaccount . This is the only class from the documentation that has the type expected.


([System.Security.Principal.WindowsIdentity]::getcurrent().groups[0]).translate([system.security.principal.ntaccount])

Value
-----
Everyone

The groups are known now to put this all together in a Foreach Loop to find out all the groups that the currently logged in user is a member of:


([System.Security.Principal.WindowsIdentity]::getcurrent().groups) | Foreach{( `

[System.Security.Principal.SecurityIdentifier]$_.value).Translate([system.security.principal.ntaccount])}

Value
-----
Everyone
.....(more groups Redacted)

With a few more updates this script can be modified to find per user when in a domain scenario. Or for local users:


Add-Type -AssemblyName System.DirectoryServices.AccountManagement
$userprincipal = ([System.DirectoryServices.AccountManagement.UserPrincipal]) -as [type]
$up = $userprincipal::FindByIdentity([System.DirectoryServices.AccountManagement.ContextType]::Machine,[System.DirectoryServices.AccountManagement.IdentityType]::SamAccountName,"somemachine\defaultAccount")

$up

GivenName :
MiddleName :
Surname :
EmailAddress :
VoiceTelephoneNumber :
EmployeeId :
AdvancedSearchFilter : System.DirectoryServices.AccountManagement.AdvancedFilters
Enabled : False
AccountLockoutTime :
.......
.......

ContextType : Machine
Description : A user account managed by the system.
DisplayName :
SamAccountName : DefaultAccount
UserPrincipalName :
Sid : S-1-5-xx-xxxxxxxx-xxxxxxxxxxx-xxxxxxxxxxxxxxxx-503
Guid :
....

Name : DefaultAccount

$up.GetGroups()

IsSecurityGroup : True
GroupScope : Local
Members : {DefaultAccount}
Context : System.DirectoryServices.AccountManagement.PrincipalContext
ContextType : Machine
Description : Members of this group are managed by the system.
DisplayName :
SamAccountName : System Managed Accounts Group
UserPrincipalName :
Sid : S-1-5-xx-xxx
Guid :
DistinguishedName :
StructuralObjectClass :
Name : System Managed Accounts Group

$up.getGroups().samacccountname

System Managed Accounts Group

Now if you need who’s in a group either Locally with: [System.DirectoryServices.AccountManagement.ContextType]::Machine

Domain with

ApplicationDirectory with

[ system.DirectoryServices.Accountmanagement.contextType ]::ApplicationDirectory

I hope this helps someone out.

 

Until then

 

Keep Scripting

 

Thom

Just Clip it

There was a question presented on StackOverflow about how do you pull an image from the Clipboard.  This article is about how this was done with two separate functions.

The first function Export-ClipItem is a function that detects what type of item is in the Clipboard.  To perform this function the built in cmdlets with powershell 5.1 work nicely.  This script however, is written assuming that an older version of Powershell maybe required.

The first thing that needs to be done is to get a windows form object:


Add-Type -AssemblyName System.Windows.Forms

$clipboard = [System.Windows.Forms.Clipboard]::GetDataObject()

The the Windows Forms object added the clipboard is now accessible.  If the $clipboard object is inspected:


$clipboard | get-member

TypeName: System.Windows.Forms.DataObject

Name MemberType Definition 
---- ---------- ---------- 
ContainsAudio Method bool ContainsAudio() 
ContainsFileDropList Method bool ContainsFileDropList() 
ContainsImage Method bool ContainsImage() 
ContainsText Method bool ContainsText(), bool ContainsText(System.Windows.Forms.TextDataFormat format) 

.....

GetAudioStream Method System.IO.Stream GetAudioStream() 
GetCanonicalFormatEtc Method int IDataObject.GetCanonicalFormatEtc([ref] GetFileDropList Method System.Collections.Specialized.StringCollection GetFileDropList() 

.....

GetImage Method System.Drawing.Image GetImage() 
GetText Method string GetText(), string GetText(System.Windows.Forms.TextDataFormat format) 

Based upon inspection there are several items that can be tested for with (Contains) and then items can be retrieved from the clipboard with (Get) methods.

Starting with Text it can be tested with ContainsText(). Retrieval of the Text can then be done with GetText()

 

if($clipboard.ContainsText())
{

$clipboard.GetText() | Out-File -FilePath "c:\temp\temp.txt"

}

ContainsImage() is a little bit trickier. Using getImage() the type of object it contains can be seen with gettype()


$clipboard.getimage().gettype()

IsPublic IsSerial Name BaseType 
-------- -------- ---- -------- 
True True Bitmap System.Drawing.Image

Since the Image retrieved from the clipboard is aready a System.Drawing.Image type.  That library has a Save() function.  it requires the path to save the image to and a type to save the image as.


$clipboard.getimage().save("c:\temp\clip.png",[System.Drawing.Imaging.ImageFormat]::Png)

Inspection of the Class for System.Drawing.Imaging.ImageFormat demonstrates there are a number of image formats that can be saved:


[System.Drawing.Imaging.ImageFormat].getproperties().Name
Png
Guid
MemoryBmp
Bmp
Emf
Wmf
Gif
Jpeg
Tiff
Exif
Icon

There are two other types of data that can be retrieved from the clipboard.

  1. ContainsFileDropList()

A file drop list is a collection of strings containing path information for files. The return from GetFileDropList is a StringCollection.  For this blog post it was chosen to just save the contents of the return as a txt file.


if($clipboard.ContainsFileDropList())

{

$clipboard.GetFileDropList()| Out-File -FilePath "c:\temp\temp.txt"

}

2. AudioFile

The last type that can be retrieved from the clipboard is an Audio file.  Performing the Export of the Audio will be presented in the next Blog post on this topic.


if($clipboard.ContainsAudio())
{
$clipboard.GetAudioStream()

#perform stream function to file ()
}

Now that we have the different types of output Text, Images, FileDropList, and at a later Date audio.  Now exploration of the File name function can be explored.  For this Script it was decided to write out a single file for each Clipboard operation.  This next explanation demonstrates how this was done.


function new-fileobject
{ param([string]$path, [string]$ext)

if(!(test-path $path -PathType Leaf -ErrorAction Ignore))
{
if([system.io.path]::GetExtension($path))
{
$filename = [system.io.path]::GetFileNameWithoutExtension([system.io.path]::GetFileName($path))
$path = [System.IO.Path]::GetDirectoryName($Path)
if(!(test-path $path -ErrorAction Ignore))
{new-item $path -Force}
}
else
{
$filename = [system.io.path]::getfilename($path)
$path = [system.io.path]::getdirectoryname($path)
if(!(test-path $path -ErrorAction Ignore))
{new-item $path -Force}
}
}
else
{
$filename = [system.io.path]::GetFileNameWithoutExtension([system.io.path]::GetFileName($path))
$path = [System.IO.Path]::GetDirectoryName($Path)
}
if([string]::IsNullOrEmpty($filename))
{
$filename = 'clip'
}
if(test-path "$path\$filename*")
{
[int]$lastFilenameNumber=((gi -path "$path\$filename*").BaseName | select-string '^\D+\d*$' | select -Last 1) -replace '^\D+',''
if($lastFilenameNumber)
{
$filename = "$filename$($lastFilenameNumber + 1)"
}
elseif(get-item "$path\$filename$($ext)")
{
$filename = "$filename$($lastFilenameNumber + 1)"
}
}
[pscustomobject]@{'filename' = $filename; 'path' = $path; 'ext' = $ext; 'fullpath' = "$path\$filename$($ext)"}
}

The object of this function was to take a file path ($Path) and an extension ($ext).

  1. Determine if the file & path exists. If path nor file exists create one or both.
  2. Determine if the file is already there. If it is present detect the current number of the file and add one.
  3. Return the filename, path, extension and fullpath.

With that function in place the script is now complete:


param([string]$path)

function export-clipItem
{
param($path)
Add-Type -AssemblyName System.Windows.Forms
#get the file extension and path

$clipboard = [System.Windows.Forms.Clipboard]::GetDataObject()

if ($clipboard.ContainsImage())
{ $ext = '.png'
$fileobject = new-fileobject $path $ext
[System.Drawing.Bitmap]$clipboard.getimage().Save("$($fileobject.fullpath)", [System.Drawing.Imaging.ImageFormat]::Png)
#https://social.msdn.microsoft.com/Forums/vstudio/en-US/9c2a05de-c680-4515-898a-e92f28eddbf9/retrieve-image-from-clipboard-and-save-it-in-different-formats
}
elseif($clipboard.ContainsText())
{
$ext = '.txt'
$fileobject = new-fileobject $path $ext
$clipboard.GetText() | Out-File -FilePath "$($fileobject.fullpath)"
}
elseif($clipboard.ContainsAudio())
{
$ext = '.wav'
$fileobject = new-fileobject $path $ext
$clipboard.GetAudioStream() #| out-file -FilePath "$($fileobject.fullpath)"
#create stream and output to file goes here.
}
elseif($clipboard.ContainsFileDropList())
{
$ext = '.txt'
$fileobject = new-fileobject $path $ext
$clipboard.GetFileDropList()| Out-File -FilePath "$($fileobject.fullpath)"
}
Write-Output "$($fileobject.fullpath)"
}

function new-fileobject
{ param([string]$path, [string]$ext)

if(!(test-path $path -PathType Leaf -ErrorAction Ignore))
{
if([system.io.path]::GetExtension($path))
{
$filename = [system.io.path]::GetFileNameWithoutExtension([system.io.path]::GetFileName($path))
$path = [System.IO.Path]::GetDirectoryName($Path)
if(!(test-path $path -ErrorAction Ignore))
{new-item $path -Force}
}
else
{
$filename = [system.io.path]::getfilename($path)
$path = [system.io.path]::getdirectoryname($path)
if(!(test-path $path -ErrorAction Ignore))
{new-item $path -Force}
}
}
else
{
$filename = [system.io.path]::GetFileNameWithoutExtension([system.io.path]::GetFileName($path))
$path = [System.IO.Path]::GetDirectoryName($Path)
}
if([string]::IsNullOrEmpty($filename))
{
$filename = 'clip'
}
if(test-path "$path\$filename*")
{
[int]$lastFilenameNumber=((gi -path "$path\$filename*").BaseName | select-string '^\D+\d*$' | select -Last 1) -replace '^\D+',''
if($lastFilenameNumber)
{
$filename = "$filename$($lastFilenameNumber + 1)"
}
elseif(get-item "$path\$filename$($ext)")
{
$filename = "$filename$($lastFilenameNumber + 1)"
}
}
[pscustomobject]@{'filename' = $filename; 'path' = $path; 'ext' = $ext; 'fullpath' = "$path\$filename$($ext)"}
}
export-clipItem $path

I hope this helps Someone

 

Until then

 

Keep Scripting

 

thom

 

 

Folder and File

After having been in the industry for a while. I must admit that I’ve never tried to create a folder and file with the same name in a folder.    Turns out that there is a rule that the same name can’t exist more than once in a folder or directory.  This applies to both windows and linux systems.

I found this out because in my code I didn’t specify the ItemType on New-Item Powershell Cmdlet.


new-item $home/.config/j
Mode                LastWriteTime         Length Name
----                -------------         ------ ----
-a----         1/4/2019   1:07 PM              0 j 

new-item $home/.config/j -itemtype container
new-item : An item with the specified name C:\Users\tschumacher\.config\j already exists.
At line:1 char:1
+ new-item $home/.config/j -itemtype container
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : ResourceExists: (C:\Users\tschumacher\.config\j:String) [New-Item], IOException
+ FullyQualifiedErrorId : DirectoryExist,Microsoft.PowerShell.Commands.NewItemCommand

To make sure I don’t commit this “cardinal sin” again. I wrote a small if statement to remedy my issue.  Since I wanted the item I’m passing to “j” to be a folder I check to see if it is a file. If it’s a file I delete it (forcibly) and then recreate it as a folder.


 if(!(test-path -PathType Container "$home/.config/j" -ErrorAction Ignore))
{
   if(test-path -path "$home/.config/j" -PathType Leaf)
    {
     Remove-Item "$home/.config/j" -force
    }
   new-item -ItemType Directory "$home/.config/j"
}

I purposefully have my folder seperators as / instead of \. Thus allowing for this script to work on both windows and linux.

I hope this helps someone.

 

until then

keep scripting

 

thom

Finding success with SCCM – trigger Schedule

If you’ve ever dealt with SCCM you’ll understand to get a client to forcibly download patches / software from SCCM you’ll need to call WMI to trigger a schedule.

The list of triggers can be found here. Trigger schedule

This post is about how to find / determine success for one of the triggers in this list:

To start with I chose to create a hashTable to hold the triggers with a name:


function New-CMSccmTriggerHashTable
{
    $Sccmhash =@{HardwareInventoryCollectionTask='{00000000-0000-0000-0000-000000000001}'
		SoftwareInventoryCollectionTask='{00000000-0000-0000-0000-000000000002}'
		HeartbeatDiscoveryCycle='{00000000-0000-0000-0000-000000000003}'
		SoftwareInventoryFileCollectionTask='{00000000-0000-0000-0000-000000000010}'
		RequestMachinePolicyAssignments='{00000000-0000-0000-0000-000000000021}'
		EvaluateMachinePolicyAssignments='{00000000-0000-0000-0000-000000000022}'
		RefreshDefaultMPTask='{00000000-0000-0000-0000-000000000023}'
		RefreshLocationServicesTask='{00000000-0000-0000-0000-000000000024}'
		LocationServicesCleanupTask='{00000000-0000-0000-0000-000000000025}'
		SoftwareMeteringReportCycle='{00000000-0000-0000-0000-000000000031}'
		SourceUpdateManageUpdateCycle='{00000000-0000-0000-0000-000000000032}'
		PolicyAgentCleanupCycle='{00000000-0000-0000-0000-000000000040}'
		CertificateMaintenanceCycle='{00000000-0000-0000-0000-000000000051}'
		PeerDistributionPointStatusTask='{00000000-0000-0000-0000-000000000061}'
		PeerDistributionPointProvisioningStatusTask='{00000000-0000-0000-0000-000000000062}'
		ComplianceIntervalEnforcement='{00000000-0000-0000-0000-000000000071}'
		SoftwareUpdatesAgentAssignmentEvaluationCycle='{00000000-0000-0000-0000-000000000108}'
		SendUnsentStateMessages='{00000000-0000-0000-0000-000000000111}'
		StateMessageManagerTask='{00000000-0000-0000-0000-000000000112}'
		ForceUpdateScan='{00000000-0000-0000-0000-000000000113}'
		AMTProvisionCycle='{00000000-0000-0000-0000-000000000120}'}
    $Sccmhash
}

Now any of triggers can be accounted for with this hashtable.


$sccmHash = New-CMSccmTriggerHashTable
$sccmHash['RequestMachinePolicyAssignments']
{00000000-0000-0000-0000-000000000021}

With a human readable form for the trigger a  function to Invoke the schedule for the trigger can be constructed:

function Invoke-CMRequestMachinePolicyAssignments
{
    param([Parameter(Mandatory=$true)]$computername, 
    [Parameter(Mandatory=$true)]$Path = 'c:\windows\ccm\logs',
            [pscredential]$credential)
    $Sccmhash = New-CMSccmTriggerHashTable
    if(Test-CCMLocalMachine $computername)
    {
        $TimeReference =(get-date)
    }
    else
    {
        $TimeReference = invoke-command -ComputerName $computername `
        -scriptblock {get-date} -credential $credential
    }
    if($credentials)
    {
        Invoke-WmiMethod -Class sms_client -Namespace 'root\ccm' 
       ​-ComputerName $computername -credential $credential 
      ​-Name TriggerSchedule 
      ​-ArgumentList "$($Sccmhash["RequestMachinePolicyAssignments"])" 
    }
    else
    {
        $SmsClient =[wmiclass]("\\$ComputerName\ROOT\ccm:SMS_Client")
        $SmsClient.TriggerSchedule`
        ($Sccmhash["RequestMachinePolicyAssignments"])
    }
    $RequestMachinePolicyAssignments = $false

    # can see when this is requested from the Policy agentlog:
    $RequestMachinePolicyAssignments = Test-CMRequestMachinePolicyAssignments`
     -computername $computername -Path $Path -TimeReference $TimeReference `
     -credential $credential

    [PSCustomObject]@{'RequestMachinePolicyAssignments' = $RequestMachinePolicyAssignments
                      'TimeReference' = ($TimeReference)}
}

Once the Request Machine Policy Assignments is triggered.  Another function is called.  This is where a search through the client logs determine success of the trigger invocation.

The value that determines success is Evaluation not required. No changes detected. Which can be found in the PolicyEvaluator.log

In order to find this value we first need to find out if the computer name that was passed is a local machine or a remote machine. This is done with the function Test-CCMLocalMachine. This function is used in both the invoke and the test to determine if excution is on the local machine or a remote machine.  To make sure when searching the log a $TimeReference is used. If it is passed in one of the parameters then that value is used to search through the log.  If it is not passed  the current time from the remote or local machine will be used.


function Test-CMRequestMachinePolicyAssignments
{
    param([Parameter(Mandatory=$true)]$computername, 
    [Parameter(Mandatory=$true)]$Path = 'c:\windows\ccm\logs'
    ,[datetime]$TimeReference,
    [pscredential] $credential)
    if ($TimeReference -eq $null)
    {
        if(Test-CCMLocalMachine $computername)
        {
            $TimeReference =(get-date)
        }
        else
        {
            [datetime]$TimeReference = invoke-command 
            ​-ComputerName $computername -scriptblock {get-date}
        }
    }
    $RequestMachinePolicyAssignments = $false
    # can see when this is requested from the Policy agentlog:
    Push-Location
    Set-Location c:
    if(Test-CCMLocalMachine $computername)
    {
        $p = Get-CMLog -path "$path\policyevaluator.log"
        $runResults = $P |Where-Object{$_.Localtime -gt $TimeReference} `
       | where-object {$_.message -like `
         "*Evaluation not required. No changes detected.*"}
    }
    else
    {
        $p = Get-CCMLog -ComputerName $computerName -path $Path -log policyevaluator -credential $credential
        $runResults = $P.policyevaluatorLog |Where-Object{$_.Localtime -gt $TimeReference} | where-object {$_.message -like "*Evaluation not required. No changes detected.*"}
    }
    Pop-Location
    #if in the 

        if($runResults)
        {
            $RequestMachinePolicyAssignments = $true
        }
    $RequestMachinePolicyAssignments
}

Finding this value in the PolicyEvaluator.log can take up to 45 minutes or more depending on the setup of your SCCM environment.

To allow for finding the value described above and two other triggers. The following script demonstrates its usage:

GetAvailUpdates.ps1

All of the functions shown above can be found in this github repository:

SCCMUtilities

 

I hope this helps someone

Until then

 

Keep Scripting

 

thom

PowerShell Conference Book – My Contribution

If you follow Twitter  like I do especially when it comes to Powershell,  you will already have noticed there is a new book out in the community called Powershell Conference Book.  There are some very sharp PowerShell MVPs/Experts/Enthusiasts that have posted a chapter about what they’ve spoken on in conferences or will speak about.

Author Website
Mike F Robbins https://mikefrobbins.com
Jeff Hicks https://jdhitsolutions.com
Michael Lombardi https://appoint.ly/t/michaeltlombardi
Adam Murry https://tikabu.com.au/blog/
Anthony Nocentino http://www.centinosystems.com
Brandin Olin https://devblackops.io
Brian Bunke https://www.brianbunke.com
Don Jones https://donjones.com
Doug Finke https://dfinke.github.io
Emin Atac https://p0w3rsh3ll.wordpress.com
Fred Weinman https://allthingspowershell.blogspot.com
Graham Beer https://graham-beer.github.io
Irwin Strachan https://pshirwin.wordpress.com
James Petty https://scriptautomaterepeat.com
Jeremy Murrah https://murrahjm.github.io
Juston Sider https://invoke-automation.blog
Luc Dekens http://www.lucd.info
Mark Kraus https://get-powershellblog.blogspot.com
Mark Wragg https://wragg.io
Mike Shepard https://powershellstation.com
Patrick Gruenhaer https://sid-500.com
Prateek Singh https://ridicurious.com
Rob Pleau https://ephos.github.io
Rob Sewell https://sqldbawithabeard.com
Thomas Lee https://tfl09.blogspot.com
Thomas Raynor https://workingsysadmin.com
Thom Schumacher https://powershellposse.com
Tim Curwick https://MadWithPowerShell.com
Tim Warner https://timwarnertech.com
Tommy Maynard https://tommymaynard.com
Tore Groneg https://asaconsultant.blogspot.com
Wesley Kirkland https://wesleyk.me

I am very honored that I was also chosen to write a chapter in this book.  My chapter is about the work I’ve done (SCCM), and dynamic parameters. I explore how to Parse a SCCM log file and then use it together with a Dynamic parameter.  Using this dynamic parameter you can more efficiently locate a log entry with a few keystrokes.

A huge thank you for the opportunity and to the organizers of this endeavor (Mike F Robbins, Jeff Hicks, Michael Lombardi).  Not only did they author their own chapter they also proof read and made sure the other authors were providing good content.

I would encourage everyone to go  get a copy.  There is a wealth of material in this book, while it is intended for experienced users, I believe that everyone can benefit from its content.

The proceeds from this book are going to a worthy cause:

The DevOps Collective, Inc. OnRamp Scholarship

Your contribution will help someone learn a new skill and move forward in their career.  I’m honored to be able to have contributed with a chapter in this Awesome book.

until then

Keep scripting

 

thom

Parsing CCM/Logs – Part 2 using a Dynamic Parameter

In the first Blog post Parsing CCM\Logs I showed you how I was able to get a Script from the community and make a few tweaks to allow for it to parse logs for CCM.  In this blog post I’m going to show you how I took the next step.   My next step was to use the parsing logic in a script and incorporate a Dynamic Parameter.

To begin with I wanted to have the user not have to go to the machine and find every log for CCM\logs and then type in the value of that log name.  For instance in my directory for c:\windows\ccm\logs there were 178 files with the .log extension.  Trying to create a Validate set for this many logs is also problematic.  So I chose the dynamic Parameter approach for this function.  Now on to the script.

The first portion of the script is standard parameter values.


param([Parameter(Mandatory=$true,Position=0)]$ComputerName = '$env:computername', [Parameter(Mandatory=$true,Position=1)]$path = 'c:\windows\ccm\logs')

The next portion of this script is where the “magic” is.  The next parameter -log is created dynamically from the first two variables ($computerName, $path).


DynamicParam
{
$ParameterName = 'Log'
if($path.ToCharArray() -contains ':')
{

$FilePath = "\\$ComputerName\$($path -replace ':','$')"
}
else
{
$FilePath = "\\$computerName\$((get-item $path).FullName -replace ':','$')"
}

$logs = Get-ChildItem "$FilePath\*.log"
$LogNames = $logs.basename

$logAttribute = New-Object System.Management.Automation.ParameterAttribute
$logAttribute.Position = 2
$logAttribute.Mandatory = $true
$logAttribute.HelpMessage = 'Pick A log to parse'

$logCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
$logCollection.add($logAttribute)

$logValidateSet = New-Object System.Management.Automation.ValidateSetAttribute($LogNames)
$logCollection.add($logValidateSet)

$logParam = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName,[string],$logCollection)

$logDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
$logDictionary.Add($ParameterName,$logParam)
return $logDictionary

}

To explain what is going on here I’ll start with the code I used to get me to this point. Martin Schvartzman wrote a great article that showed how to do most of what I’ve posted here Dynamic ValidateSet in a Dynamic Parameter. 

I’ll do my best to explain how his code works.  First step is the DynamicParam Statement.  This tells PowerShell that we are going to create a dynamic Parameter.  Very simply stated a dynamic parameter is a Parameter that is added at runtime only when needed.

The first thing that is done in this dynamic parameter is to create a Runtime Defined Parameter Dictionary.  In order to add a runtime parameter we have to define the parameter and it’s attributes and then add it to the collection to return to the runtime so it will be added properly.

<pre>System.Management.Automation.RuntimeDefinedParameterDictionary</pre>

This next portion of the code creates a object that will contain our parameter attributes.


$logAttribute = New-Object System.Management.Automation.ParameterAttribute

For the purposes of this script we are only going to make the parameter mandatory, set it’s position in the pipeline, and create a help message.  There are other items that can be defined if required.  We can see this by getting the members of the $logAttribute:


$logAttribute  | get-member -properties

TypeName: System.Management.Automation.ParameterAttribute

Name MemberType Definition
---- ---------- ----------
DontShow Property bool DontShow {get;set;}
HelpMessage Property string HelpMessage {get;set;}
HelpMessageBaseName Property string HelpMessageBaseName {get;set;}
HelpMessageResourceId Property string HelpMessageResourceId {get;set;}
Mandatory Property bool Mandatory {get;set;}
ParameterSetName Property string ParameterSetName {get;set;}
Position Property int Position {get;set;}
TypeId Property System.Object TypeId {get;}
ValueFromPipeline Property bool ValueFromPipeline {get;set;}
ValueFromPipelineByPropertyName Property bool ValueFromPipelineByPropertyName {get;set;}
ValueFromRemainingArguments Property bool ValueFromRemainingArguments {get;set;}

Since we are going to need this set of attributes in our parameter we need to add this to the Attribute collection ($logCollection) that will in turn be added to the runtime Parameter $logParam.

Next we’ll create our Validate set item from the list of logs on the remote machine which was gathered with the FilePath variable then added to a new object that will contain our ValidateSet attributes. Then add it to our LogCollection.


$FilePath = "\\$ComputerName\$($path -replace ':','$')"

<br>

$logValidateSet = New-Object System.Management.Automation.ValidateSetAttribute($LogNames)

 $logCollection.add($logValidateSet)

Finally we’ll add our parameter name and LogCollection to a Runtime Defined parameter.  Then put this all in our Runtime Defined Parameter Dictionary. Then hand it back to PowerShell.


$logParam = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName,[string],$logCollection)

$logDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
 $logDictionary.Add($ParameterName,$logParam)
 return $logDictionary

Now  that we have the Full explanation of the Dynamic Parameter we can stitch our previous Log Parser together with this function to give us back any one of the logs on our remote machine.  We’ll put this in our Process block of our function:


 $sb2 = "$((Get-ChildItem function:get-cmlog).scriptblock)`r`n"
 $sb1 = [scriptblock]::Create($sb2)
 $results = Invoke-Command -ComputerName $ComputerName -ScriptBlock $sb1 -ArgumentList "$path\$log.log"
 [PSCustomObject]@{"$($log)Log"=$results}

Now when we call Get-CcmLog we’ll get a return with a parsed log that has Log appended in the object name.

dynparam3

Full code is posted in a gist here:

function Get-CMLog
{
<#
.SYNOPSIS
Parses logs for System Center Configuration Manager.
.DESCRIPTION
Accepts a single log file or array of log files and parses them into objects. Shows both UTC and local time for troubleshooting across time zones.
.PARAMETER Path
Specifies the path to a log file or files.
.INPUTS
Path/FullName.
.OUTPUTS
PSCustomObject.
.EXAMPLE
C:\PS> Get-CMLog -Path Sample.log
Converts each log line in Sample.log into objects
UTCTime : 7/15/2013 3:28:08 PM
LocalTime : 7/15/2013 2:28:08 PM
FileName : sample.log
Component : TSPxe
Context :
Type : 3
TID : 1040
Reference : libsmsmessaging.cpp:9281
Message : content location request failed
.EXAMPLE
C:\PS> Get-ChildItem -Path C:\Windows\CCM\Logs | Select-String -Pattern 'failed' | Select -Unique Path | Get-CMLog
Find all log files in folder, create a unique list of files containing the phrase 'failed, and convert the logs into objects
UTCTime : 7/15/2013 3:28:08 PM
LocalTime : 7/15/2013 2:28:08 PM
FileName : sample.log
Component : TSPxe
Context :
Type : 3
TID : 1040
Reference : libsmsmessaging.cpp:9281
Message : content location request failed
.LINK
http://blog.richprescott.com
#>
param(
[Parameter(Mandatory=$true,
Position=0,
ValueFromPipelineByPropertyName=$true)]
[Alias("FullName")]
$Path,
$tail =10
)
PROCESS
{
if(($Path -isnot [array]) -and (test-path $Path PathType Container) )
{
$Path = Get-ChildItem "$path\*.log"
}
foreach ($File in $Path)
{
if(!( test-path $file))
{
$Path +=(Get-ChildItem "$file*.log").fullname
}
$FileName = Split-Path Path $File Leaf
if($tail)
{
$lines = Get-Content Path $File tail $tail
}
else {
$lines = get-Content path $file
}
ForEach($l in $lines ){
$l -match '\<\!\[LOG\[(?<Message>.*)?\]LOG\]\!\>\<time=\"(?<Time>.+)(?<TZAdjust>[+|-])(?<TZOffset>\d{2,3})\"\s+date=\"(?<Date>.+)?\"\s+component=\"(?<Component>.+)?\"\s+context="(?<Context>.*)?\"\s+type=\"(?<Type>\d)?\"\s+thread=\"(?<TID>\d+)?\"\s+file=\"(?<Reference>.+)?\"\>' | Out-Null
if($matches)
{
$UTCTime = [datetime]::ParseExact($("$($matches.date) $($matches.time)$($matches.TZAdjust)$($matches.TZOffset/60)"),"MM-dd-yyyy HH:mm:ss.fffz", $null, "AdjustToUniversal")
$LocalTime = [datetime]::ParseExact($("$($matches.date) $($matches.time)"),"MM-dd-yyyy HH:mm:ss.fff", $null)
}
[pscustomobject]@{
UTCTime = $UTCTime
LocalTime = $LocalTime
FileName = $FileName
Component = $matches.component
Context = $matches.context
Type = $matches.type
TID = $matches.TI
Reference = $matches.reference
Message = $matches.message
}
}
}
}
}
function Get-CCMLog
{
param([Parameter(Mandatory=$true,Position=0)]$ComputerName = '$env:computername', [Parameter(Mandatory=$true,Position=1)]$path = 'c:\windows\ccm\logs')
DynamicParam
{
$ParameterName = 'Log'
if($path.ToCharArray() -contains ':')
{
$FilePath = "\\$($ComputerName)\$($path -replace ':','$')"
}
else
{
$FilePath = "\\$($ComputerName)\$((get-item $path).FullName -replace ':','$')"
}
$logs = Get-ChildItem "$FilePath\*.log"
$LogNames = $logs.basename
$logAttribute = New-Object System.Management.Automation.ParameterAttribute
$logAttribute.Position = 2
$logAttribute.Mandatory = $true
$logAttribute.HelpMessage = 'Pick A log to parse'
$logCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
$logCollection.add($logAttribute)
$logValidateSet = New-Object System.Management.Automation.ValidateSetAttribute($LogNames)
$logCollection.add($logValidateSet)
$logParam = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName,[string],$logCollection)
$logDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
$logDictionary.Add($ParameterName,$logParam)
return $logDictionary
}
begin {
# Bind the parameter to a friendly variable
$Log = $PsBoundParameters[$ParameterName]
}
process {
$sb2 = "$((Get-ChildItem function:get-cmlog).scriptblock)`r`n"
$sb1 = [scriptblock]::Create($sb2)
$results = Invoke-Command ComputerName $ComputerName ScriptBlock $sb1 ArgumentList "$path\$log.log"
[PSCustomObject]@{"$($log)Log"=$results}
}
}

view raw
get-ccmlog.ps1
hosted with ❤ by GitHub

Parsing CCM\Logs

If you’ve ever worked with Configuration manager you’ll understand that there are quite a few logs on the Client side.  Opening and searching through them for actions that have taken place can be quite a task.  I needed to find when an item was logged during initial startup/build of a vm.  So I sought out tools to parse these logs to find out the status of  Configuration Manager client side. This post is about the tools/scripts I found and what I added to them to make it easier to discover and parse all the log files.

I started with the need to be able to just parse the log files.  I discovered that Rich Prescott in the community had done the work of parsing these log files with this script:

http://blog.richprescott.com/2017/07/sccm-log-parser.html

With that script in had I made two changes to the script.  The first change was to allow for all the files in the directory to be added to the return object.

 if(($Path -isnot [array]) -and (test-path $Path -PathType Container) )
{
$Path = Get-ChildItem "$path\*.log"
}

The second change allowed for the user to specify a tail amount. This allows for just a portion of the end of the log to be retrieved instead of the entire log.   That script can be found on one of my gists at the Tail end of this article.

 if($tail)
{
$lines = Get-Content -Path $File -tail $tail
}
else {
$lines = get-Content -path $file
}
ForEach($l in $lines )

 

function Get-CMLog
{
<#
.SYNOPSIS
Parses logs for System Center Configuration Manager.
.DESCRIPTION
Accepts a single log file or array of log files and parses them into objects. Shows both UTC and local time for troubleshooting across time zones.
.PARAMETER Path
Specifies the path to a log file or files.
.INPUTS
Path/FullName.
.OUTPUTS
PSCustomObject.
.EXAMPLE
C:\PS> Get-CMLog -Path Sample.log
Converts each log line in Sample.log into objects
UTCTime : 7/15/2013 3:28:08 PM
LocalTime : 7/15/2013 2:28:08 PM
FileName : sample.log
Component : TSPxe
Context :
Type : 3
TID : 1040
Reference : libsmsmessaging.cpp:9281
Message : content location request failed
.EXAMPLE
C:\PS> Get-ChildItem -Path C:\Windows\CCM\Logs | Select-String -Pattern 'failed' | Select -Unique Path | Get-CMLog
Find all log files in folder, create a unique list of files containing the phrase 'failed, and convert the logs into objects
UTCTime : 7/15/2013 3:28:08 PM
LocalTime : 7/15/2013 2:28:08 PM
FileName : sample.log
Component : TSPxe
Context :
Type : 3
TID : 1040
Reference : libsmsmessaging.cpp:9281
Message : content location request failed
.LINK
http://blog.richprescott.com
#>
param(
[Parameter(Mandatory=$true,
Position=0,
ValueFromPipelineByPropertyName=$true)]
[Alias("FullName")]
$Path,
$tail =10
)
PROCESS
{
if(($Path -isnot [array]) -and (test-path $Path PathType Container) )
{
$Path = Get-ChildItem "$path\*.log"
}
foreach ($File in $Path)
{
if(!( test-path $file))
{
$Path +=(Get-ChildItem "$file*.log").fullname
}
$FileName = Split-Path Path $File Leaf
if($tail)
{
$lines = Get-Content Path $File tail $tail
}
else {
$lines = get-Content path $file
}
ForEach($l in $lines ){
$l -match '\<\!\[LOG\[(?<Message>.*)?\]LOG\]\!\>\<time=\"(?<Time>.+)(?<TZAdjust>[+|-])(?<TZOffset>\d{2,3})\"\s+date=\"(?<Date>.+)?\"\s+component=\"(?<Component>.+)?\"\s+context="(?<Context>.*)?\"\s+type=\"(?<Type>\d)?\"\s+thread=\"(?<TID>\d+)?\"\s+file=\"(?<Reference>.+)?\"\>' | Out-Null
if($matches)
{
$UTCTime = [datetime]::ParseExact($("$($matches.date) $($matches.time)$($matches.TZAdjust)$($matches.TZOffset/60)"),"MM-dd-yyyy HH:mm:ss.fffz", $null, "AdjustToUniversal")
$LocalTime = [datetime]::ParseExact($("$($matches.date) $($matches.time)"),"MM-dd-yyyy HH:mm:ss.fff", $null)
}
[pscustomobject]@{
UTCTime = $UTCTime
LocalTime = $LocalTime
FileName = $FileName
Component = $matches.component
Context = $matches.context
Type = $matches.type
TID = $matches.TI
Reference = $matches.reference
Message = $matches.message
}
}
}
}
}
function Get-CCMLog
{
param([Parameter(Mandatory=$true,Position=0)]$ComputerName = '$env:computername', [Parameter(Mandatory=$true,Position=1)]$path = 'c:\windows\ccm\logs')
DynamicParam
{
$ParameterName = 'Log'
if($path.ToCharArray() -contains ':')
{
$FilePath = "\\$($ComputerName)\$($path -replace ':','$')"
}
else
{
$FilePath = "\\$($ComputerName)\$((get-item $path).FullName -replace ':','$')"
}
$logs = Get-ChildItem "$FilePath\*.log"
$LogNames = $logs.basename
$logAttribute = New-Object System.Management.Automation.ParameterAttribute
$logAttribute.Position = 2
$logAttribute.Mandatory = $true
$logAttribute.HelpMessage = 'Pick A log to parse'
$logCollection = New-Object System.Collections.ObjectModel.Collection[System.Attribute]
$logCollection.add($logAttribute)
$logValidateSet = New-Object System.Management.Automation.ValidateSetAttribute($LogNames)
$logCollection.add($logValidateSet)
$logParam = New-Object System.Management.Automation.RuntimeDefinedParameter($ParameterName,[string],$logCollection)
$logDictionary = New-Object System.Management.Automation.RuntimeDefinedParameterDictionary
$logDictionary.Add($ParameterName,$logParam)
return $logDictionary
}
begin {
# Bind the parameter to a friendly variable
$Log = $PsBoundParameters[$ParameterName]
}
process {
$sb2 = "$((Get-ChildItem function:get-cmlog).scriptblock)`r`n"
$sb1 = [scriptblock]::Create($sb2)
$results = Invoke-Command ComputerName $ComputerName ScriptBlock $sb1 ArgumentList "$path\$log.log"
[PSCustomObject]@{"$($log)Log"=$results}
}
}

view raw
get-ccmlog.ps1
hosted with ❤ by GitHub

I hope this helps someone.

Until then

Keep scripting

Thom

TNSNames File Parsing

If you’ve ever worked with  Oracle you are familiar with Oracle’s TNSNAMES file. This file describes how to get to a database.   With ODP.Net it doesn’t provide a means to parse the TNSNAMES.ora file and then in turn use it with ODP.Net.  From everything I’ve read you must just copy from the Description() and put Data Source = Description(). Then you can use that as a means to connect to your Oracle Database server.    With that in mind I set out to write some scripting to help with this problem.

The first thing I did was to follow this great article by the Scripting Guys about how to use ODP.NET.  After reading that article I found a great module on the Gallery that implemented much of what is spoken about there and I’ll be using that module here in this posting (SimplySQL)

Now I know where my  TNSNAMES.ora file is located so I’ll bring it into my session with:

$tnsnamesPath = 'c:\tns\tnsnames.ora'
$tn = get-content $tnsnamesPath -raw

I brought the file in -raw so that I knew I would have a full object.  Now with some REGEX I can get this file in to the fashion I want.  First to look for the common string in my TNSNAMES.ora file somename = (Description = . 

$parsedTN = $tn -replace '(.*\=.*|\n.*\=)(.*|\n.*)\(DESCRIPTION*.\=' ,'Data Source = (DESCRIPTION ='

Now that I have the connection Name replaced with Data Source = I can now split it into an array and then select my connection based on that array:

$splitTN = $parsedTN -split '(?=.*Data Source = \(DESCRIPTION \=)' 
$splitTN.count
3

$splitTN[1]
Data Source = (DESCRIPTION =
 (ADDRESS_LIST=
 (ADDRESS = (PROTOCOL = TCP)(HOST = server3)(PORT = 1521))
 (ADDRESS = (PROTOCOL = TCP)(HOST = server58)(PORT = 1521)))
 (LOAD_BALANCE = YES)(CONNECTION_TIMEOUT=5)(RETRY_COUNT=3)
 (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ketchup)
 (FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)))
 )

Now that  I have the connections split into an array I can now select the one I want using Where-Object -like “myconnectionName“.  Then with this handy commandlet Open-OracleConnection From this module (simplySQL) , all i have to do next is pass in my username and password and that should open My oracle connection.

$tnsnames = $splitTN |?{$_ -like "*$connectionName*"}
$connstring = "$tnsnames;User Id=$username;Password=$password"
Open-OracleConnection -ConnectionString $connstring -ConnectionName $connectionName

Below is the full script in a GitHub Gist:

param($tnsnamesPath = 'c:\tns\tnsnames.ora',$username = 'user',$password = 'gotmehere', $connectionName = 'mustard', $query = 'Select sysdate from dual')
$simplySQLPath = (Get-Module ListAvailable simplySQL).ModuleBase
if($simplySQLPath -and (test-path $tnsnamesPath PathType Leaf) -and (![string]::IsNullOrEmpty($node)))
{
[System.Reflection.Assembly]::LoadFile("$simplySQLPath\DataReaderToPSObject.dll") | OUT-NULL
Import-Module SimplySql Force
$parsedTN = (get-content $tnsnamesPath raw) -replace '(.*\=.*|\n.*\=)(.*|\n.*)\(DESCRIPTION*.\=' ,'Data Source = (DESCRIPTION ='
$splitTN = $parsedTN -split '(?=.*Data Source = \(DESCRIPTION \=)'
$tnsnames = $splitTN |?{$_ -like "*$connectionName*"}
$connstring = "$tnsnames;User Id=$username;Password=$password"
try
{
Open-OracleConnection ConnectionString $connstring ConnectionName $connectionName
$result = Invoke-SqlQuery ConnectionName $connectionName Query "$SQLQuery"
Close-SqlConnection ConnectionName $connectionName
}
catch
{
$_.exception
}
}
Else
{
if(!(test-path $tnsnamesPath PathType Leaf ErrorAction Ignore))
{
Throw "Check TNSnamesPath: $tnsNamesPath"
}
else
{
Throw "Exeception SIMPLYSQL not found in module Path $($env:PSModulePath)"
}
}
$result

ketchup=(DESCRIPTION =
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = server3)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = server58)(PORT = 1521)))
(LOAD_BALANCE = YES)(CONNECTION_TIMEOUT=5)(RETRY_COUNT=3)
(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ketchup)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)))
)
mustard =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))
(LOAD_BALANCE = YES)(CONNECTION_TIMEOUT=5)(RETRY_COUNT=3)
(FAILOVER = on)
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mustard)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))))
)
)
)
)

view raw
tnsnames.ora
hosted with ❤ by GitHub

I hope this helps someone.

 

Until then

 

Keep scripting

 

Thom