How to extract last 5 characters from column entries and add them into a new column

I’m new to Grafana and I’m still learning. Unfortunately I’m having trouble with something.

Here is the issue:
I’m using ElasticSearch in Grafana, where we keep the license consumption data of an application we use. I have several columns in the data table. Among them hostId is a unique data. However, it contains an ID number, the last 5 characters of which we call KID. Some hostIds may have the same last 5 characters. My goal is to see if there is more than one of the same KID in exactly the same timeStamp. If there is, I want to set an alarm and get a warning accordingly. One of my data columns is “used” and normally we expect it to be always 1. Because every hostId must have one license.

I thought of two solutions for this:

  1. make GroupBy - Term - timeStamp according to timeStamp and sum or count the number of hostIds listed that have the same last 5 digits

  2. create a new column called KID by removing the last 5 characters of the hostId and repeat the above process to trigger the alarm if the same KIDs are present.

However, I could not achieve this somehow. I focused on the data transformation tab, but I could not produce a solution. Please help me with the subject and offer me solutions. If there are any points that are not understood, I will be happy to answer.