Developmentish

September 15, 2014

PowerShell + BusinessObjects REST API #9: WEBi Alerters (Conditional Formatting)

Filed under: Business Objects, Powershell, REST API — Martyn Gough @ 2:42 pm

Conditional formatting rules are an everyday part of reporting via BusinessObjects WEBi… But finding out what the colouring rules behind a report are is still a horribly manual process.
You can use your newfound scripting abilities to combat this… In this tutorial, we’ll be covering how to discover the conditions and formats behind an alerter.

Assumptions are that you’re now familiar with connecting to the platform via the REST web service and are able to search for a document based on it’s ID… If not, I suggest you recap some earlier posts on this.

Well, first thing’s first… Here’s the whole script:

#########################################################################################################################################################            
# Log on to BI platform            
            
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"            
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}            
            
$LogonInfo             = @{}            
$LogonInfo.userName    = "developmentish"               
$LogonInfo.password    = "password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)            
            
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""            
            
#########################################################################################################################################################            
# Make a request            
            
# Do this for a report ID I already know            
$ReportID              = 706729            
            
# And AlerterID 6 - You'll need to discover which ID you want by running a get on the ../documents/reportID/alerters URL without the /ID at the end.            
$AlerterID             = 6            
            
$DocRequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents/$ReportID/alerters/$AlerterID"            
$DocRequestHead        = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
# Run the rest URL and save the results as a variable            
$RestResult             = Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHead            
            
#########################################################################################################################################################            
# Log off of BI Platform            
            
$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"            
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
Invoke-RestMethod -Method POST -Uri $LogoffRequestUri -Headers $LoggoffRequestHeaders             
            
#########################################################################################################################################################            
# Working with our web service result            
            
# Create a system object for the result so we can add custom properties to our output            
$input = New-Object System.Object            
            
# Add various properties to our object... Multiple values will appear in @{} brackets... This happens if your alerter has multiple conditions & formats            
$Input | Add-Member -MemberType NoteProperty -Name ID -Value $RestResult.alerter.id            
$Input | Add-Member -MemberType NoteProperty -Name Name -Value $RestResult.alerter.name            
$Input | Add-Member -MemberType NoteProperty -Name Description -Value $RestResult.alerter.description            
$Input | Add-Member -MemberType NoteProperty -Name Condition -Value $RestResult.alerter.rule.conditions.condition            
$Input | Add-Member -MemberType NoteProperty -Name Background -Value $RestResult.alerter.rule.action.style.background.color            
$Input | Add-Member -MemberType NoteProperty -Name Font  -Value $RestResult.alerter.rule.action.style.font            
            
# Display the result in the console            
$Input

So we need to look at what’s going on here… Lets start with our request:

            
# Do this for a report ID I already know            
$ReportID              = 706729            
            
# And AlerterID 6 - You'll need to discover which ID you want by running a get on the ../documents/reportID/alerters URL without the /ID at the end.            
$AlerterID             = 6            
            
$DocRequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents/$ReportID/alerters/$AlerterID"            
$DocRequestHead        = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
# Run the rest URL and save the results as a variable            
$RestResult             = Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHead

This shows us doing a GET request to the REST URL ../Documents/[whatever our reportID is]/alerters/[Whatever our alerterID is].
Before I built this URL I ran ../documents/[ReportID]/alerters and looked at the results and picked which alerter I wanted… I could have easily ran that and looped through the result set with a ForEach-Object{} loop too if I wanted all alerters for a document.

After I’ve pulled the results of my REST request, I saved them under the variable $RestResult then logged off of the platform to release the session.

At the bottom of the script I’ve manipulated my result and pushed various parts of it into properties of an empty object (this just looks nice when displaying it on screen)

            
# Create a system object for the result so we can add custom properties to our output            
$input = New-Object System.Object            
            
