How to Obtain the Number of Computers Assigned Multiple Times to Users (Platform Analytics)

[segarenc]

I’m working on a dashboard where I need to create a Single Score widget that shows the percentage of laptops that have been assigned multiple times.

The formula is:

% of Laptops Assigned Multiple Times =
(Number of laptops where Assigned To has changed at least once) ÷ (Total number of laptops) × 100

To achieve this, I think ( not sure) that I need two automatic indicators:

1. Amount of computers that have been assigned multiple times (this is where I’m stuck).

2. Total number of laptops (this one is straightforward).

My idea is to then create a formula indicator that calculates the percentage.

My Questions:

1. How can I create the automatic indicator for computers assigned multiple times?

   - From my research, it looks like I may need to create a scripted automatic indicator, but I’m not sure how to           add the script into the automatic indicator definition. I already wrote some script logic (screenshot     attached), but I don’t know how to plug it in.

2. Is there another easier solution for this use case instead of a scripted indicator?

Any guidance or examples would be really helpful.

Thanks in advance!

[segarenc]

Hi @Bhuvan ,
Thanks a lot for your suggestion. I also thought about creating a counter field (similar to the incident reassignment counter), but unfortunately this approach won’t work  — my colleagues would not accept adding new fields for this purpose.

Since the Audit History table capturing all changes on the Computer  tables.  For example, whenever a laptop is reassigned to a user, the record is logged in the Audit History.

I was able to create two scripts (attached) that return all computers which have been assigned to more than one user. So the logic is working, but the issue I’m facing is that I don’t know how to incorporate this script into an Automatic Indicator.

That’s where I’m currently stuck.

[Bhuvan]

@segarenc 

I would strongly recommend not to use any script that would use system tables especially audit and log tables as it would lead to performance problems, even if you use optimized script

If I am in your shoes, I would explain the benefits of keeping the logic simple by adding a new field to the team. I do not see any issue with upgradeability & manageability by adding a new field and impact would be much less than using indicator to use audit table. If this is not feasible, try to use metric definition to capture any changes to assigned to field and use it as data source

As you understand, indicators are configured to use indicator sources for scores collection. Create a new metric definition to track changes to assigned_to field of Computers table. Create a new automated indicator to track changes to assigned_to field and configure data collection job to collect daily scores that should give number of computers whose assigned_to has changed

https://www.servicenow.com/docs/bundle/zurich-platform-administration/page/use/reporting/task/create...

Use formula indicator to calculate the percentile value based on the automated indicators. Recommendation is to use earlier approach by adding a new field as it would keep the logic simple and easy to maintain

I hope you appreciate the efforts to provide you with detailed information. If my responses helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[Bhuvan]

@segarenc 

Did you get a chance to review this ?

I hope you appreciate the efforts to provide you with detailed information. If my response helped to guide you or answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan

[segarenc]

Hi @Bhuvan  ,

I tried using the Metrics instance, where I created a metric business rule and a metric definition to capture all Assigned to changes. It is working perfectly.

However, I’m facing two issues:

1. Since I’ve only just set this up, only new changes are now being stored in the metric table. What about the old data, where laptops had their Assigned to field changed several times in the past? How should I handle that?

2. I also need to construct an automatic indicator (with a script), where the indicator source is the metric. Could you please help me with the script that should be included in the automatic indicator?

Thanks in advance!

[Bhuvan]

@segarenc 

1. Historical data collection job works only when there is underlying past data. Since this is new configuration, it will not be possible to get historical data. If you are maintaining the data manually in some place to track assigned to details, we can look at option of creating the scores manually or via automation [similar to Manual Indicators] but I doubt you would have this data

2. Please share details of your code or configuration, where you are stuck or what is not working so that I can check

As per community guidelines, you can accept more than one answer as accepted solution. If my responses helped to answer your query, please mark it helpful & accept the solution.

Thanks,

Bhuvan