Query Item Descriptions - automated?

[dougp]

We use MS SQL Server and store column descriptions as extended properties in the database.  Is there an easy, automated way to get those into the Comments property of each query item in a data module?  Or am I stuck either manually entering thousands of descriptions?  Would this be possible using the Cognos SDK?

I haven't yet poked at data modules in the Content Store database.  Would it be possible to update this directly in SQL?

[bus_pass_man]

The 11.1.7 SDK has endpoints for working with modules so yes it should be possible.

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_api.doc/swagger_ca.json

https://www.ibm.com/support/knowledgecenter/SSEP7J_11.1.0/com.ibm.swg.ba.cognos.ca_api.doc/c_ca_api_rest.html

[dougp]

Bummer.  I'm still on 11.0.13.  No API.

[ashley]

It's not usually recommend messing around with the content store, but the JSON of a data module is stored in CMOBJPROPS86 in column cbasedef. I never 100% tested all of this either but seemed to get somewhere with the below.

You can run the following recipe on the value of column cbasedef to return the JSON: https://gchq.github.io/CyberChef/#recipe=From_Base64('A-Za-z0-9%2B/%3D',true)Gunzip()

Make the changes to the JSON, then run this recipe to convert it back: https://gchq.github.io/CyberChef/#recipe=Gzip('Dynamic%20Huffman%20Coding','','',false)To_Base64('A-Za-z0-9%2B/%3D') and then update the cbasedef column value with the new one.

I never got round to trying to automate using Python and the above methods of encoding/decoding, as we upgraded to 11.1.7 so will use the API at some point.

[dougp]

Thanks for trying, but when I go to...

Code Select Expand

https://gchq.github.io/CyberChef/#recipe=From_Base64('A-Za-z0-9%2B/%3D',true)Gunzip()

...and paste in the value from CBASEDEF, I get...

Code Select Expand

Gunzip - invalid file signature:80,75

Two things I notice:
The URL changes to

Code Select Expand

https://gchq.github.io/CyberChef/#recipe=From_Base64('A-Za-z0-9%2B/%3D',true)Gunzip()?input={some random crap that doesn't look like my CBASEDEF value}
My CBASEDEF value is 1208 characters long, so I doubt it's being truncated in the output in SSMS.

[dougp]

But if I use the "random crap" as input (in the box, not in the URL), the output matches my CBASEDEF value.

[ashley]

Apologies, I just noticed you said this was on 11.0.13 and my example was from an early 11.1 (before the API), so I guess they changed something with how it is stored (hence can never really rely on the content store).

Looks like for 11.0.13, the following recipes are done:

To get the spec: https://gchq.github.io/CyberChef/#recipe=From_Base64('A-Za-z0-9%2B/%3D',true)Unzip('',false). Once you've done that, you'll see in the output a little x which you can click.

To convert back to base64: https://gchq.github.io/CyberChef/#recipe=Zip('file.txt','','','Deflate','MSDOS','Dynamic%20Huffman%20Coding')To_Base64('A-Za-z0-9%2B/%3D')

Again, not sure how the above could be automated somewhere :o

[dougp]

You said JSON, but I see XML.  Were you mistaken, or is that another change that was made?  XML in 11.0.13 and JSON in 11.1.7?

[ashley]

Indeed, seems to have changed between the two as it is JSON in 11.1.7.