# Add various properties to our object... Multiple values will appear in @{} brackets... This happens if your alerter has multiple conditions & formats            
$Input | Add-Member -MemberType NoteProperty -Name ID -Value $RestResult.alerter.id            
$Input | Add-Member -MemberType NoteProperty -Name Name -Value $RestResult.alerter.name            
$Input | Add-Member -MemberType NoteProperty -Name Description -Value $RestResult.alerter.description            
$Input | Add-Member -MemberType NoteProperty -Name Condition -Value $RestResult.alerter.rule.conditions.condition            
$Input | Add-Member -MemberType NoteProperty -Name Background -Value $RestResult.alerter.rule.action.style.background.color            
$Input | Add-Member -MemberType NoteProperty -Name Font  -Value $RestResult.alerter.rule.action.style.font            
            
# Display the result in the console            
$Input

Alternatively you could have explored this result object yourself and retrieve only the parts you’re interested in.

You can use this method for getting the details of an alerter and programmatically adding the rule to other documents for example. We may cover that in future sessions.

Enjoy!

August 14, 2014

PowerShell + BusinessObjects REST API #8: WEBi Schedules

Filed under: Business Objects, Powershell, REST API — Martyn Gough @ 4:06 pm

Just a quick little one today for checking for failed schedules for a specific report.
You can throw this in a loop to cycle through key reports or even just use it to check for completed instances by changing the status… But simplicity of demonstration is the key here, so we’re just going to check 1 report and see if any of the schedules have a failed status.

            
#########################################################################################################################################################            
# Log on to BI platform            
            
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"            
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}            
            
$LogonInfo             = @{}            
$LogonInfo.userName    = "devlopmentish"               
$LogonInfo.password    = "password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)            
            
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""            
            
#########################################################################################################################################################            
            
$DocRequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents/87262/schedules"            
$DocRequestHead        = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
$Schedules             = (Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHead).schedules.schedule             
            
$Schedules | where-object {$_.Status."$" -eq "failed"}             
            
#########################################################################################################################################################            
# Log off of BI Platform            
            
$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"            
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}

As always, previous lessons tell us all about logging on and off and searching based on ID.

For my example, I’ve used an ID number od 87262.

Skipping to the interesting bit:

            
$DocRequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents/87262/schedules"            
$DocRequestHead        = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
$Schedules             = (Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHead).schedules.schedule             
            
$Schedules | where-object {$_.Status."$" -eq "failed"}

Here we’re using a documents/ID/schedules URL to pull back the schedules in JSON format. Then expanding the node to the schedules.schedule level… This returns the name, id, type and status, saving this collection as out $Schedules variable.

We then look at $Schedules variable and select from our collection only schedules with a status of “failed”… We do this here as the status has to be expanded to search by status name and where-object is the easiest way.

Not to complicated for today!

July 23, 2014

PowerShell + BusinessObjects REST API #7: Webi Variables

Filed under: Business Objects, Powershell, REST API — Martyn Gough @ 5:38 pm

Isn’t it fun when you want to change a universe but you haven’t a clue what people before you have done in Webi with variables? No, no it’s not… You end up breaking some sort of hard coded Webi if statement somewhere and nobody tells you for weeks then expects you to re-run all the reports.

Luckily, thanks to the joys of the new api, we can quickly look at all the variable logic for a document… (Or just adapt this script into a loop for a set of documents)… Here’s how.

By now you should be quite familiar with logging on and the concepts of document ID’s… If not I urge you to go back a few lessons and all will become clear!

Full script:

#########################################################################################################################################################            
# Log on to BI platform            
            
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"            
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}            
            
$LogonInfo             = @{}            
$LogonInfo.userName    = "developmentish"               
$LogonInfo.password    = "password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)            
            
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""            
            
#########################################################################################################################################################            
# Request            
            
$RequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents/538771/variables"            
$RequestHead        = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
# Get variables in document, cycle through to get variable details for each:            
(Invoke-RestMethod -Method GET -Uri $RequestUri -Headers $RequestHead).variables.variable | ForEach-Object {            
    $CurrentID = $_.id            
    (Invoke-RestMethod -Method GET -Uri "$RequestUri/$CurrentID" -Headers $RequestHead).Variable            
}            
            
