How to store 100k ids in a field in a table?

[Baishnabi]

I have a scenario where there is a query with certain names of instances stored in a string field. Now i have to extract the corresponding ids for these instance names in the query from cmdb table and store it to process it at a later point. There could be upto 100k names in that instance list. Whats the most ideal way to handle this? Should i extract the ids and store it in a separate string field in the same table? Or create a new table to map a set of ids? Please advise.

example query : u_nameINinsxyz,insxyz2,insxyz3... (upto 100k)

expected instance ids list : 

"f918b7341be7fcd07504751f034bcbbb,f918b7341be7fcd07504751f034bcbbb..." (upto 100k)

Am skeptical if its good practice to store such long strings.

[GlideFather]

Hi @Baishnabi 

The maximum field length might be 4000 characters, assuming one sys ID is 32 characters long then it will not be enough at all...

But what about to store the sys IDs in a knowledge article and then to retrieve it from there?

Not tested myself, also verify the max lenght of KB Articles...

EDIT: and what is the justification for storing so many sys IDs this way??

[Baishnabi]

Hi - when i created the field in my vsf, its max length got assigned as 45k. Attached screenshot for reference. Is 4k the default limit?

About knowledge article - I can explore and try this. Thanks.

what is the justification for storing so many sys IDs this way??  --

A little context - I am from instance analyzer team and we came across an issue with audit request execution. When a user creates an audit request today and sets start date of execution to a few days later and chooses all production instances to execute it on, lets say.

In the backend all these prod instances selection are getting saved in form of a query of instance names. (like this - u_nameINinsxyz,insxyz2,insxyz3... ). At the time of execution we fetch them using this query. We came across situations when an instance gets "renamed" to something else between the period of audit creation and execution. Now the new name is not a part of the query and nothing is found with the old name, hence the audit request gets skipped from executing on that particular instance. 

To solve this - our idea is to store the corresponding ids (which would essentially remain the same always) instead of names. And that way we wouldnt skip any instance, hence the ask.

Hope that makes sense 🙂

[TamoghnaB]

Hi @Baishnabi ,

I would say to create a custom table and add rows for each Id. This way it will not cause any performance issue . Also, You can retrieve / query from that table into any script very easily and storing records will also be very clean and easy.

Please mark reply as Helpful/Correct, if applicable. Thanks!

Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

[Baishnabi]

Thanks Tamoghna, this sounds doable. Will give it a try.