Using:
IBM Cognos Analytics 11.0.7
Microsoft SQL Server 2016
I'll start off by admitting that my XML parsing knowledge is weak.
I'm trying to use the Cognos Audit database to get useful information about report parameters that users have chosen when running reports. (Specifically, I suspect some users are downloading the entire data mart.) I have set up parameter logging. When I run a query to get the parameter info, I see it in the column named COGIPF_PARAMETER_VALUE_BLOB, which is ntext containing XML. I am having trouble using this XML with other data from the Audit database, so I want to script out a query that will present it as a view.
I created a simple report based on sample data and included several prompts. When I run the report, answer the prompts, and click finish, I see the resulting parameters in the Audit database:
<parameterValues xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" xmlns:bus="http://developer.cognos.com/schemas/bibus/3/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="parameterValue[5]">
<item xsi:type="parameterValue">
<name xsi:type="xs:string">Date</name>
<value xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="parmValueItem[1]">
<item xsi:type="simpleParmValueItem">
<inclusive xsi:type="xs:boolean">true</inclusive>
<display xsi:type="xs:string">Feb 7, 2019</display>
<use xsi:type="xs:string">2019-02-07T00:00:00.000</use>
</item>
</value>
</item>
<item xsi:type="parameterValue">
<name xsi:type="xs:string">ValueSingle</name>
<value xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="parmValueItem[1]">
<item xsi:type="simpleParmValueItem">
<inclusive xsi:type="xs:boolean">true</inclusive>
<display xsi:type="xs:string">Austria</display>
<use xsi:type="xs:string">Austria</use>
</item>
</value>
</item>
<item xsi:type="parameterValue">
<name xsi:type="xs:string">Text</name>
<value xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="parmValueItem[1]">
<item xsi:type="simpleParmValueItem">
<inclusive xsi:type="xs:boolean">true</inclusive>
<display xsi:type="xs:string">asdf</display>
<use xsi:type="xs:string">asdf</use>
</item>
</value>
</item>
<item xsi:type="parameterValue">
<name xsi:type="xs:string">DateRange</name>
<value xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="parmValueItem[1]">
<item xsi:type="boundRangeParmValueItem">
<inclusive xsi:type="xs:boolean">true</inclusive>
<end xsi:type="simpleParmValueItem">
<inclusive xsi:type="xs:boolean">true</inclusive>
<display xsi:type="xs:string">Feb 14, 2019</display>
<use xsi:type="xs:string">2019-02-14T23:59:59.999</use>
</end>
<start xsi:type="simpleParmValueItem">
<inclusive xsi:type="xs:boolean">true</inclusive>
<display xsi:type="xs:string">Feb 6, 2019</display>
<use xsi:type="xs:string">2019-02-06T00:00:00.000</use>
</start>
</item>
</value>
</item>
<item xsi:type="parameterValue">
<name xsi:type="xs:string">ValueMultiple</name>
<value xsi:type="SOAP-ENC:Array" SOAP-ENC:arrayType="parmValueItem[3]">
<item xsi:type="simpleParmValueItem">
<inclusive xsi:type="xs:boolean">true</inclusive>
<display xsi:type="xs:string">Canada</display>
<use xsi:type="xs:string">Canada</use>
</item>
<item xsi:type="simpleParmValueItem">
<inclusive xsi:type="xs:boolean">true</inclusive>
<display xsi:type="xs:string">China</display>
<use xsi:type="xs:string">China</use>
</item>
<item xsi:type="simpleParmValueItem">
<inclusive xsi:type="xs:boolean">true</inclusive>
<display xsi:type="xs:string">Denmark</display>
<use xsi:type="xs:string">Denmark</use>
</item>
</value>
</item>
</parameterValues>
Notice that in the parameter named ValueMultiple, I have selected three values: Canada, China, Denmark.
I created this query:
select b.value('(text())[1]', 'varchar(128)') as 'ParamName'
, b.value('(../value/@arrayType)[1]', 'varchar(128)') as 'ParamType'
, b.value('(../value/item/inclusive)[1]', 'varchar(128)') as 'Inclusive'
, b.value('(../value/item/display)[1]', 'varchar(128)') as 'Display'
, b.value('(../value/item/use)[1]', 'varchar(128)') as 'Use'
--, b.value('(../value/item/start/inclusive)[1]', 'varchar(128)') as 'RangeStartInclusive'
--, b.value('(../value/item/start/display)[1]', 'varchar(128)') as 'RangeStartDisplay'
--, b.value('(../value/item/start/use)[1]', 'varchar(128)') as 'RangeStartUse'
--, b.value('(../value/item/end/inclusive)[1]', 'varchar(128)') as 'RangeEndInclusive'
--, b.value('(../value/item/end/display)[1]', 'varchar(128)') as 'RangeEndDisplay'
--, b.value('(../value/item/end/use)[1]', 'varchar(128)') as 'RangeEndUse'
from @x.nodes('/parameterValues/item/name') a(b)
order by 1
Which returns this row:
+---------------+------+---------+---------+------+
| ValueMultiple | true | Canada | Canada | NULL |
+---------------+------+---------+---------+------+
...but does not return a row for China or Denmark. I also want to see...
+---------------+------+---------+---------+------+
| ValueMultiple | true | China | China | NULL |
+---------------+------+---------+---------+------+
| ValueMultiple | true | Denmark | Denmark | NULL |
+---------------+------+---------+---------+------+
Also, you can see that the ParamType returned column contains all NULLs. I tried SOAP-ENC:arrayType, but SQL server told me XQuery [value()]: The name "SOAP-ENC" does not denote a namespace. This seems odd to me (again, limited XML knowledge) because I see xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" on the first line of XML. I can handle this by including something like REPLACE(xml, 'SOAP-ENC:', '') in my query.
Any help is appreciated, but my two main questions at this point are:
How do I make the query output this data to a usable table (including the missing rows)? Will this require an ETL process I could build in SSIS, or is there a good way to perform this in a SQL query?
How can I inspect the value of the SOAP-ENC:arrayType attribute to identify how many elements are in the value? Should I just use the REPLACE code that I suggested, or is there a better way?
I finally worked this out. The query below will produce the results I need. If the output is large, this is really slow. I can use this same technology to write a stored procedure to ETL the data into a table daily. My testing indicates that will reduce query run times by about 99%.
declare @start datetime
declare @end datetime
set @start = {ts '2019-02-06 08:00:00.000'}
set @end = {ts '2019-02-06 14:30:00.000'}
select u.COGIPF_USERNAME
, p.COGIPF_LOCALTIMESTAMP
, r.COGIPF_REPORTNAME
, p.name
, p.inclusive
, p.type
, p.display
, p.[use]
, p.RangeStartInclusive
, p.RangeStartDisplay
, p.RangeStartUse
, p.RangeEndInclusive
, p.RangeEndDisplay
, p.RangeEndUse
from (
-- SQL to see user-selected parameters for report runs
-- this uses the "parameterValues" parameter type
-- join to COGIPF_RUNREPORT on COGIPF_REQUESTID and COGIPF_LOCALTIMESTAMP
select q2.COGIPF_LOCALTIMESTAMP
, q2.COGIPF_REQUESTID
, q2.name
--, item.val.query('.') as 'val'
, item.val.value('inclusive[1]', 'varchar(5)') as 'inclusive'
, item.val.value('(@type)[1]', 'varchar(128)') as 'type'
, case
when item.val.value('(@type)[1]', 'varchar(128)') = 'simpleParmValueItem' then item.val.value('display[1]', 'varchar(128)')
end as 'display'
, case
when item.val.value('(@type)[1]', 'varchar(128)') = 'simpleParmValueItem' then item.val.value('use[1]', 'varchar(128)')
end as 'use'
, item.val.value('(start/inclusive)[1]', 'varchar(128)') as RangeStartInclusive
, item.val.value('(start/display)[1]', 'varchar(128)') as RangeStartDisplay
, item.val.value('(start/use)[1]', 'varchar(128)') as RangeStartUse
, item.val.value('(end/inclusive)[1]', 'varchar(128)') as RangeEndInclusive
, item.val.value('(end/display)[1]', 'varchar(128)') as RangeEndDisplay
, item.val.value('(end/use)[1]', 'varchar(128)') as RangeEndUse
from (
select q.COGIPF_LOCALTIMESTAMP
, q.COGIPF_REQUESTID
, param.item.value('name[1]', 'varchar(128)') as 'name'
--, opt.item.value('value[1]', 'varchar(128)') as 'value'
, param.item.query('.') as 'item'
, cast(replace(replace(param.item.value('(value/@arrayType)[1]', 'varchar(128)'), 'parmValueItem[', ''), ']', '') as int) as 'arrayLen'
from (
select p.COGIPF_LOCALTIMESTAMP
, p.COGIPF_REQUESTID
, cast(replace(replace(replace(replace(cast(p.COGIPF_PARAMETER_VALUE_BLOB as varchar(max)), 'xs:', ''), 'xsi:', ''), 'bus:', ''), 'SOAP-ENC:', '') as xml) as 'params'
from COGIPF_PARAMETER p
where p.COGIPF_PARAMETER_NAME = 'parameterValues'
and p.COGIPF_LOCALTIMESTAMP between @start and @end
--and p.COGIPF_LOCALTIMESTAMP > cast(getdate() as date)
) q
cross apply q.params.nodes('/parameterValues/item') as param(item)
) q2
cross apply q2.item.nodes('/item/value/item') as item(val)
where q2.arrayLen <> 0
) p
inner join COGIPF_RUNREPORT r on r.COGIPF_REQUESTID = p.COGIPF_REQUESTID
and r.COGIPF_LOCALTIMESTAMP = p.COGIPF_LOCALTIMESTAMP
inner join COGIPF_USERLOGON u on u.COGIPF_SESSIONID = r.COGIPF_SESSIONID
where u.COGIPF_LOGON_OPERATION = 'logon'
and u.COGIPF_USERNAME = 'lastname, firstname'