AutoIT – Meets PowerShell using .NET Common Language Runtime (CLR) Framework

August 9, 2017

Since we AutoIT have got access the to the .NET Common Language Runtime (CLR) Framework 

We can access the PowerShell Automation Classes as well.

This is really powerfull stuff … because we can reuse .NET code as well as all the native CMDlet’s and scripts.

As you can see the script runs an PowerShell command that lists all running processes on your machine. And return the Output to a .NET Grid.

image

Which runs inside our Unmanaged Host, isn’t that cool …Smile

We can access native .NET Classes as well because PS runs on top off it.

Next is that you could access any kind of Cloud Web Service using the multitude of PS Modules that are available.

As well as accessing Custom Build Assemblies.

How to :

For more information on how to access .NET Classes see the above post about .NET Common Language CLR Framework.

Or Join the most friendly and helpful AutoIT Community on the net Smile

I will post some more examples and How To’s later on, so stay tuned …

Advertisements

SalesForce.com – Accessing using Web Services and Scripting – Part 3

March 23, 2016

 

Powershell – SalesForce.com – REST Example :

CLS

function Get-AuthorizationTokenWithUsernamePasswordFlow ` 
($client_id, $client_secret, $username, $password, $security_token)
{
    Add-Type -AssemblyName System.Web

    # $uri = "https://test.salesforce.com/services/oauth2/token";
    $uri = "https://login.salesforce.com/services/oauth2/token";
    $grant_type = "password";

    $username = [System.Web.HttpUtility]::UrlEncode($username)
    $password = [System.Web.HttpUtility]::UrlEncode($password)

    $requestBody = "";
    $requestBody += "grant_type=$grant_type";
    $requestBody += "&client_id=$client_id";
    $requestBody += "&client_secret=$client_secret";
    $requestBody += "&username=$username";
    $requestBody += "&password=$password$security_token";

    
    Write-Host "Uri:" $uri
    Write-Host "Body:" $requestBody

    Invoke-RestMethod -Method Post -Uri $uri -Body $requestBody
}

# usage create a ClientID and ClientSecret
# https://help.salesforce.com/apex/HTViewSolution?id=000205876&language=en_US

# https://developer.salesforce.com/page/Digging_Deeper_into_OAuth_2.0_on_Force.com
# Obtaining a Token in an Autonomous Client (Username and Password Flow)

$Cred = Get-Credential -credential "Email.Address@Company.com"
$username = $Cred.UserName
$password = $Cred.GetNetworkCredential().Password $client_id = "YOUR CLIENT ID";
$client_secret = "YOUR CLIENT SECRET";
$security_token = "YOUR SECURITY TOKEN";

# Debug
 Get-AuthorizationTokenWithUsernamePasswordFlow ` 
 $client_id $client_secret $username $password $security_token

$Token = Get-AuthorizationTokenWithUsernamePasswordFlow ` 
$client_id $client_secret $username $password $security_token

$URI = $Token.instance_url+"/services/data/v20.0/"

$Response = Invoke-RestMethod -URI $URI -Method GET ` 
-Headers @{"Authorization" = "OAuth " + $Token.access_token};

Write-Host ""

# Response = availaible REST services

$Response

Write-Host ""

# Query Accounts Example 
Write-Host "Accounts :"

$StrQuery = "SELECT Name, Id from Account LIMIT 100"

$URI = $Token.instance_url+"/services/data/v20.0/query?q=" + $StrQuery

$Response = Invoke-RestMethod -URI $URI -Method GET ` 
-Headers @{"Authorization" = "OAuth " + $Token.access_token};

Write-Host ""
Write-Host " # of Records : "
$Response.records.Count

Write-Host ""
Write-Host "Enum Account Name :"
$Response.records | % {$_.name}


# Query Opporunities Example 
$StrQuery = "SELECT AccountId, OwnerId, Name, Description, ` 
StageName, Amount, Probability FROM Opportunity WHERE StageName <> `</pre>
<pre>'Closed Lost' ORDER BY StageName "

$URI = $Token.instance_url+"/services/data/v20.0/query?q=" + $StrQuery

$Response = Invoke-RestMethod -URI $URI -Method GET ` 
-Headers @{"Authorization" = "OAuth " + $Token.access_token};

