topic License Details using API or DB Query in University Forum

License Details using API or DB Query

ShivakumarKadro — Mon, 25 Oct 2021 02:09:00 GMT

Hi All,

Is there a way to fetch available Blueprism session license details? e.g. Via Webservice, DB query call

Thanks,
Shiva

------------------------------
Shivakumar Kadrolli
------------------------------

RE: License Details using API or DB Query

ritansh.jatwani — Mon, 25 Oct 2021 19:50:00 GMT

Hi @ShivakumarKadro,

You can run the below query to fetch the license details:

select * from [dbo].[BPALicense]

------------------------------
Ritansh Jatwani Senior Consultant
Consultant
EY
Gurgaon
*If you find this post helpful mark it as best answer
------------------------------

RE: License Details using API or DB Query

bruce.liu — Tue, 26 Oct 2021 01:26:00 GMT

Hi Shivakumar,

If you were referring to the total license count available in a Blue Prism environment, I am afraid there does not exist an official way to retrieve it. All license information in DB is encrypted, it will not be easy to take advantage of. But nothing would prevent you from writing an automation to Blue Prism Interactive Client itself to get exactly that.

If you need to know the current license usage, a simple

SELECT COUNT(*) FROM BPASession WHERE statusid in (0,1)

would be suffice.

------------------------------
Bruce Liu
Senior Product Consultant, Professional Services
Blue Prism
Australia/Sydney
------------------------------

RE: License Details using API or DB Query

ShivakumarKadro — Tue, 26 Oct 2021 12:50:00 GMT

Thanks Bruce! This should help me and I see set of views which provides data in structured way.
Are there any APIs? if no any plans to be considered in future releases?

------------------------------
Shivakumar Kadrolli
------------------------------

RE: License Details using API or DB Query

EmersonF — Tue, 26 Oct 2021 13:22:00 GMT

@ShivakumarKadro

I set up for you to be able to view the information of your licenses through the bank I hope it helps!!
If it helps, mark as best answer

select
   issue_to as 'Name of Company',
   FORMAT(License_expires_on,'dd/MM/yyyy') 'Date of validate',
   datediff(day, getdate(), License_expires_on) 'Days Remaining'
from
   (
      select
         cast (lic.licensexml as xml).value('(/license//licensee/node())[1]', 'nvarchar(10)') as [issue_to],
         cast(cast(lic.licensexml as xml).value('(/license//expires/node())[1]', 'nvarchar(10)') as date) as [License_expires_on],
         cast(lic.licensexml as xml).value('(/license//maxconcurrentsessions/node())[1]', 'nvarchar(10)') as [available_licences] 
      from
         (
            select
               convert (varchar(2000), cast(' ' as xml).value('xs:base64Binary(sql:column("base64_column"))', 'VARBINARY(2000)')) AS LICENSEXML 
            FROM
               (
                  select
                     licensekey as base64_column 
                  from
                     bpalicense
               )
               a
         )
         lic
   )
   as pog 
where
   pog.License_expires_on >= getdate() 
order by
   pog.License_expires_on

------------------------------
Emerson Ferreira
Sr Business Analyst
Avanade Brasil
Recife
+5581988869544
If my answer helped you? Mark as useful!
------------------------------

RE: License Details using API or DB Query

ewilson — Tue, 26 Oct 2021 13:54:00 GMT

Hi @ShivakumarKadro,

NOTE: THIS IS AN UNSUPPORTED SOLUTION

As @Bruce Liu mentioned, there's no officially supported way to pull the count of available licenses, but you can do it by using Blue Prism to automate the Interactive Client. With that said, if you want to see the contents of the license file there is a DLL in the BP installation folder called BPCoreLib.dll. If you reference that DLL from within a VBO you can use a code stage and create an instance of the KeyInfo object. With that, you can pass in the value of the licensekey column from the dbo.BPLicense table. The license info will be decoded and presented to you via the object.