#########################################################################################################################################################            
# Log off of BI Platform            
            
$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"            
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
Invoke-RestMethod -Method POST -Uri $LogoffRequestUri -Headers $LoggoffRequestHeaders

As always I’ll explain the relevant bit:

#########################################################################################################################################################            
# Request            
            
$RequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents/538771/variables"            
$RequestHead        = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
# Get variables in document, cycle through to get variable details for each:            
(Invoke-RestMethod -Method GET -Uri $RequestUri -Headers $RequestHead).variables.variable | ForEach-Object {            
    $CurrentID = $_.id            
    (Invoke-RestMethod -Method GET -Uri "$RequestUri/$CurrentID" -Headers $RequestHead).Variable            
}

What we’re doing here, is a request for document ID 538771… We use the /variables url to get all variables in the document… Then we push all them objects into a for each loop and grab the ID for each, firing it into another rest method get request using the /variables/VariableIDNumber url… This gives us the extended properties of the variable, including the type, syntax and data type.

You should end up with some output like this:


@qualification : Dimension
@dataType : Numeric
id : L23
name : Success Flag
formulaLanguageId : [Success Flag]
definition : =If([Outcome]="Success";1;0)

Fun huh?

July 14, 2014

PowerShell + BusinessObjects REST API #6: Retrieving report elements for use elsewhere

Filed under: Business Objects, Powershell, REST API — Martyn Gough @ 3:13 pm

Today’s lesson is pretty handy if you have an intranet and want to quickly generate some BI content for displaying on it. Like as an iFrame or something simple.

This code will allow you to extract a single element from any Webi report in BusinessObjects and output it in HTML format. This means 1 BusinessObjects session to get the content, 1 database hit if you’re refreshing it but ultimately an unlimited audience for your information as it’s pulled off in a raw static format.
Want it to update? Just add in a refresh command and put the script as a scheduled task. It’s almost a dashboard!

Complete working script:

#########################################################################################################################################################                        
# Log on to BI platform                        
                        
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"                        
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}                        
                        
$LogonInfo             = @{}                        
$LogonInfo.userName    = "developmentish"                           
$LogonInfo.password    = "password"                        
$LogonInfo.auth        = "secEnterprise"                          
$Logon                 = ($LogonInfo | ConvertTo-Json)                        
                        
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""                        
                        
#########################################################################################################################################################                        
# Do the good stuff:            
            
# Here is our URL for the rest call.            
$RequestUri  = "http://BOEServer:6405/biprws/raylight/v1/documents/55160/reports/2798/elements/2803"            
            
# Our request header            
$RequestHead = @{"Accept" = "text/html"; "X-SAP-LogonToken" = $logonToken }            
            
# Just an output            
$OutputHTML = "C:\Temp.html"            
            
# Call the Rest method then pipe out the text/html output into a text file...            
Invoke-RestMethod -Method GET -Uri $RequestUri -Headers $RequestHead  | Out-File $OutputHTML            
            
# Open the output            
Invoke-Item $OutputHTML             
                        
#########################################################################################################################################################                        
# Log off of BI Platform                        
                        
$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"                        
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}                        
                        
Invoke-RestMethod -Method POST -Uri $LogoffRequestUri -Headers $LoggoffRequestHeaders                         
                        
#########################################################################################################################################################

 

As always, we won’t recap the silly things like logging on and off again… We’ll dive into the fresh bit:

As previous we needed to create our REST url and header…

#########################################################################################################################################################                        
# Do the good stuff:            
            
# Here is our URL for the rest call.            
$RequestUri  = "http://BOEServer:6405/biprws/raylight/v1/documents/55160/reports/2798/elements/2803"            
            
# Our request header            
$RequestHead = @{"Accept" = "text/html"; "X-SAP-LogonToken" = $logonToken }

 

