Developmentish

September 13, 2011

BusinessObjects: Delete a recurring instance from a business objects report using powershell

Filed under: Business Objects, Powershell — Martyn Gough @ 2:51 pm

Just a quick post today following on from previous posts invoking methods off the back of infostore queries.

            
$SessionMgr = New-Object -com ("CrystalEnterprise.SessionMgr")            
$EnterpriseSession = $SessionMgr.Logon("developmentish","password55","boxiserver01","Enterprise")            
$InfoStore = $EnterpriseSession.Service("","InfoStore")            
            
$InfoObjects = $InfoStore.Query("Select * From CI_INFOOBJECTS Where SI_RECURRING = 1 and SI_NAME = 'My Report Recurring Instance Name'")             
            
$InfoObjects | ForEach {            
    $_.DeleteNow()            
   }            

Like before, lines 1 2 and 3 of the script are connecting to a session and invoking an infostore service.

The next line see’s us using a query where we search for SI_RECURRING=1 meaning a recurring instance and SI_NAME = ‘My Report Recurring Instance Name’, the name of my recurring instance.

Once my query returns the objects, we just loop through the results using a ForEach loop and execute the DeleteNow() Method.

This should delete any recurring instances that have the same name of the specified one in the query.

Be sure to always include the instance name in the query as this code is pretty dangerous… not including a name will delete ALL recurring instances on your Business Objects system!!!

Enjoy!

September 4, 2011

BusinessObjects: Scheduling a report instance to run now in Business Objects using PowerShell

Filed under: Business Objects, Powershell — Martyn Gough @ 11:37 pm

Following on from the last post where we used the COM objects of the business objects SDK to find webi reports, we’ll adapt that to run a webi instance of any report…

            
$SessionMgr = New-Object -com ("CrystalEnterprise.SessionMgr")            
            
$EnterpriseSession = $SessionMgr.Logon("Username","password","BOXIServer01","Enterprise")            
            
$InfoStore = $EnterpriseSession.Service("","InfoStore")            
$InfoObjects = $InfoStore.Query("Select * From CI_INFOOBJECTS Where SI_NAME ='Monthly Sales Report'")            
            
$InfoStore.Schedule($InfoObjects)            

Notice we’re using the infostore object to invoke the .Schedule method on our webi report or “object”.

We can always find out what other methods and properties are available by using Get-Member.

August 16, 2011

BusinessObjects: Querying InfoStore in PowerShell using COM objects

Filed under: Business Objects, Powershell — Martyn Gough @ 4:42 pm

In my current job there isn’t much need for admin style scripts and jazzy exchange stuff… However I like using PowerShell too much, so I’ve chosen to adapt what little I do know and try to use it with the Business Objects SDK and other business intelligence software…

I’ve written the below mini script to pull back all the Web Intelligence objects on the CMS and wrapped it as a reusable function… Thus starting my business objects module nicely.

Function Get-Webi() {            
############################################################################            
#.SYNOPSIS            
#  Gets list of Webi objects from business objects CMS            
#.PARAMETER Cms            
#  The server name of your business objects Central Management Server Node            
#.PARAMETER Username            
#  Enterprise username required to access business objects            
#.PARAMETER Password            
#  Enterprise password required to access business objects            
############################################################################            
            
Param (            
    [string]$CMS,            
    [string]$Username,            
    [string]$Password            
)            
            
    $objSessionMgr = New-Object -com ("CrystalEnterprise.SessionMgr")            
    $objEnterpriseSession = $objSessionMgr.Logon($Username,$Password,$CMS,"Enterprise")            
                
    $objInfoStore = $objEnterpriseSession.Service("","InfoStore")            
    $objInfoObjects = $objInfoStore.Query("SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi'")            
                
    Return $objInfoObjects            
}            

We can call our function and pipe our Webi Objects to something new like:

            
Get-Webi -cms BoxiServer01 -user developmentish -password password1 | Select-Object Title, Description | Out-Gridview            

lets run through the beans of the script then…
Firstly, I’m passing 3 parameters into the function, this is just the CMS server name, business objects user and password.

We create an new COM CrystalEnterprise.SessionMgr object… We then use our session manager object to create an enterprise session passing in the parameters and invoking the logon method.

We then use our enterprise session to start an infostore service and execute a query against it.
We bind this to a local variable and return it to be piped off the back of the function..

When we call it we’re just interested in the title and description and we pipe that out to a gridview just to display it.

And for those who like a good powershell one liner… here is the whole lot on a single line:

            
(((New-Object -com ("CrystalEnterprise.SessionMgr")).Logon("developmentish","password1","boxiserver01","Enterprise")).Service("","InfoStore")).Query("SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND = 'Webi'") | Select Title, Description | OGV            

Enjoy!

August 1, 2011

Quick Tip: Email from your scripts

Filed under: Powershell, Quick Tips — Martyn Gough @ 10:50 pm

