How to get useful Nintex Workflows Analytics from SharePoint?

Posted on Updated on

Nintex is still one of the most effective Workflow platforms, and it have complete suite with SharePoint.

Nintex with SharePoint On-Prem is still hard to be managed and maintained, this before it reaches its threshold very quickly, Nintex stated that its threshold is 30 Million WorkflowProgress items per Nintex Database. To understand what does this means I will quickly describe How Nintex data is stored in Databases, and this is for SharePoint 2016, SharePoint 2013, SharePoint 2010, & also SharePoint 2007.

Every Nintex worklflow is broken down to something named Workflow Instances, and each workflow instances is break down to workflow Progress.
Assume we have a Vacation Approval workflow, every time this workflow runs it generate a workflow instance,  if you re-run it on a single item 100 times, it will generates 100 workflow instances in the Nintex database. depends on the number of components it consist, workflow progress will be generated, so if this Workflow consists of 3 controls (Pause, Query list, Send Mail) then it will generate 3 WF progress records per run.

To get some useful info from the NinteDatabase, run the below commands:


create PROC [dbo].[WorkflowProgressProc] @db nvarchar(50)
AS
IF DB_ID(@db) IS NULL /*Validate the database name exists*/
BEGIN
RAISERROR('Invalid Database Name passed',16,1)
RETURN
END<
BEGIN
CREATE TABLE #tmpBus
(
WorkflowProgress INT,
WorkflowInstance INT,
size int,
NumOfSites int
)
declare @WorkflowProgress int,
@WorkflowInstance int,
@size int,
@NumOfSites int
select @WorkflowProgress= COUNT(*) from dbo.WorkflowProgress (nolock)
select @WorkflowInstance=COUNT(*) from dbo.WorkflowInstance (nolock)
select @size=size * 8.0 / 1024 from sys.master_files where name like @db
select @NumOfSites= count(*) from ( Select SiteID
From WorkflowProgress P inner join WorkflowInstance I
on (P.InstanceID = I.InstanceId)
group by I.SiteID) x
INSERT INTO #tmpBus values(@WorkflowProgress,@WorkflowInstance,@size,@NumOfSites)
END
select * from #tmpBus

After that, run the stored procedure and give the database name as a parameter

exec [WorkflowProgressProc] [NintexWF2013DB]

The result will be as follow:

From experience in Nintex, the threshold can be much more, even till 50 millions item if you have powerful servers. but other than this, you will face alot of delays and Nintex workflow errors.

Also, to get more details about SharePoint Sites vs WorkflowProgress numbers.


Select I.SiteID, Count(P.WorkflowProgressID) NoWFProgress,Count(distinct I.InstanceID) NoWFInstance , MAX(p.TimeStamp) Last_Activity
From WorkflowProgress P inner join WorkflowInstance I (nolock)
on (P.InstanceID = I.InstanceId)
group by I.SiteID
order by I.SiteID

This will give you a good figures about Nintex Analytics in your SharePoint environment.

Advertisements

“Access Denied” to Access Requests list or “Request approval failed” when you process a pending request in SharePoint Online

Posted on Updated on

I have run through a problem today during my work on one of the clients, and this sometimes happened mainly with sites that is migrated to SharePoint Online

 

 

 

This works perfectly fine !

Draw PowerBI Map from IP Addresses

Posted on

I had a request from a client, where he has an Excel sheet with list of IP addresses as well as other information, and he want to convert those IP address to the equivalent country and list in PowerBI.

