#BPTechTips - How to obtain license information

[EmersonF]

Guys, good morning, I noticed that some people are wanting a way to get information about the licenses, so I had the idea to work on it, the result follows, a query that returns information such as company name, Expiration date and how many days are left missing, I hope it's useful for you:

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​

#BPTechTips #License #Helpfull​​​​

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

[ritansh.jatwani]

Great stuff @EmersonF. Thanks for sharing. :)​

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

[JerinJose]

Emerson,

same achieved using VB 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()

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

[EmersonF]

Wow, interesting, do you use this inside BP in a code stage?

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

[EVIPUTI]

@EmersonF I do know that we can get the license info from system>license screen which i am assuming makes it available in DB ! Can we simply use that or is it something else we are trying to achieve here mate ?​

------------------------------
------------------------------
Vipul Tiwari
Senior Process Simplification Developer
Amazon
------------------------------
------------------------------

[EmersonF]

That's exactly what we're doing 😄

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

[EVIPUTI]

@EmersonF Ahhaa Gotchya , I feel BP should create a standard VBO for this . To give license level information . 😛 ​

------------------------------
------------------------------
Vipul Tiwari
Senior Process Simplification Developer
Amazon
------------------------------
------------------------------