How to combine multiple values of a field into a single value?

[vinoth_raj]

Hi folks,
[Current scenario]
When a role is created with capabilities, I am receiving one event for the role creation and each added capability is generated as an event.
For example, one role with five capabilities will produce six events in total with similar 'ID'.

Event for role created:
2023-04-20T16:08:05,290 INFO [ID] 1234567:user - Added IdentityType=Role Name=<Role Name>, ObjId=<Object Id>.

Events for capability added:
2023-04-20T16:12:07,020 INFO [ID] 1234567:user - Access Control change on ObjectType=<Object type>, Name=<Capability>, ObjId=<Object Id>.
2023-04-20T16:12:07,020 INFO [ID] 1234567:user - Access Control change on ObjectType=<Object type>, Name=<Capability>, ObjId=<Object Id>.
2023-04-20T16:12:07,020 INFO [ID] 1234567:user - Access Control change on ObjectType=<Object type>, Name=<Capability>, ObjId=<Object Id>.
2023-04-20T16:12:07,021 INFO [ID] 1234567:user - Access Control change on ObjectType=<Object type>, Name=<Capability>, ObjId=<Object Id>
2023-04-20T16:12:07,021 INFO [ID] 1234567:user - Access Control change on ObjectType=<Object type>, Name=<Capability>, ObjId=<Object Id>.

My SPL:
index=test
|eval Info=case(Type="Role" AND Action="Added",'User'." "."has created the role named ".'Name'." with the following capabilities: ".'Capabilities')

In the above I need the values of the five capability in the field(Capabilities).

[Requirement]
Any idea on how to include all the capability based on ID into a field called 'Capabilities'?

Note:I dont want to use 'stats values()' directly in my main search.

[PickleRick]

And why don't you want to use stats values()? That's the most straightforward thing to do here. Of course you can do eventstats and then dedup but it's kinda silly. You could try to do transaction but this command is often best avoided if you can do the same another way.

[vinoth_raj · Accepted solution]

Thanks for the reply.I don't want to use stats in main search only because the above is a part of my SPL and using stats directly can limit my data required.

I was able to resolve using something like the below in the join command.
| join type=outer typeID
[ search index=testing ("Access Control change on ObjectType" OR "Added IdentityType=Role")
| rex field=_raw "^(:?\d{4}-\d{2}-\d{2}\w\d+\S\d+\S\d+,\d+)\s\w+\s+\[(?<ID>[0-9]+)]\s\d+:[A-Za-z@]+\s-\s[A-Za-z0-9\s]+=[A-Za-z0-9\s]+,\sName=(?<capability>[A-Z\sa-z0-9]+)"
| eventstats values("capability") as test by ID
| eval Capabilities=mvjoin(test,";")
| fields Capabilities]

[PickleRick]

OK. You lost me on that subsearch. (And I'm not gonna get started about the usage of join; just be warned that that's usually not the best idea).

Your subsearch just outputs a list (with duplicates) of capabilities sets. And since it's a subsearch, it's limited to this execution scope. So how is it better than just stats values?