NOTE: The URL here is static to my report… You will need to populate this with your own document, report and element ID!
Syntax is: ...raylight/v1/documents/YOUR DOCUMENT ID/reports/YOUR REPORT ID/elements/YOUR ELEMENT ID

You can find these out by going to your properties in BI Launchpad/CMC on a report. The ID is listed there. This is your document ID.

To find your report ID (these correspond to the tabs in your webi document)…
Run a rest GET call on YOURSERVER:PORTS/biprws/raylight/v1/documents.YOUR DOCUMENT ID/reports
Follow the same pattern to get your element ID: YOURSERVER:PORTS/biprws/raylight/v1/documents.YOUR DOCUMENT ID/reports/YOUR REPORT ID/elements

ANOTHER NOTE: Notice our request hearer has the accept parameter set to text/html rather than the usual application/json? This is because we want it to output the report element in html code so that we can shove it directly into a html file!

Now with all that complexity out of the way, we can actually get on with doing what we came here for:

# Just an output            
$OutputHTML = "C:\Temp.html"            
            
# Call the Rest method then pipe out the text/html output into a text file...            
Invoke-RestMethod -Method GET -Uri $RequestUri -Headers $RequestHead  | Out-File $OutputHTML            
            
# Open the output            
Invoke-Item $OutputHTML

 

The first line just being a variable to store the path for our HTML file at C:\Temp.html..

Then we call our rest web service and pipe | the html based response to our html path… The file doesn’t need to exist for this, it’ll generate/overwrite.

Then after that we open the page in the default browser. If you’re past testing, you can get rid of this step.

Not bad huh?
You can expand this script heavily with document refresh, or do multiple documents, or multiple outputs or whatever! I personally just use it to retrieve a refreshed element from our core KPI report and then use a HTML iFframe on our information portal page to display the content as a page element. Integrated BI-fed displays without having to build a dashboard!

June 9, 2014

PowerShell + BusinessObjects REST API #5: Purging data

Filed under: Business Objects, Powershell, REST API — Martyn Gough @ 3:23 pm

If like me, you like to keep documents purged in your BI platform to keep the sizes down, then you’ll love this…

Complete working script:

#########################################################################################################################################################            
# Log on to BI platform            
            
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"            
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}            
            
$LogonInfo             = @{}            
$LogonInfo.userName    = "developmentish"               
$LogonInfo.password    = "password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)            
            
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""            
            
#########################################################################################################################################################            
# Main Script            
            
# The ID for the document            
$DocID = 56224            
            
# The purge command + options if applicable            
$PurgeCommand = "?purge=true"            
            
# Document Request URL and Headers            
$DocRequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents/$DocID/dataproviders"            
$DocRequestHead        = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
# Loop through the data providers in the document (queries)            
(Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHead).DataProviders.DataProvider | ForEach-Object {            
                
    # Get the current data provider id            
    $CurrentDataProviderID = $_.id            
            
    # Call the purge URL which is the doc request url / data provider id and the purge command parameters.            
    Invoke-RestMethod -Method PUT -Uri "$DocRequestUri/$CurrentDataProviderID$PurgeCommand" -Headers $DocRequestHead            
}            
            
# Save Request URL and Header            
$SaveRequestUri        = "http://BOEServer:6405/biprws/raylight/v1/documents/$DocID"            
$SaveRequestHead       = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
# Update the doc to save            
Invoke-RestMethod -Method PUT -Uri $SaveRequestUri -Headers $SaveRequestHead            
            
#########################################################################################################################################################            
# Log off of BI Platform            
            
$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"            
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
Invoke-RestMethod -Method POST -Uri $LogoffRequestUri -Headers $LoggoffRequestHeaders             
            
#########################################################################################################################################################

 

We’ve already covered logging on and finding document details (you’ll need a document ID for this script)

