Export list of all KB to Excel

Maria_jandt

Hi, 
I tried to export all KB articles to Excel to get a list of them. We need to have the owner column and category column but they only shows binary, we do not get the user id or categoryname.
Anyone have an idea så populate a list with all kb articles, with owner, category etc.

Regards
Maria

Roland_Kind

something like this could work

select kb.ArticleID, kb.Title, kb.VendorArticleID, usr.DisplayName, kb.Category,CatStr.DisplayString from KnowledgeArticle kb

left outer join CI$User Usr with (nolock) on kb.Owner=usr.Id

left outer join DisplayString CatStr with (nolock) on CatStr.ElementID=kb.Category and CatStr.LocaleID = 'ENU'

Roland_Kind

It depends how you create the export - did you made a SQL query ? if yes - can you post the query

Maria_jandt

select ArticleID, Title, VendorArticleID, Owner, Category from KnowledgeArticle

ArticleID, Title, VendorArticleID are OK
But, Owner and category onlys shows binary code, i guess they get the data from Another table, which one I don´t know.,

Roland_Kind

something like this could work

select kb.ArticleID, kb.Title, kb.VendorArticleID, usr.DisplayName, kb.Category,CatStr.DisplayString from KnowledgeArticle kb

left outer join CI$User Usr with (nolock) on kb.Owner=usr.Id

left outer join DisplayString CatStr with (nolock) on CatStr.ElementID=kb.Category and CatStr.LocaleID = 'ENU'

Maria_jandt

Perfect! owner now works. But Category still is in binary.

Roland_Kind

strange ... in my dev. env this works

on the right side "Hardware" ...

314 test  roki FE31BC1E-FD34-99C1-351E-218702F2A58C Hardware

what is your default language .. is it english ? 

try changing "ENU" to your default language ... e.g. "DEU" for german

Maria_jandt

Justin_Workman

The 'DisplayString' is the category.  In your select statement do "CatStr.DisplayString AS 'Category'" and omit kb.Category.

Maria_jandt

Thanks for your help! 

 

Maria_jandt

Hi again, 

We have upgrade SCSM to 2016 and with it all cireson apps.

This worked Before but now when we run it I only get guids on the category list, so the join does not seeme to work anymore. Anyone have any idéa ??

select kb.ArticleID, kb.Title, kb.VendorArticleID, usr.DisplayName, kb.Category,CatStr.DisplayString from KnowledgeArticle kb

left outer join CI$User Usr with (nolock) on kb.Owner=usr.Id

left outer join DisplayString CatStr with (nolock) on CatStr.ElementID=kb.Category and CatStr.LocaleID = 'SVE'