How to find all Knowledge Articles that were first published during the last month

[Janelle3]

Hi wonderful knowledge managers,

I am trying to find only knowledge articles that have had their first version published during the last month, and which are still published and past their valid to date. We have versioning turned on. There doesn’t seem to be any field in the knowledge table that represents the date this knowledge article’s first version was published.

This is what I've got so far:

Table: kb_knowledge
Published on Last Month AND
Workflow is Published AND
Valid to at or after Today AND
Version.Version is 1.0

The problem with this query is that some knowledge articles were published for the first time during the last month, but have now had more recent versions published. These articles would have a version later than 1.0. 

I know I can change the condition to workflow is Published OR Outdated, but that will bring me the outdated versions of the articles in my results. What I really want is the record for Published version 2.0, 3.0 or whatever, if the outdated version 1.0 of the same article was first published during the last month.

I thought the "Base Version" reference might be the first original version of the knowledge article but just seems to be the previous version, which doesn’t really help me either.

Does anyone have an idea of how to build this query?

Kind Regards and Thanks,

Janelle

[Lauren Methena]

Oh, my goodness! This is the question, isn't it?! I've been asked numerous times to come up with a "just published" report, and it's just so much easier/faster to track by the incoming emails or just look at all articles in the KB and then sort by "updated." 

But, to answer your question, the closest I got was using these parameters (see image), and then I sorted by version, which gave me everything that was 1.0. You could then export that info and delete any extraneous results. 

Probably not as satisfactory as you were hoping for - I'm going to keep playing with this some more.

The issue I keep running into is when I add the version number (1.0), the report doesn't yield any results - even though when I take the version number off, I get lots of results, including articles and knowledge blocks with "1.0." 

I got much closer when I wrote Version "contains" 1.0 instead of Version "is" 1.0 (34 records instead of 228) -- but then I still got all the versions that were 11.0, 21.0, 71.0, etc. Not perfect - but I don't understand by changing the operator to "is" would give me no results. 

To be continued ... I hope this helps further the conversation at least! I'll be following.

[Janelle3]

Hi Lauren,

Thank you for your reply! So glad to know I'm not the only one who has struggled with this.

Try Version.Version is 1.0 - I have no idea why this works but it does. 

This is the best I've come up with:

Table: kb_knowledge
Published on Last Month AND
Workflow is one of (Published, Outdated)
Valid to at or after Today AND
Version.Version is 1.0

This gives me close to what I need. I won't get any duplicate results because I'm only looking for version 1.0's published during the month. This also stops me from getting articles which merely had new versions published during the month (because they would be later versions). 

I also include outdated versions that are 1.0, because that represents an article that was published for the first time during the month too. The only thing it might miss is that if that article has now being retired, it will still be included in my list. 

Unfortunately the results will have the link to the outdated article, which is what I'm trying to avoid. But if I'm sending this out in an email/newsletter it's fine, I just copy/paste the article KB number, short description etc without the version.

Cheers,

Janelle

[Lauren Methena]

Thanks for the tip, Janelle! Yeah, I find this whole thing frustrating. I'm not sure why ServiceNow just makes retired, expired, and old versions all "outdated." More differentiation would be helpful. Maybe this should be in the idea portal if it's not already.

[jaimehonaker]

The best I've been able to get is similar to what Lauren has provided above. 🙂