So assuming we know our DocumentID, we’ll need to fire it through a rest method request, pull back the dataproviders and then loop through them:

            
# Document Request URL and Headers            
$DocRequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents/$DocID/dataproviders"            
$DocRequestHead        = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
# Loop through the data providers in the document (queries)            
(Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHead).DataProviders.DataProvider | ForEach-Object {

We take the ID of each dataprovider and call a PUT request telling that data provider to purge…

            
              
    # Get the current data provider id            
    $CurrentDataProviderID = $_.id            
            
    # Call the purge URL which is the doc request url / data provider id and the purge command parameters.            
    Invoke-RestMethod -Method PUT -Uri "$DocRequestUri/$CurrentDataProviderID$PurgeCommand" -Headers $DocRequestHead

Aaaaaaaaaand finally we have to save our document after purge…

            
# Save Request URL and Header            
$SaveRequestUri        = "http://BOEServer:6405/biprws/raylight/v1/documents/$DocID"            
$SaveRequestHead       = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
# Update the doc to save            
Invoke-RestMethod -Method PUT -Uri $SaveRequestUri -Headers $SaveRequestHead

That was simple huh?

June 4, 2014

PowerShell + BusinessObjects REST API #4 – Search for documents by ID and opening them with OpenDoc

Filed under: Business Objects, OpenDoc, Powershell, REST API — Martyn Gough @ 12:08 pm

If like me, you use the %SI_PARENTID% placeholder in your scheduled instances as a sort of Report ID, you’ll notice that the BOE search function can’t search on this.

So our skills with PowerShell come back into play…
I’ve put together this handy-dandy script that allows us to search the BOE platform on ID and asks us if we’d like to open the report using OpenDoc.

#########################################################################################################################################################            
# Log on to BI platform            
            
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"            
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}            
            
$LogonInfo             = @{}            
$LogonInfo.userName    = "developmentish"               
$LogonInfo.password    = "password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)            
            
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""            
            
#########################################################################################################################################################            
# Get Document Details            
            
# Get input            
$ReportID              = (Read-Host "Enter the report ID Number")            
            
# Build URL and Headers            
$DocRequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents/$ReportID"            
$DocRequestHead        = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
# Retrive document            
$Document              = (Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHead).document            
            
# If there's nothing... Quit!            
if($Document -eq $null) {            
    exit            
}            
            
# Grab the CUID for use in an OpenDoc URL            
$cuid                  = $Document.cuid            
            
#########################################################################################################################################################            
# Log off of BI Platform            
            
$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"            
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
Invoke-RestMethod -Method POST -Uri $LogoffRequestUri -Headers $LoggoffRequestHeaders             
            
#########################################################################################################################################################            
            
# Shows document detail            
$Document                          
            
# Wait for OpenDoc input and Launch IE with OpenDoc URL            
If((Read-Host "Do you want to open this report now? Y/N") -eq "Y") {            
    $ie =New-Object -ComObject InternetExplorer.Application            
    $ie.Navigate2("http://BOEServer:8080/BOE/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=$cuid")            
    $ie.Visible = $true            
}

Logging on and off again is explained in part 1 of this series: Logging on and off of SAP BusinessObjects using PowerShell and the RESTful API

Firstly we use the Read-Host cmdlet to take console input of the ID number…

$ReportID              = (Read-Host "Enter the report ID Number")

Our URL and header are nothing special that hasn’t already been covered in this series, so I won’t bother to explain them.

The rest of this script block is pretty simple too, call the rest-method, quit if the document isn’t found and pull back the CUID from the properties before logging off of BusinessObjects.

# Retrive document            
$Document              = (Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHead).document            
            
# If there's nothing... Quit!            
if($Document -eq $null) {            
    exit            
}            
            
# Grab the CUID for use in an OpenDoc URL            
$cuid                  = $Document.cuid

Now for the fancy bit… We display the document properties on screen here, this includes things like modified date, cuid, path etc…

# Shows document detail            
$Document

