Reporting SQL Queries

HI All,
I am trying to generate a report on Camunda history data to show, all daily cases and their variables. I came across a very useful link which has some reporting queries

However, can any one give me a sample of sql query where considering my process1 has 2 variables, “Name” which is String and “Account” which is serializable java object. I want to select the same from history procinst and varinst tables and report on them.

Any help is appreciated.

Thanks,
Ashish

Out of curiosity, what does “report on them” mean in this case? If you have a query to extract the Account variables, since they’re serialized java objects, you’ll end up with a blob of bytes from the ACT_GE_BYTEARRAY table. Is that really what you ultimately what you want?

Hi,
Nope. Basically we want to report on the actual values inside bytearray. e.g. Account byte array has "{“AccountNumber”: “234324”, “AccountType”: “Consumer”}. I want to report on that data.

Report on them means create a report which user can view them as a list of columns where AccountNumber, AccountType would be the columns of that report.

One of the options would be to get this data using rest api and show it. But I was looking to see if this can be read using an SQL query. We have Oracle DB.

sorry maybe for “stupid” question but i’m a newbie… what would be the difference then? thanks

Doing so will give us different architecture style and we can extract the data using same sql way into other data warehouse in future. I think performance as well will be better.

The REST API is probably your most straightforward route here. Along the lines of what is mentioned here, in order to do what you’re looking for, you would have to reverse the java serialization in order to make sense of the data. Outside of the java context, this is going to be custom and not straightforward. You might as well lean on an API to do that heavy lifting for you.