Some Analytical tool like Splunk have this is a built in function, but for PowerBI you need to build it your self, and we will rely on a free web-service to implement this, so lets start

  1. Go to http://freegeoip.net/json/
  2. You should see the default data, which is your data retrieved by your IP address.
  3. Data displayed will be Country Code, Country Name, Region Code, Region Name, City, Zip Code(if applicable), Time Zone, Latitude, Longitude, and Metro Code
  4. if you entered specific IP address, correspondence data will be displayed, example: http://freegeoip.net/json/148.50.4.18
  5. Now, lets get back to PowerBI, Open your , Click Get Data, and then select Blank Query
  6. Click View, then Advanced Query, and add the Query in the right text
  7. Copy paste this Query and enter it in the text box of the Advanced Query
    let
    Source = (#"IP Address" as text) => let
    Source = Json.Document(Web.Contents("http://freegeoip.net/json/" & #"IP Address")),
    #"Converted to Table" = Record.ToTable(Source),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
    in
    #"Promoted Headers"
    in
    Source
  8. Click Done, and rename the function with a meaningful name like : fn_GetRegion
  9. Now, you need to create a column with this function, to do so, from Edit Queries go to Add Column > Invoke Custom Function
  10. Enter the data as shown below
  11. The column will appear at the end of your table, now we need to Expand the column by click on the double arrow icons as in the snapshot below
  12. Now, all the columns is appeared clearly.
  13. and you can use it to draw your maps.

Thanks to guavaq who shows us the solution in this post

List SharePoint Online Sites in PowerShell using PnP

Posted on Updated on

How to Retrieving List of SharePoint Online Sites in PowerShell using PnP.

Currently PnP is considered to be the most modern way to deal with SharePoint Online and On-Prem, because it provides one interface for both

Quick handy script to do that, and you can take the output to write it in CSV file or import it in SharePoint List, or store it in any other repository

1. You need first to download PnP PowerShell release, from here
https://github.com/SharePoint/PnP-PowerShell/releases

2. Download the library, and un-zip the file
3. Now you will need to relocate the module files, you should find it in this paths
C:\Program Files (x86)\SharePointPnPPowerShellOnline\Modules\SharePointPnPPowerShellOnline\SharePointPnPPowerShellOnline.psd1 


$pnpmodulepath="C:\Program Files (x86)\SharePointPnPPowerShellOnline\Modules\SharePointPnPPowerShellOnline\SharePointPnPPowerShellOnline.psd1"
Import-Module $pnpmodulepath -Force
#Specify tenant admin and site URL
$User = "admin.tenat@withavanade.com"
$Password = ConvertTo-SecureString -String "password" -AsPlainText -Force
$Tenant = "https://o365accelerator-admin.sharepoint.com"
$CurrentCred = New-Object Management.Automation.PSCredential ($User, $Password)
Connect-PnPOnline -TenantAdminUrl $Tenant -Url $Tenant -Credentials $CurrentCred
$AllSites = Get-PnPTenantSite
foreach ($site in $AllSites )
{
if($site.Url.Contains("yourcompany.sharepoint.com")) #This if condition to dismiss public sites, but you can remove it
{
Connect-PnPOnline -Url $Site.Url -Credentials $CurrentCred
Write-Host ($site.Url +";"+
$site.Title +";"+
$site.Lcid +";"+
$site.LastContentModifiedDate +";"+
$site.LockState +";"+
$site.StorageMaximumLevel +";"+
$site.StorageUsage +";"+
"")
Disconnect-PnPOnline
}
}

Upload file to SharePoint Online using PowerShell

Posted on

This script help you to upload your file or document to SharePoint library using PowerShell

  1. Download SharePoint Online client library from here , and install it. (If you already have it, please ignore this step)
  2. Create a folder named Temp on your C: drive, and put the Excel/CSV file in it
  3. Modify the parameters below and run the below script.


#Specify tenant admin and site URL
$User = "site.admin@tenantname.onmicrosoft.com"
$Password = "YourPassword"
$SiteURL = "https://tenantname.sharepoint.com/sites/site"
$Folder = "C:\Temp"
$DocLibName = "DocumentLibraryName"
#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files (x86)\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files (x86)\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
#Bind to site collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,(ConvertTo-SecureString $Password -AsPlainText -Force));
$Context.Credentials = $Creds
#Retrieve list
$List = $Context.Web.Lists.GetByTitle($DocLibName)
$Context.Load($List)
$Context.ExecuteQuery()
#Upload file
Foreach ($File in (dir $Folder -File))
{
$FileStream = New-Object IO.FileStream($File.FullName,[System.IO.FileMode]::Open)
$FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation
$FileCreationInfo.Overwrite = $true
$FileCreationInfo.ContentStream = $FileStream
$FileCreationInfo.URL = $File
$Upload = $List.RootFolder.Files.Add($FileCreationInfo)
$Context.Load($Upload)
$Context.ExecuteQuery()
}
#Upload completed

Let’s follow protocols: HTTP

Posted on Updated on

Some of us know a lot about what happen when we write URL in our browser and hit enter or when an app connect to a web service or API to get data or perform an operation, but let us discuss exactly what is going on between the browser or any client and the URL  or any server resource like HTML page, files, or web service … etc. As many knows HTTP sort for Hypertext Transfers Protocol so every thing happen is written as text on this dialog between the client and the server but what is exactly this message is and what is the server replay that what we will try to clarify.

First let us discuss the client which may be web browser, app, or even an web debugging tools like fiddler, Post Man, SOAP UI. which I think we need to talk about after HTTP.

Client Message or HTTP Request, in a happy world the request between client and server simply done by sending the request with a certain structure (we will discuss it in details) to the server and wait for the server response, but this request can go throw a long way by passing to proxy, gateway, and/or tunnel. In sort, A proxy is a forwarding agent, receiving requests for a URL in its absolute form, rewriting all or part of the message, and forwarding the reformatted request toward the server identified by the URL. A gateway is a receiving agent, acting as a layer above some other server(s) and, if necessary, translating the requests to the underlying server’s protocol. A tunnel acts as a relay point between two connections without changing the messages; tunnels are used when the communication needs to pass through an intermediary (such as a firewall) even when the intermediary cannot understand the contents of the messages.

 

 

HTTP Request message structure (Header and body):

1- Request Line contains [Request Method] + [Server Resource URL] + [HTTP Protocol version] + [Carriage return] + [Line feed]

ex: GET /pdf/book.pdf HTTP/1.1

2- Request header fields list, all header fields are optional except the host field. and the field structure is [Field Name] + [Colon] + [Field Value] + [Carriage return] + [Line feed]

ex: Host: tecgang.wordpress.com

3-Empty Line  [Carriage return] + [Line feed] in order to split between request header and request body

4-[Optional] Request body, which can be anything, binary file, text, JSON data or any data you want to send it to the server

HTTP Request methods are:

GET: The GET method requests a representation of the specified resource. for example to get an image or html file.

HEAD: The HEAD method asks for a response identical to that of a GET request, but without the response body. for example if you want to get a file but you want to know the file length first then you request a HEAD. (Useful for retrieving meta-information)

POST: Used to ask the server to accept the entity enclosed in the request as a new entity of the resource identified by the URL.

PUT: Used to ask the server to accept the entity enclosed in the request to store an entity of the resource identified by the URL. if it not exists its create new one or update existing one.

 DELETE: The DELETE method deletes the specified resource.

TRACE: The TRACE method echoes the received request so that a client can see what (if any) changes or additions have been made by intermediate servers.

OPTIONS: The OPTIONS method returns the HTTP methods that the server supports for the specified URL.

CONNECT: The client asks an HTTP Proxy server to tunnel the TCP connection to the desired destination. The server then proceeds to make the connection on behalf of the client. Once the connection has been established by the server, the Proxy server continues to proxy the TCP stream to and from the client.

PATCH: The PATCH method update partial resources.

 

 

HTTP Response message structure (Header and body):

1- Status Code and message [HTTP Version] + [Status Code] + [Message] + [Carriage return] + [Line feed]

ex: HTTP/1.1 200 OK

2- Response header fields, all header fields are optional and the field structure is [Field Name] + [Colon] + [Field Value] + [Carriage return] + [Line feed]

ex: Content-Type: application/pdf

3- Empty Line  [Carriage return] + [Line feed] in order to split between response header and response body

4-[Optional] Request body, which can be anything, binary file, text, JSON data or any data send by the server

 

 

Now you can see whats happen if you type in your browser an url like this one https://tecgang.wordpress.com/index.php, the dialog will be:

The Request:

GET /index.php HTTP/1.1
Host: tecgang.wordpress.com

The Response:

HTTP/1.1 200 OK
Content-Type: text/html; charset=UTF-8
Content-Encoding: UTF-8
Content-Length: 138

<html>
<head>
<title>An Example Page</title>
</head>
<body>
Hello World, this is a very simple HTML document.
</body>
</html>

Retrieving Office 365 message center using PowerShell

Posted on Updated on

How to Retrieving Office 365 message center Information using PowerShell

Quick handy script to do that, and you can take the output to write it in CSV file or import it in SharePoint List, or store it in any other repository

1. You need to download Office 365 Service Communications PowerShell Module from Get Hub
https://github.com/mattmcnabb/O365ServiceCommunications

2. Download the library, and un-zip the file
3. Now you will need to relocate the module files, you should find it in this paths
C:\Users\Administrator\Downloads\O365ServiceCommunications-master\O365ServiceCommunications-master\O365ServiceCommunications


Import-Module C:\Users\Administrator\Downloads\O365ServiceCommunications-master\O365ServiceCommunications-master\O365ServiceCommunications
# Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "C:\Program Files (x86)\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files (x86)\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
# Specify tenant admin"
$User = "tenant.admin@company.onmicrosoft.com"
$Pass = "password"
$cred = New-Object System.Management.Automation.PSCredential($User,(ConvertTo-SecureString $Pass -AsPlainText -Force));
# gather events from the Service Communications API
$MySession = New-SCSession -Credential $cred
$Events = Get-SCEvent -EventTypes Message -PastDays 100 -SCSession $MySession |
Select-Object Id, Title, StartTime, Service, EventType, Status, @{n='Message';e={$_.messages[0].messagetext}}
foreach ($Event in $Events)
{
$Event.Id
$Event.Title
$Event.StartTime
$Event.Message
$Event.Service
$Event.EventType
$Event.Status
}
Write-Host "Completed!"