We do another Read-Host for input to see if we’d like to open the report, then we launch Internnet explorer, navigate to an OpenDoc URL built using our CUID from the retrieved document properties, oh and most importantly, make IE visible!!!! Because, for some strange reason will open hidden form the UI as default! This is pretty seamless if you have SSO enabled on your BI platform.

            
# Wait for OpenDoc input and Launch IE with OpenDoc URL            
If((Read-Host "Do you want to open this report now? Y/N") -eq "Y") {            
    $ie =New-Object -ComObject InternetExplorer.Application            
    $ie.Navigate2("http://BOEServer:8080/BOE/OpenDocument/opendoc/openDocument.jsp?sIDType=CUID&iDocID=$cuid")            
    $ie.Visible = $true            
}

Enjoy!!!

June 3, 2014

PowerShell + BusinessObjects REST Api #3: Finding/Disabling inactive users automatically

Filed under: Business Objects, Powershell, REST API — Martyn Gough @ 11:27 am

Today we’re going to take the hassle out of BusinessObjects user administration with a handy snippet that queries for each user’s last logon time.

To start with you’ll need to have read the first post on logging onto your platform: Logging on and off of SAP BusinessObjects using PowerShell and the RESTful API

To do the automatic removal of users from an Active Directory group you’ll need the active directory module for PowerShell and permissions on AD to remove users from security groups (or access to a network administrator who can help test and implement such a script).

Our complete script will look like this:

#########################################################################################################################################################            
# Log on to BI platform            
            
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"            
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}            
            
$LogonInfo             = @{}            
$LogonInfo.userName    = "Developmentish"               
$LogonInfo.password    = "Password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)            
            
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""            
            
#########################################################################################################################################################            
# Query InfoStore            
            
$InfostoreRequestUri   = "http://BOEServer:6405/biprws/infostore/Users/children"            
$InfostoreRequestHead  = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
$DaysCutoof            = 90            
$BOEAccessGroupAD      = "GRP_BOE_AppLogon"            
            
$UserList.Clear            
            
# Query infostore for child objects of the users            
$Infostore             = Invoke-RestMethod -Method GET -Uri $InfostoreRequestUri -Headers $InfostoreRequestHead            
            
$Infostore.link | ForEach-Object {            
    # Get user specific URI and call rest on each for logon times            
    $UserRestUri       = $_.href             
    $GetUser           = Invoke-RestMethod -Method GET -Uri $UserRestUri -Headers $InfostoreRequestHead            
                
    # Path to last logon            
    $LastLogon         = $GetUser.Entry.content.attrs.attr."#text"[6]            
            
            
    if($LastLog -eq $null) {             
        # If never logged on set the datediff for 999            
        $diff          = 999            
    }            
    else{            
        # Perform a date diff between last logon data and current date in days            
        $diff          = (New-TimeSpan $($LastLog) $(Get-Date)).days            
    }              
    if($diff -gt $DaysCutoof) {             
        #If user hasn't logged on in x days, add username to list            
        $UserList + ($GetUser.Entry.content.attrs.attr."#text"[3])            
    }            
}            
            
#########################################################################################################################################################            
# Log off of BI Platform            
            
$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"            
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
Invoke-RestMethod -Method POST -Uri $LogoffRequestUri -Headers $LoggoffRequestHeaders             
            
#########################################################################################################################################################            
# Use list of users from BI query to action active directory            
            
$UserList | ForEach-Object {            
    $User = $_            
    Remove-ADGroupMember -Identity $BOEAccessGroupAD -Member "$User"            
}            
            
#########################################################################################################################################################

Ok, so what does all that mean then??? Well I’m not going to explain logging on and off so we’ll skip straight to the query infostore section…

This code contains our URL and header for our “Get-All-Users” query… Notice the incision of the “/children” in the URL… This does not mean we’re just looking for the BI youth, but refers to bringing back the properties of each user.

            
$InfostoreRequestUri   = "http://BOEServer:6405/biprws/infostore/Users/children"            
$InfostoreRequestHead  = @{"Accept" = "application/json";"ContentType" = "application/json";"X-SAP-LogonToken" = $LogonToken}

We’ve just used a couple of variables here to store some data, you’ll need to change these to suit your requirements. Putting it all here just makes it easy to manage.