One of the most useful things I’ve found is using PowerShell to send an email… Doesn’t sound like much, but it provides power-users a nice “don’t worry, it’s done” email and everyone sleeps that little bit more soundly with some confirmation.

So lets hit the script then…

            
$msg = new-object System.Net.Mail.MailMessage            
$msg.From = new-object System.Net.Mail.MailAddress("Martyn@Developmentish.com")            
$msg.To.Add("Somebody@Someplace.com")                
$msg.Subject = "New Email"            
$msg.Body = "Look at this <b>TEXT</b>"            
            
$htmlView = [System.Net.Mail.AlternateView]::CreateAlternateViewFromString($msg.Body, "text/html")            
$msg.AlternateViews.Add($htmlView)            
            
$smtpClient = new-object System.Net.Mail.SmtpClient            
$smtpClient.Host = "MyEmailServer"            
$smtpClient.Port = 25            
$smtpClient.Send($msg)            

The more seasoned developer will point out that this script is using elements that are more common in .Net applications.
Why? Because PowerShell can!

Lets break it down…

Firstly we create a System.Net.Mail.MailMessage and bind it to the $msg variable.
Then we add a From, a To, a Subject and a body (notice the inclusion of HTML tags in the message).

We could just leave the $msg object as is, but the problem we have is that the body is in plain text… And our body contains HTML.
Here we create an “alternate view” from the message body converting text to HTML.
Then we add the alternate view to our $msg object.

Now comes the geeky part. We need to point our message code at an SMTP server… We create an SMTP client object… to this we declare the host (server) and the port (25 is default). Once we have this all set up, we use the SMTP Client to send our message object.

Once you’ve got the hang of it you can use it for things like error reporting when combined with the ole try catch method. Quick example below tries to get the contents of a folder, if it can’t it sends an email:

            
Try {            
    Get-ChildItem "C:\SomePlace"            
}            
Catch {            
    $msg = new-object System.Net.Mail.MailMessage            
    $msg.From = new-object System.Net.Mail.MailAddress("Martyn@Developmentish.com")            
    $msg.To.Add("Somebody@Someplace.com")                
    $msg.Subject = "New Email"            
    $msg.Body = "Look at this TEXT"            
            
    $htmlView = [System.Net.Mail.AlternateView]::CreateAlternateViewFromString($msg.Body, "text/html")            
    $msg.AlternateViews.Add($htmlView)            
            
    $smtpClient = new-object System.Net.Mail.SmtpClient            
    $smtpClient.Host = "MyEmailServer"            
    $smtpClient.Port = 25            
    $smtpClient.Send($msg)            
}            

Enjoy!

July 30, 2011

Something Cool: BI on the fly with PowerShell

Filed under: Cool Stuff, Powershell — Martyn Gough @ 12:17 am

So far I’ve only done a couple of silly little quick tips… So lets try something a bit more complex…

As a serious fan of PowerShell the more I can do with it, the better… So that got me thinking, why not create some sort of chart on the fly based on system information?
First problem I encountered was picking a nice simple method to use, I opted to use the google charts api as it’s a more pervasive technology than something like SQL server.

So lets get cracking, I just kept the it “powershell light” and simply outputted my top 10 processes based on CPU usage.

            
function Get-GChart ($url) {             
    $filename = $home + "\chart.png"             
    $webClient = new-object System.Net.WebClient             
    $Webclient.DownloadFile($url, $filename)             
    Invoke-Item $filename             
}             
             
function Set-Encode ($valueArray, $labelArray, $size, [switch] $chart3D) {             
    $simpleEncoding = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'             
    if ($chart3D) {$chartType = "p3"} else {$chartType ="p"}             
    $total = 0             
    foreach ($value in $valueArray) {             
        $total = $total + $value             
    }             
    for ($i = 0;$i -lt $valueArray.length;$i++) {             
        $relativeValue = ($valueArray[$i] / $total)*62             
        $relativeValue = [math]::round($relativeValue)             
        $encodingValue = $simpleEncoding[$relativeValue]             
        $chartData = $chartData + "" + $encodingValue             
    }                 
    $chartLabel = [string]::join("|",$labelArray)             
    Write-Output "http://chart.apis.google.com/chart?chtt=Running Process CPU Usage&cht=$chartType&chd=s:$chartdata&chs=$size&chl=$chartLabel"             
}             
             
function Get-ProcessArray() {             
    $ListOfProcs = Get-Process | Sort-Object CPU -desc | Select-Object CPU, ProcessName -First 10             
    $ListOfProcs | ForEach-Object {             
        $ProcName = $ProcName + "," + $_.ProcessName             
        $ProcUsage = $ProcUsage + "," + $_.CPU             
    }             
    Write-Output (($ProcName.trimStart(",")).split(","), ($ProcUsage.trimStart(",")).split(","))             
}             
             
