How to Use AuxData in Queries

I customize the Altiris Helpdesk Solutions since many years and never did find useful the AuxData because of the Query limitation.

Finally, one day, I decided to spend some time with my friend Geoffroy Perrier and we found the solution.

First you have to create a view using this script:

-- Begin of the create view script
Use Altiris_Incidents
Go

create view ZFAB_vAuxData
as
select id,x.value('(value)[1]','nvarchar(MAX)') AS colvalue, x.value('(name)[1]','nvarchar(MAX)') AS colname
From 
(
select id, cast(auxdata as xml) auxdata from workitem
)
s
CROSS APPLY auxdata.nodes('AuxDataDataSet/*') det(x)

-- End of the create view script

Next, to query the workitems with their AuxData you have to join the workitem_current_view or the workitem_detail_view with the ZFAB_vAuxData view using the id column:

-- Begin of Query
Select * from workitem_current_view
inner join ZFAB_vAuxData on workitem_current_view.workitem_id = ZFAB_vAuxData.id
-- End of Query

By this way you will display as records as entries in the AuxData container for the current workitem record.

To filter on a member of the AuxData container add a condition on the colname column of the ZFAB_vAuxData view such as:

-- Begin of Query
Select * from workitem_current_view
inner join ZFAB_vAuxData on workitem_current_view.workitem_id = ZFAB_vAuxData.id
Where colname = 'survey_complete'
-- End of Query

This will return a colvalue value of 'false' or 'true' depends if the satisfaction survey was sent or not.

It is also possible to combine the conditions such as:

-- Begin of Query
Select * from workitem_current_view
inner join ZFAB_vAuxData on workitem_current_view.workitem_id = ZFAB_vAuxData.id
Where colname = 'survey_complete' and colvalue = 'false'
-- End of Query

To use the ZFAB_vAuxData view in the Altiris reports use the following script under SQL:

-- Begin of create view in Altiris
Use Altiris
Go
Create view HD_ZFAB_vAuxData as
Select * from altiris_incidents.dbo.ZFAB_vAuxData

-- End of create view in Altiris