$DaysCutoof            = 90            
$BOEAccessGroupAD      = "GRP_BOE_AppLogon"

The clear statement was added just because I somehow managed to run this script twice at the same time and it went a bit mental… This shouldn’t be required if you’re sensible.

Now we run our rest request:

            
# Query infostore for child objects of the users            
$Infostore             = Invoke-RestMethod -Method GET -Uri $InfostoreRequestUri -Headers $InfostoreRequestHead

The for each item in our list we retrieve the “link” property… This is essentially a direct URL to each user’s extended properties… so we loop through our list of links, using them to call for the last login per user, checking if it’s date is greater than our cutoff and adding their userid to a list:

$Infostore.link | ForEach-Object {            
    # Get user specific URI and call rest on each for logon times            
    $UserRestUri       = $_.href             
    $GetUser           = Invoke-RestMethod -Method GET -Uri $UserRestUri -Headers $InfostoreRequestHead            
                
    # Path to last logon            
    $LastLogon         = $GetUser.Entry.content.attrs.attr."#text"[6]            
            
            
    if($LastLog -eq $null) {             
        # If never logged on set the datediff for 999            
        $diff          = 999            
    }            
    else{            
        # Perform a date diff between last logon data and current date in days            
        $diff          = (New-TimeSpan $($LastLog) $(Get-Date)).days            
    }              
    if($diff -gt $DaysCutoof) {             
        #If user hasn't logged on in x days, add username to list            
        $UserList + ($GetUser.Entry.content.attrs.attr."#text"[3])            
    }            
}

So finally we log off the patform and loop through our list of “userid’s that have a last logon greater than x days ago” and use an active directory cmdlet to remove them from the application access group:

#########################################################################################################################################################            
# Use list of users from BI query to action active directory            
            
$UserList | ForEach-Object {            
    $User = $_            
    Remove-ADGroupMember -Identity $BOEAccessGroupAD -Member "$User"            
}

Of course the active directory part is optional, you could quite easily do other things with a list of inactive users… e.g. Send each one an email reminding them to look at BusinessObjects or pipe the list to a csv file for manual management of accounts.

Hope this is handy!

June 2, 2014

PowerShell + BusinessObjects REST Api #2: Getting a list of documents

Filed under: Business Objects, Powershell, REST API, Uncategorized — Martyn Gough @ 3:00 pm

Today we’re going to follow on from the first lesson: Logging on and off of SAP BusinessObjects using PowerShell and the RESTful API.

This is a simple request to send however we need to handle the response as the Raylight SDK will only return results in batches of up to 50.

$DocRequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents?offset=0&limit=10"            
$DocRequestHeaders     = @{"Accept" = "application/json";"Content-Type" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
(Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHeaders).documents.document | Format-Table

As we can see above, our web service URL contains 2 parameters… Offset and Limit…
Limit is the number of results returned by the GET request [1-50]
Offset is the starting point in the result list.

In English, offset=0&limit=50 will return results 1-50… offset=150&limit=20 will return results 150-170.

So if we lob this all together with our login script we’ve got something that’ll return us a list of documents in BusinessObjects in a tabular format.

#########################################################################################################################################################            
# Log on to BI platform            
            
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"            
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}            
            
$LogonInfo             = @{}            
$LogonInfo.userName    = "developmentish"               
$LogonInfo.password    = "Password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)            
            
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""            
            
#########################################################################################################################################################            
# Get a list of documents            
            
