How to create a report based on assignment group and ka author

raj149 · ‎06-21-2023

Hello Experts,

My requirement is

There is group called ABC group. In this group I want to check how many members are not authors for knowledge articles. We can check authors in this sn_km_mr_st_kb_knowledge table.

we can find that users manually but here I want to create a report based on above requirement.

How to achieve this...?

Thanks in advance.

Best Regards,

Raj

Amit Gujarathi · ‎06-21-2023

@raj149 ,
You can try below script

var grMembers = new GlideRecord('sys_user_grmember');
grMembers.addQuery('group', abcGroupId);
grMembers.query();

var nonAuthorsCount = 0;

while (grMembers.next()) {
  var userId = grMembers.user.toString();
  
  var grKnowledge = new GlideRecord('sn_km_mr_st_kb_knowledge');
  grKnowledge.addQuery('author', userId);
  grKnowledge.query();

  if (!grKnowledge.next()) {
    nonAuthorsCount++;
  }
}

gs.print('Number of members in ABC group who are not authors: ' + nonAuthorsCount);

Was this answer helpful?

Please consider marking it correct or helpful.

Your feedback helps us improve!

Thank you!

Regards,

Amit Gujrathi

View solution in original post

raj149 · ‎06-21-2023

Hello @subhadipkarmaka

1.Which table I need select in report...?

2. ABC group is from sys_user_group table

3.Author names present in sn_km_mr_st_kb_knowledge table

My question is how to check ABC group members are knowledge article authors or not...?

Best regards,

Raj.

Amit Gujarathi · ‎06-21-2023

HI @raj149 ,
I trust you are doing great.

To achieve the requirement of finding the members in the "ABC" group who are not authors for knowledge articles in the "sn_km_mr_st_kb_knowledge" table, you can follow these steps:

Create a new report in ServiceNow by navigating to the "Reports" module and selecting "Create New Report."
Select the "sn_km_mr_st_kb_knowledge" table as the data source for the report.
Define the required filters for the report:
- Filter 1: Set the filter condition to "Author is empty" to identify knowledge articles without authors.
- Filter 2: Add a condition to filter by the "ABC" group.
Specify the columns you want to include in the report. For example, you may want to include the "Number" and "Short Description" columns to provide information about the knowledge articles.
Save the report and name it appropriately, such as "Members of ABC Group without Knowledge Article Authorship."
Run the report to generate the desired output, which will show the members of the "ABC" group who are not authors for any knowledge articles.

Was this answer helpful?

Please consider marking it correct or helpful.

Your feedback helps us improve!

Thank you!

Regards,

Amit Gujrathi

raj149 · ‎06-21-2023

Hello @Amit Gujarathi

1.I want to display user names in report not knowledge article numbers

2.ABC group is not knowledge article assignment group.

I think some script is required to check ABC group members are authors or not...

Best regards

Raj

Amit Gujarathi · ‎06-21-2023

You can extract the user ids of the people by doing gliderecord on the table and defining the function for the same in script include .
then you can call that script include from reports.
I have done the same kind of thing but diffrent use case please find the video for the same.

Was this answer helpful?

Please consider marking it correct or helpful.

Your feedback helps us improve!

Thank you!

Regards,

Amit Gujrathi