$data = Get-ProcessArray             
$url = Set-Encode $data[1] $data[0] "700x350" -chart3D             
Get-GChart $url             

Ok… what the hell was that?
you’ll notice the 3 functions then 3 lines of code at the bottom… We’ll work from the function calls then explain each function…

I assign $data to the results of the Get-ProcessArray.
This function uses get-process to sort the running processes by CPU then select the CPU and name of the process. We then pipe these 2 values into 2 lists, one for cpu one for name. After this we output these lists as an array.

Now the complicated bit we push the array into the next function Set-Encode.
This function first picks the chart type, adds up all the totals in the value list. We then do some calculation to work out the pie segments then encode it into the correct format for the google charts api to understand.
We then “Join” our label list meaning we turn it into a pipe separated string. Now we have everything we need to build a url for google, so we write that back to the main code.

And finally, we need to get the chart… We simply pass out url value into the Get-GChart function.
This function creates creates a filepath to download to in this case I used my PowerShell default path of $home. We then call the .Net class System.Net.WebClient to perform our internet bidding… Using this we can call .DownloadFile passing in the url to download from and the path to download it to…
Aaaaaaaaaaand finally, Invoke-Item to open our newly downloaded image.

Like all powershell scripts, this was a hell of a lot easier to write than to explain.

Laters!

July 6, 2011

Quick Tip: Finding text in files

Filed under: Powershell, Quick Tips — Martyn Gough @ 12:43 pm

Lets start with this:

            
Get-ChildItem "C:\Test" | Select-String "password" | Select-Object Filename, LineNumber,Path, pattern, Line            

This will find all files in the C:\Test directory, search them using the Select-String cmdlet for a match on the work “password”, then return the filename, line number, path, the word it matched (in this case password) and the actual line of text containing the word.

That’s a lot for one line huh? How does that work?
Well it’s all thanks to the pipe character |. This enables us to pass one object into the next segment of code… I like to think of the pipe meaning “and then”. Using that logic the code says: Find a file and then look for text and then return results. Makes sense like that.

So what happens if we want to delete all files with certain words in them on your whole drive? Maybe you have top secret government information you need to dispose of, or maybe you’re paranoid. Either way we use the pipe:

            
Get-ChildItem "C:\" -recurse | Select-String "Reverse Microwave" |  ForEach-Object { Remove-Item $_.Path  -force }            

In this scenario I’ve backed up all my plans for a reverse microwave (one that freezes stuff really really quickly) to a flash drive and thus need to delete any trace of it from my computer before the people who make ice cube trays track me down.

It’s pretty simple to see what I’ve done, I did a ForEach-Object loop meaning each result is then passed into the code contained in the {curly brackets}. Here I’ve just done a remove-item using -force (to cater for read only violations).

That’s all from me!

May 5, 2011

Quick Tip: Creating folders based on dynamic dates

Filed under: Powershell, Quick Tips — Martyn Gough @ 1:39 pm

Lets get things started with a favourite of mine… Powershell. An immensely powerful tool which makes automation of mundane tasks a breeze. (anyone wanting to learn should check out the TechNet webinars here!)

Anyway… onto the point of today’s post, Dates and times.

The cmdlet for pulling the current date and time is Get-Date:

Get-Date

This will return:

    05 May 2011 13:06:29

Ok, handy, but what if we need this formatting in a different way say for a folder name or similar? well we use the -format parameter.

Get-Date -format "dd-MM-yyyy"

This will return:

    05-05-2011

So lets work on that folder name theory, say we want to create a folder with yesterday’s date.

$dYesterday  = (Get-Date) - (New-TimeSpan -days 1)            
$dYesterday  = Get-Date $dYesterday -format "dd-MM-yyyy"            
            
$sFolderName = "C:\$dYesterday"            
            
New-Item -type Directory -path $sFolderName            

Lets go through this step by step then…

This uses Get-Date to pull the current date time then subtracts a time span of 1 day from the value:

$dYesterday  = (Get-Date) - (New-TimeSpan -days 1)

We use Get-Date to pull the previously calculated value so that we can apply the -format parameter on it… We don’t do this in the above calcuation as some formats can convert the datetime value into a string causing the subtraction of the timespan to fail:

$dYesterday  = Get-Date $dYesterday -format "dd-MM-yyyy"

We create a path for our new folder as C:\yesterday’s date in dd-MM-yyyy format:

$sFolderName = "C:\$dYesterday"

Finally we use New-Item to create a directory using our dynamically created path variable from above:

New-Item -type Directory -path $sFolderName            

If you wish to delve deeper into dates and timespans in powershell, just remember you already have all you need at your fingertips:

Get-Help Get-Date
Get-Help New-Timespan

Wew, there we go, my first ever technical blog… I know this assumes some working knowledge of powershell, but hey it’s a tip not a step by step.

Enjoy!

« Newer Posts

Blog at WordPress.com.

Design a site like this with WordPress.com
Get started