$DocRequestUri         = "http://BOEServer:6405/biprws/raylight/v1/documents?offset=0&limit=10"            
$DocRequestHeaders     = @{"Accept" = "application/json";"Content-Type" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
(Invoke-RestMethod -Method GET -Uri $DocRequestUri -Headers $DocRequestHeaders).documents.document | Format-Table            
            
#########################################################################################################################################################            
# Log off of BI Platform            
            
$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"            
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
Invoke-RestMethod -Method POST -Uri $LogoffRequestUri -Headers $LoggoffRequestHeaders             
            
#########################################################################################################################################################

Enjoy!

May 30, 2014

PowerShell + BusinessObjects REST Api #1: Logging on and off

Filed under: Business Objects, Powershell, REST API — Martyn Gough @ 2:23 pm

Well it’s been a while since I posted some PowerShell and BusinessObjects tips… That’s pretty much due to the whole SDK being a bit rubbish.
Now however things have got better since the introductions of the RESTful API and PowerShell’s invoke-RestMethod cmdlet.

So lets start at the begining… Creating a session token (aka, logging on).
First question, why do we need a session token??? Well as everything in the RESTful api is URL driven, we need to provide a session when we want to do other actions.

Firstly… You’ll need PowerShell v4 or above (Version 3 has an issue with Invoke-RestMethod and does weird stuff with the accept headers).
You’ll also need a RESTful compatible BusinessObjects instance.

For my examples, my server is BOEServer, my port is the default 6405 and I’ll be using JSON rather than XML (It’s just nicer!).

Complete Working Logon Script

            
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"            
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}            
            
$LogonInfo             = @{}            
$LogonInfo.userName    = "Developmentish"               
$LogonInfo.password    = "Password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)            
            
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""

So lets go through what all that means…

This line contains the logon URL.

$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"

This line contains the headers for the request, we’re telling it we’ll accept json as an output and we’re senting json as the content type of our request.

$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}

Next we build an array to contain all of our logon credentials then convert it to json to put into our request.

$LogonInfo             = @{}            
$LogonInfo.userName    = "Developmentish"               
$LogonInfo.password    = "Password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)

And finally we call the RestMethod wrap the result in quotes (Because it won’t accept it not in quotes) and assign it to a variable for use later.
Our request is a POST request and puts our content variables in the header and the json authentication information in the body.

$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""

And there you go, you’ve just logged on!
You better log off too, you don’t want to use up all your BusinessObjects licenses with logon requests! So lets do that!

It’s just the same idea, we build a Rest request and invoke it…

$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"            
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
Invoke-RestMethod -Method POST -Uri $LogoffRequestUri -Headers $LoggoffRequestHeaders

It’s a bit simpler as we don’t need to provide all our logon credentials again, we just build the logon token into the request header. Simple!

All together the script looks like this…

#########################################################################################################################################################            
# Log on to BI platform            
            
$LogonRequestUri       = "http://BOEServer:6405/biprws/logon/long"            
$LogonRequestHeaders   = @{"Accept" = "application/json";"Content-Type" = "application/json"}            
            
$LogonInfo             = @{}            
$LogonInfo.userName    = "Developmentish"               
$LogonInfo.password    = "Password"            
$LogonInfo.auth        = "secEnterprise"              
$Logon                 = ($LogonInfo | ConvertTo-Json)            
            
$LogonToken            = "`"" + (Invoke-RestMethod -Method POST -Uri $LogonRequestUri -Headers $LogonRequestHeaders -Body $Logon).logonToken + "`""            
            
#########################################################################################################################################################            
            
# Here you'd do something....            
            
#########################################################################################################################################################            
# Log off of BI Platform            
            
$LogoffRequestUri      = "http://BOEServer:6405/biprws/logoff"            
$LoggoffRequestHeaders = @{"Accept" = "application/json";"X-SAP-LogonToken" = $LogonToken}            
            
Invoke-RestMethod -Method POST -Uri $LogoffRequestUri -Headers $LoggoffRequestHeaders             
            
#########################################################################################################################################################

Enjoy!

September 20, 2011

News: PowerShell 3.0 CTP#1

Filed under: News, Powershell — Martyn Gough @ 11:56 pm

Hot off the press, PowerShell 3.0 CTP #1… Boasting a whole range of new tinkerables. Download and have a looky, it’s shaping up to have some pretty meaty features.

Download the Windows Management Framework 3.0 – Community Technology Preview (CTP) #1 here!

Older Posts »

Blog at WordPress.com.

Design a site like this with WordPress.com
Get started