The below example is from a small C# console application I used to test this.

Ex.

KeyInfo ki = new KeyInfo("YOUR_KEY_INFO_HERE");
Console.WriteLine("Num Concurrent Session: " + ki.NumConcurrentSessions);
Console.WriteLine("Num Published Processes: " + ki.NumPublishedProcesses);
Console.WriteLine("Num Resource PCs: " + ki.NumResourcePCs);
Console.WriteLine("Expiry Date: " + ki.ExpiryDate);
Console.WriteLine("Press any key to continue.");
Console.ReadLine();

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

RE: License Details using API or DB Query

ShivakumarKadro — Tue, 26 Oct 2021 20:00:00 GMT

Thanks!

------------------------------
Shivakumar Kadrolli
------------------------------

RE: License Details using API or DB Query

ShivakumarKadro — Tue, 26 Oct 2021 20:03:00 GMT

Does Blueprism (BP) support nolock SELECT query to BP DB? I am asking this if we need to pull the data from BP to any other reporting tool like Tablaeu or Kibana or ELK?

------------------------------
Shivakumar Kadrolli
------------------------------

RE: License Details using API or DB Query

bruce.liu — Wed, 27 Oct 2021 02:30:00 GMT

Hi Shivakumar,

It depends on how you use your query and the scenario you want to achieve. Blue Prism or not, it does not really matter. From SQL Server point of view it does support NOLOCK hints, and all Blue Prism databases must be hosted in SQL Server.

------------------------------
Bruce Liu
Senior Product Consultant, Professional Services
Blue Prism
Australia/Sydney
------------------------------

RE: License Details using API or DB Query

ShivakumarKadro — Wed, 27 Oct 2021 03:32:00 GMT

I am asking from Blue prism product best practices perspective and BP recommendation .
Can we have select queries to BP DB to fetch the data for Kibana/Tablaeu. I mentioned about nolock to highlight retrieving the data without locking the table row.

------------------------------
Shiva
------------------------------

RE: License Details using API or DB Query

bruce.liu — Wed, 27 Oct 2021 04:36:00 GMT

Hi Shiva,

Please understand it is not a simple yes and no question if this was your expectation. All that is certain is the fact that it is possible to issue a select query with nolock on Blue Prism database. There was little information given on what exactly you were trying to achieve by having the nolock hints, and how it would be different than doing wiithout it. There are also other important questions that are not clear, for example, the data you are retrieving, the frequency of the query, the volume of the data returned, and what else may be happening to the database while you issuing those queries etc etc. It will not be easy to answer. I would ask you to consider those factors before making a call on what is the most appropriate way to query data out of Blue Prism.

It is also important to understand that we do not offer direct support on work you developed in getting data out from our database, unless it is a script provided by the Blue Prism team to you directly. All work done by you is considered customisation, and you will need to use best judgement to do what you think is right, and take on the risks knowing if anything goes wrong, e.g. database goes down, Blue Prism will not be liable for it.

Hope this is clear.

------------------------------
Bruce Liu
Senior Product Consultant, Professional Services
Blue Prism
Australia/Sydney
------------------------------

RE: License Details using API or DB Query

JerinJose — Wed, 27 Oct 2021 04:45:00 GMT

Hi Shiva,

you can get total license details by code

Public Function GetLicenses() As DataTable

Dim LicenseInfo As List(Of KeyInfo) = app.gSv.GetLicenseInfo()

Dim dt As New DataTable
dt.Columns.Add("Owner", GetType(System.String))
dt.Columns.Add("Start Date", GetType(System.DateTime))
dt.Columns.Add("Expiry Date", GetType(System.DateTime))
dt.Columns.Add("Sessions", GetType(System.Int16))

For Each ki As KeyInfo In LicenseInfo
Dim dr As DataRow = dt.NewRow
dr("Owner") = ki.LicenseOwner()
dr("Start Date") = ki.StartDate()
dr("Expiry Date") = ki.ExpiryDate()
dr("Sessions") = ki.NumConcurrentSessions()
dt.Rows.Add(dr)
Next
Return dt