$Response.records.Count

Write-Host ""
Write-Host "Query Output :"

Foreach ($objItem in $Response.records) {

    "{0,-120} {1, 25} {2, 30}" -f `
 
   $objItem.name, $objItem.StageName, $objItem.Amount
 }

This script will return your Access Token, using this function

Get-AuthorizationTokenWithUsernamePasswordFlow

image

Next it will list the REST API’s you can use

image

Query Accounts Example :

image

Enjoy !

Smile


SalesForce.com – Accessing using Web Services and Scripting SOAP – Part 2

March 23, 2016

 

Powershell – SalesForce.com – SOAP Example :

CLS

# WSDL is created from within SF.Com Application
$Path = Split-Path -Path $($global:MyInvocation.MyCommand.Path)
$Uri = $Path+"\Enterprise.wsdl" # You can also chose to create a Partner WSDL


$Cred = Get-Credential –credential “<a href="mailto:“Email.LastName@company.com&quot;$SecToken">Email.LastName@company.com”
$SecToken</a> = "YOUR SECURITY TOKEN HERE"

$User = $Cred.UserName
$Password = $Cred.GetNetworkCredential().Password 

# Proxy
$service = New-WebServiceProxy -Uri $Uri -Namespace sforce -UseDefaultCredential

# Login
$loginResult = $service.login($Cred.UserName, $Password+$SecToken)

# Debug info
# $loginResult

$service = New-Object sforce.SforceService
$service.Url = $loginResult.serverUrl
$service.SessionHeaderValue = New-Object sforce.SessionHeader
$service.SessionHeaderValue.sessionId = $loginResult.sessionId

# Listing SalesForce Objects
$accountObjects = $service.describeGlobal()

Write-Host "Enum SalesForce Objects"
Write-Host "-----------------------"

Foreach ($item in $accountObjects.sobjects)
{
    Write-host ($item.name, $item.label)
}

Write-Host ""
Write-Host "Retrieving Object Meta Data"
Write-Host "---------------------------"

# Retrieving meta data about a specific object
$opportunityObject = $service.describeSObject("Opportunity")

foreach ($item in $opportunityObject.fields)
{
    Write-Host ($item.name, $item.label, $item.type)
}

Write-Host ""
Write-Host "RelationshipNames"
Write-Host "-----------------"

foreach ($item in $opportunityObject.childRelationships)
{
  Write-Host ($item.relationshipName)
}

Write-Host ""
Write-Host "Querying Objects"
Write-Host "----------"

# $StrQuery = "SELECT Id, Name, StageName, Amount FROM Opportunity WHERE IsWon = True";

$StrQuery = "SELECT AccountId, OwnerId, Name, Description, StageName, `
Amount, Probability FROM Opportunity";
 
$queryResult = $service.query($StrQuery) 
 
foreach ($item in $queryResult.records)
{
   Write-Host ( $item.AccountId, $item.OwnerId, $item.Name, ´
   $item.Description, $item.StageName, $item.Amount, $item.Probability)
}

rv Password, SecToken 

This Script will list SalesForce Account Objects.

image

Retrieves meta data about a specific object.

image

Retrieves RelationshipNames.

image

Querying Objects

image

For more info on how to get started see here

To go further see here

Enjoy!

Smile


SalesForce.com – Accessing using Web Services and Scripting – Part 1

March 23, 2016

Many of the modern Web platform offer Web Services API’s to access their data.

One of them is SalesForce.com. I choose this example because it has a huge global audience and a well documented Developer Guide.

Before starting your scripting exercise you need to plough through the extensive developer manuals. Which is the lease fun part. Therefore I summarized it all for you to give you all a head start. Winking smile

SF.com has a number of Web Services Protocols as well as a number of authentication methods using the OAuth 2.0 protocol.

On top of that you can start developing in SF.com using APEX

What is APEX

https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_intro_what_is_apex.htm

Apex is a strongly typed, object-oriented programming language that allows developers to execute flow and transaction control statements on the Force.com platform server in conjunction with calls to the Force.com​ API. Using syntax that looks like Java and acts like database stored procedures, Apex enables developers to add business logic to most system events, including button clicks, related record updates, and Visualforce pages. Apex code can be initiated by Web service requests and from triggers on objects.

1. Web Services Protocol

You can use the SOAP protocol or you can use the REST protocol, to access to the web services API.

Both have different ways of dealing with this in you programming / scripting environment.

REST API

https://developer.salesforce.com/page/Getting_Started_with_the_Force.com_REST_API

The REST API is designed to be easily accessible from any programming language – once you understand the basic concepts of the API, you will be able to apply them to PHP, Ruby, .NET, or any other environment.

SOAP API

https://developer.salesforce.com/docs/atlas.en-us.200.0.api.meta/api/sforce_api_quickstart_intro.htm

Use SOAP API to create, retrieve, update or delete records, such as accounts, leads, and custom objects. With more than 20 different calls, SOAP API also allows you to maintain passwords, perform searches, and much more. Use SOAP API in any language that supports Web services.

Choosing a WSDL

· Force.com Enterprise WSDL—This API is for most enterprise users who are developing client applications for their organization. The enterprise WSDL file is a strongly typed representation of your organization’s data.

Note the following when generating the enterprise WSDL:

· If new custom fields or objects are added to, renamed, or removed from your organization’s information, you need to regenerate the WSDL file in order to access them.

· The generated WSDL contains the objects and fields in your organization, including those available in the selected versions of each installed package. If a field or object is added in a later package version, you must generate the enterprise WSDL with that package version to work with the object or field in your API integration.

· Force.com Partner WSDL—This API is for Salesforce partners who are developing client applications for multiple organizations. As a loosely-typed representation of the Salesforce object model, the partner WSDL can be used to access data within any organization.

More API’s and which one to chose ?

https://developer.salesforce.com/docs/atlas.en-us.200.0.api.meta/api/

2. Authtentication Protocol

SF.com uses OAuth2.0 protocol for users to authorize applications, to access Force.com resources (via the Force.com REST and SOAP Web Service APIs).

More info :

http://oauth.net/

The OAuth 2.0 authorization framework enables a third-party application to obtain limited access to an HTTP service.

More info on OAuth 2.0 authorization framework :

https://developer.salesforce.com/page/Digging_Deeper_into_OAuth_2.0_on_Force.com

1. Obtaining an Access Token in a Web Application (Web Server Flow)

2. Obtaining an Access Token in a Browser or Native Application (User-Agent Flow)

3. Obtaining an Access Token using a JWT Bearer Token

4. Obtaining an Access Token using a SAML Bearer Assertion

5. Obtaining an Access Token using a Web SSO SAML Assertion

6. Obtaining a Token in an Autonomous Client (Username and Password Flow)

For our scripting purpose we are going to use the Autonomous Client method.

clip_image002

SF.com Configuration Settings

Before Starting you need to do some configurations in SF.com

1. User

On the user level you need to trigger a Security Token, that we will use in the client application

clip_image004

2. SOAP API

Generate your WSDL file ENTERPRISE or PARTNER

clip_image006

3. REST API

Create a new APP Config and setup the OAuth access parameters.

If you use a Client Application (no Web Interface) you don’t need to set the Callback URL.

Just fill in some dummy data.

image

Once you have configured the new APP, you can read out the CLIENT CONSUMER KEY and the CONSUMER SECRET

This you will need later on when configuring the Client Application Access.

4. Object Model

https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_erd_majors.htm

Next you need to get acquainted with the SF.com Object Model / Data Model in order to run the proper Queries definitions.

clip_image002[5]

OK, so now you are ready to get started if you have made up your mind which of the options you want to chose.

Powershell Examples :

1. SOAP API : See here

2. REST API : See here

Out of experience the REST method is much faster.

More info here:

http://danlb.blogspot.be/2010/10/salesforcecom-rest-api.html

As you can notice is that you go a long way just using a Scripting environment.

Enjoy

Smile


PowerShell – Generating XLSX files without Excel

May 31, 2015

Ever got the need to create excel files without the need of having Excel installed.

Well start reading here http://ramblingcookiemonster.github.io/PSExcel-Intro/

The Powershell module is based on EPPLus Library.

Using this nice PSExcel Module you can do a lot.

First thing is to download the Module and install it in this location.

C:\Users\YourUser\Documents\WindowsPowerShell\Modules\PSExcel

image

Open Powershell ISE and run the Demo Script.

CLS

# Download PSExcel from https://github.com/RamblingCookieMonster/PSExcel
# Unblock the zip
# Extract the PSExcel folder to a module path (e.g. $env:USERPROFILE\Documents\WindowsPowerShell\Modules\)

# Import the module.
    Import-Module PSExcel    #Alternatively, Import-Module \\Path\To\PSExcel

# Get commands in the module
    Get-Command -Module PSExcel

# Get help for a command
    Get-Help Import-XLSX -Full

#Create some demo data
    $DemoData = 1..10 | Foreach-Object{
    
        $EID = Get-Random -Minimum 1 -Maximum 1000
        $Date = (Get-Date).adddays(-$EID)

        New-Object -TypeName PSObject -Property @{
            Name = "jsmith$_"
            EmployeeID = $EID
            Date = $Date
        } | Select Name, EmployeeID, Date
    }

# Export it
    $DemoData | Export-XLSX -Path C:\Temp\Demo.xlsx

# Import it back
    $Imported = Import-XLSX -Path C:\Temp\Demo.xlsx -Header samaccountname, EID, Date

# Open that Excel file...
    $Excel = New-Excel -Path C:\Temp\Demo.xlsx

# Get a workbook
    $Workbook = $Excel | Get-Workbook

# Get a worksheet - can pipe ExcelPackage or Workbook.
# Filtering on Name is optional
    $Excel | Get-Worksheet
    $WorkSheet = $Workbook | Get-Worksheet -Name Worksheet1

# Freeze the top row
    $WorkSheet | Set-FreezePane -Row 2

# Save and close!
    $Excel | Close-Excel -Save

# Re-open the file
    $Excel = New-Excel -Path C:\Temp\Demo.xlsx
    
# Add bold, size 15 formatting to the header
    $Excel |
        Get-WorkSheet |
        Format-Cell -Header -Bold $True -Size 14

# Save and re-open the saved changes
    $Excel = $Excel | Save-Excel -Passthru
    
#  Text was too large!  Set it to 11
    $Excel |
        Get-WorkSheet |
        Format-Cell -Header -Size 11

    $Excel |
        Get-WorkSheet |
        Format-Cell -StartColumn 1 -EndColumn 1 -Autofit -AutofitMaxWidth 7 -Color DarkRed

# Save and close
    $Excel | Save-Excel -Close

# Search a spreadsheet
    Search-CellValue -Path C:\Temp\Demo.xlsx { $_ -like 'jsmith10' -or $_ -eq 280 }

# Add a table, autofit the data.  We use force to overwrite our previous demo.
    $DemoData | Export-XLSX -Path C:\Temp\Demo.xlsx -Table -Autofit -Force

# Fun with pivot tables and charts! Props to Doug Finke
# Get files in your profile, create a chart breaking down size by file extension
    Get-ChildItem $env:USERPROFILE -Recurse -File |
        Export-XLSX -Path C:\Temp\Files.xlsx -PivotRows Extension -PivotValues Length -ChartType Pie

Result is this nice PowerPivot Excel Sheet

image

Great addition for PS Smile


PowerShell – Monitor MS Azure Status

February 22, 2015

If you are using MS Azure or MS Office 365 you are depending on the uptime of all the MS Cloud services.

Well to monitor this there is the Azure Status site.

image

This site offers for each service a RSS subscription to monitor the status.

In this case there was an issue, so we can kick in Powershell to grab the RSS output.

Here we go

 

CLS

$hsg = Invoke-WebRequest  "http://azure.microsoft.com/en-us/status/feed/"

$hsg.Content

[xml]$ret = $hsg.Content.TrimStart("")

#$ret.rss.channel.item | Select-Object *

write-host ""



if($ret.rss.channel.item.category -eq $null){

        write-host $ret.rss.channel.title " On : "  $ret.rss.channel.pubDate
        write-host "All services are working properly"
        write-host ""
    }
Else
    {

    write-host $ret.rss.channel.title " On : "  $ret.rss.channel.pubDate
    Write-Host "Issue Category : " $ret.rss.channel.item.category
    write-host ""
    Write-Host "Details : " $ret.rss.channel.item.title

    $ret.rss.channel.item.description
    write-host ""

}

rv hsg, ret
image

As you can see the raw XML data is prefixed (deliberately or not?) by a few strange characters which we have to eliminate before PowerShell can dig it.

So the final output is like this.

image

So you can create a job to have this run at a frequency to check for uptime issues.

Enjoy!


PowerShell – Access Windows Search

February 10, 2015

Windows has a Search feature that integrates in the OS and as well in Outlook.

Using the Control Panel you access the settings, which folders to index and which folders or Email folders.

image

image

To access the Search database you can use the “Search.CollatorDSO” Provider.

CLS

# GENERAL_COLUMNS
#$GENERAL_COLUMNS = "System.Kind"
$GENERAL_COLUMNS = "System.ItemPathDisplay"
#$GENERAL_COLUMNS = "System.ItemFolderPathDisplay"
#$GENERAL_COLUMNS = "System.Size"
#$GENERAL_COLUMNS = "System.DateCreated"
#$GENERAL_COLUMNS = "System.Author"
#$GENERAL_COLUMNS = "System.Keywords"

# DOCUMENT_COLUMNS
#$GENERAL_COLUMNS = "System.Image.HorizontalSize"
#$GENERAL_COLUMNS = "System.Image.VerticalSize"
#$GENERAL_COLUMNS = "System.Image.BitDepth"
#$GENERAL_COLUMNS = "System.Image.Compression"
#$GENERAL_COLUMNS = "System.Photo.CameraModel"
#$GENERAL_COLUMNS = "System.Photo.DateTaken"
#$GENERAL_COLUMNS = "System.Photo.Flash"

# MUSIC_COLUMNS
#$GENERAL_COLUMNS = "System.Music.Artist"
#$GENERAL_COLUMNS = "System.Music.Genre"
#$GENERAL_COLUMNS = "System.Music.TrackNumber"
#$GENERAL_COLUMNS = "System.Audio.Compression"
#$GENERAL_COLUMNS = "System.Audio.SampleRate"
#$GENERAL_COLUMNS = "System.DRM.IsProtected"
#$GENERAL_COLUMNS = "System.Music.AlbumTitle"
#$GENERAL_COLUMNS = "System.Rating"
#$GENERAL_COLUMNS = "System.Audio.EncodingBitrate"

# VIDEO_COLUMNS
#$VIDEO_COLUMNS = "System.RecordedTV.ChannelNumber"
#$VIDEO_COLUMNS = "System.RecordedTV.EpisodeName"
#$VIDEO_COLUMNS = "System.RecordedTV.NetworkAffiliation"
#$VIDEO_COLUMNS = "System.RecordedTV.RecordingTime"
#$VIDEO_COLUMNS = "System.Video.Compression"
#$VIDEO_COLUMNS = "System.Video.EncodingBitrate"
#$VIDEO_COLUMNS = "System.Video.FrameHeight"
#$VIDEO_COLUMNS = "System.Video.FrameWidth"    
  
$keyword1 = "PowerShell"  
$keyword2 = "au3"  
$extension = ".PDF"
$folder = "C:\"  


$objConnection = New-Object -com ADODB.Connection  
$objRecordSet = New-Object -com ADODB.Recordset

$objConnection.Open("Provider=Search.CollatorDSO;Extended Properties='Application=Windows';")  
$objRecordSet.Open("SELECT $GENERAL_COLUMNS FROM SYSTEMINDEX WHERE System.FileExtension = '$extension' `
                    AND (Contains(Contents,'$keyword1') OR Contains(Contents, '$keyword2')) `
                    AND System.ItemPathDisplay LIKE '$folder\%'", $objConnection)  

if ($objRecordSet.EOF -eq $false) {$objRecordSet.MoveFirst() }  
  
while ($objRecordset.EOF -ne $true) {  
  $objRecordset.Fields.Item("System.ItemPathDisplay").Value  
  $objRecordset.MoveNext()  
 }


rv folder, extension, keyword1, keyword2

As you can see there are many columns you can search. Or using some Queries you can run this on your indexed mails

image

The bonus you get it that you can use this technology to do Remote Search over your network on a remote server or PC.

Enjoy !