End Function

License_Information = GetLicenses()

output will be a collection, and use query from @Bruce Liu to get total pending and running sessions, subtract the value from total sessions allowed in license details you will get available sessions in your current environment (tested in version 7 not sure if it will work on lower versions this code lists all licenses both active and inactive)

------------------------------
Jerin Jose
Technical Product Owner
EY
Asia/Kolkata
*"If you find this post helpful mark it as best answer, .*
------------------------------

RE: License Details using API or DB Query

chris.strong — Wed, 27 Oct 2021 07:59:00 GMT

Hello Shivakumar,

You've had lots of good answer here and it sounds like the ability to query the license is something other people have solved in different ways.

You have some solutions for no, but I think it would be useful to submit an idea for the Blue Prism RESTful API to be extended to support License Information.

PS: I know the current Blue Prism API (7.0.0) does not support this information, because we have been focused on the surfacing the Control Room API first. We will be continuing to extend the API capabilities over time, so why not submit the idea and see if the community shares their thought and the usefulness of the capability.

I'll keep an eye out for it.

King regards

------------------------------
Chris Strong
Product Manager
Blue Prism
------------------------------

RE: License Details using API or DB Query

ShivakumarKadro — Wed, 27 Oct 2021 13:18:00 GMT

Sure! Thanks

------------------------------
Shivakumar Kadrolli
------------------------------

RE: License Details using API or DB Query

ShivakumarKadro — Wed, 27 Oct 2021 13:24:00 GMT

Thanks Bruce! I was not looking for Yes/No answer, I was looking for the details on this topic, which you provided and you answered my query.

------------------------------
Shivakumar Kadrolli
------------------------------

RE: License Details using API or DB Query

ShivakumarKadro — Thu, 28 Oct 2021 23:32:00 GMT

All
I found that V7 REST has API "Return a list of sessions in the environment" to get a list of sessions along with all other details. As each active session consumes one license, can we use this API and filter the status to get active number license/session?

------------------------------
Shivakumar Kadrolli
------------------------------

RE: License Details using API or DB Query

bruce.liu — Thu, 28 Oct 2021 23:47:00 GMT

Hi Shivakumar,

It is doable, but I question if it is the most appropriate method.

Note that the session API call is paged. You may need to issue multiple calls using a paging token to get all sessions meeting your criteria. The biggest drawback of this approach - in order to know a total session count of a type, you must pull all session details from the server. It is just too expensive to my liking. Another consideration is the timing. The moment you have had all sessions returned and performed the count, the system may have moved on, i.e. the real time license utilisation may be something else. You need to ask yourself if this would be an issue to your scenario.

All in all, I would still think the SQL query is the best solution to get you the license utilisation based on real time usage info, with least amount of resources and it is also the quickest way available to my knowledge. It is actually the exact query behind the Blue Prism license utilisation dashboard tile as well.

I have created a license utilisation script to based on historical session records. You can take a look at it to see if you want to adopt it. Note that it is a solution based on SQL Server queries as well. Link to the asset: https://digitalexchange.blueprism.com/dx/entry/3439/solution/license-utilisation-sql-script

I would also suggest you raise an idea in the Ideation Portal, so that our product team can consider add an API endpoint to return exactly what you need without going through session endpoint specifically.

------------------------------
Bruce Liu
Senior Product Consultant, Professional Services
Blue Prism
Australia/Sydney
------------------------------

RE: License Details using API or DB Query

ShivakumarKadro — Mon, 01 Nov 2021 01:59:00 GMT

Thanks!

------------------------------
Shivakumar Kadrolli
------------------------------

Re: RE: License Details using API or DB Query

cxcarl — Mon, 07 Apr 2025 14:36:22 GMT

Are you able to add a column to get the actual license count per installed license?