topic Re: How to use the regex matched variables from the first search into the other search to get all matching results in Splunk Search

How to use the regex matched variables from the first search into the other search to get all matching results

sarathi125 — Tue, 31 Dec 2024 02:00:52 GMT

Hi All,

I am searching UiPath Orchestrator Logs in Splunk as following:

index="<indexname>" source = "user1" OR source = "user2" "<ProcessName>" "Exception occurred" | rex field=message "(?<dynamic_text>jobId:\s*\w+)" | search dynamic_text!=null | stats values(dynamic_text) AS extracted_texts | map search="index="<indexname>" source = "user1" OR source = "user2" dynamic_text=\"$extracted_texts$\""

with my above search, I'll have to reference the jobId matched field from the first search to get other matching records to process transaction details

Thanks a lot in advance!

Re: How to use the regex matched variables from the first search into the other search to get all matching results

bowesmana — Tue, 31 Dec 2024 04:58:23 GMT

Map is generally NOT a solution to searches. This is a potential use of a subsearch, i.e.

index="<indexname>" source = "user1" OR source = "user2" [ search index="<indexname>" source = "user1" OR source = "user2" "<ProcessName>" "Exception occurred" | rex field=message "(?<dynamic_text>jobId:\s*\w+)" | search dynamic_text!=null | stats values(dynamic_text) AS dynamic_text ]

So here you are using a subsearch to get all the dynamic_text values you want and then that is passed as a constraint to the outer search.

Re: How to use the regex matched variables from the first search into the other search to get all matching results

sarathi125 — Tue, 31 Dec 2024 14:11:36 GMT

@bowesmana,

I have tried with that, but not getting any results. Actually I am trying to match the jobid from the below message. And using this jobId I have get other records which are all matching with this jobid

Re: How to use the regex matched variables from the first search into the other search to get all matching results

isoutamo — Tue, 31 Dec 2024 14:19:13 GMT

Have you try a spath command as you have json data in use?

Re: How to use the regex matched variables from the first search into the other search to get all matching results

sarathi125 — Tue, 31 Dec 2024 20:02:38 GMT

@isoutamo,

No, I have not tried with spath, Could you please guide me with that.

I tried with the below, its showing events, but not getting the transaction level information

index="<indexname>" source = "user1" OR source = "user2" "<ProcessName>" "Exception occurred" | spath | table _time JobId TransactionId _raw | search JobId=* | append [ search index="<indexname>" source = "user1" OR source = "user2" | spath | search JobId=* | table _time JobId TransactionId _raw ] | stats dc(TransactionId) as UniqueTransactionCount values(TransactionId) as UniqueTransactions by JobId

Re: How to use the regex matched variables from the first search into the other search to get all matching results

yuanliu — Wed, 01 Jan 2025 07:59:48 GMT

As @bowesmana says, map is generally not suitable for what you are trying to do. Instead of illustrating an imagined SPL snippet for volunteers to read your mind, it is better to ask yourself, and illustrate:

What is a meaningful dataset to illustrate my problem? Action: Illustrate said dataset using text. (Screenshot does not apply. Anonymize as needed.)
What is the information I am trying to obtain? Action: Illustrate your desired output based on the dataset.
What is the logic between my sample dataset and desired output? Use plain language, not SPL. Make your intention clear in logical terms. Use common mathematical/logical symbols if you like, but not SPL if you have any doubt about your code.
If you illustrate some SPL that does not give you desired output, also illustrate actual results from the sample dataset. Then, explain why the result differs from desired output unless the reason is painfully obvious.

Before I try to read your mind, let me point out one critical point you need to clarify - I will use your "first search" to exemplify. Do you try to search for events with terms "<ProcessName>" and "Exception occurred" only in source=user2, then all events from source=user1? Because that's what your first search does. Your second search has the same logic, therefore IF that map command works, events in source=user1 will always match. Is this really your intention?

I have a high suspicion that you want to search for events with terms "<ProcessName>" and "Exception occurred" in either source=user1 or source=user2. Is this correct? I will assume so in the following.

This being said, based on the screenshot snippet you shared, you don't need to use regex or even spath to extract jobId because Splunk has clearly done that for you. The field name is Properties.jobId. All you need to do is to match this field.

In other words, given these 8 simplified events:

	source	_raw
1	user1	{"Level": "Error", "MessageTemplate": "Exception occurred - something something", "Properties": { "jobId": "8ef3e2f8-35c4-4f0a-8553-cffd718640b", "message": "<ProcessNotName2> Exception occurred - Exception Source: System.Activities stuff, stuff" } }
2	user1	{"Level": "Error", "MessageTemplate": "Exception occurred - something more", "Properties": { "jobId": "8ef3e2f8-2903-4f0a-8553-cffd718640b", "message": "<ProcessName> Exception occurred - Exception Source: System.Activities stuff, stuff" } }
3	user1	{"Level": "Info", "MessageTemplate": "Exception did not occurr - something else", "Properties": { "jobId": "8ef3e2f8-1234-4f0a-8572-cffd718640b", "message": "Exception won't happen - blah" } }
4	user1	{"Level": "Info", "MessageTemplate": "Not exception - something else", "Properties": { "jobId": "8ef3e2f8-5678-4f0a-8553-cffd718640b", "message": "Nothing to see here - don't worry" } }
5	user2	{"Level": "Error", "MessageTemplate": "Exception occurred - something more", "Properties": { "jobId": "8ef3e2f8-35c4-4f0a-8553-cffd718640b", "message": "Exception occurred - Exception Source: System.Activities stuff, stuff" } }
6	user2	{"Level": "Error", "MessageTemplate": "Exception occurred - something something", "Properties": { "jobId": "8ef3e2f8-2903-4f0a-8553-cffd718640b", "message": "Exception occurred - Exception Source: System.Activities stuff, stuff" } }
7	user2	{"Level": "Info", "MessageTemplate": "Exception did not occurr - something else", "Properties": { "jobId": "8ef3e2f8-2903-4f0a-8572-cffd718640b", "message": "Exception won't happen - blah" } }
8	user2	{"Level": "Info", "MessageTemplate": "Not exception - something else", "Properties": { "jobId": "8ef3e2f8-2903-4f0a-8553-cffd718640b", "message": "Nothing to see here - don't worry" } }

you want to select 2, 6, and 8.

This is the search to use:

index="<indexname>" (source = "user1" OR source = "user2") [ search index="<indexname>" (source = "user1" OR source = "user2" ) "<ProcessName>" "Exception occurred" | stats values(Properties.jobId) AS Properties.jobId ]

This is the data emulation to generate the mock dataset posted above. Play with it and compare with real data

Using this emulation in both main search and subsearch, here is a full emulation:

| makeresults | eval data = mvappend( "{\"Level\": \"Error\", \"MessageTemplate\": \"Exception occurred - something something\", \"Properties\": { \"jobId\": \"8ef3e2f8-35c4-4f0a-8553-cffd718640b\", \"message\": \"<ProcessNotName2> Exception occurred - Exception Source: System.Activities stuff, stuff\" } }", "{\"Level\": \"Error\", \"MessageTemplate\": \"Exception occurred - something more\", \"Properties\": { \"jobId\": \"8ef3e2f8-2903-4f0a-8553-cffd718640b\", \"message\": \"<ProcessName> Exception occurred - Exception Source: System.Activities stuff, stuff\" } }", "{\"Level\": \"Info\", \"MessageTemplate\": \"Exception did not occurr - something else\", \"Properties\": { \"jobId\": \"8ef3e2f8-1234-4f0a-8572-cffd718640b\", \"message\": \"Exception won't happen - blah\" } }", "{\"Level\": \"Info\", \"MessageTemplate\": \"Not exception - something else\", \"Properties\": { \"jobId\": \"8ef3e2f8-5678-4f0a-8553-cffd718640b\", \"message\": \"Nothing to see here - don't worry\" } }" ) | mvexpand data | rename data AS _raw | spath | eval source = "user1" | append [| makeresults | eval data = mvappend( "{\"Level\": \"Error\", \"MessageTemplate\": \"Exception occurred - something more\", \"Properties\": { \"jobId\": \"8ef3e2f8-35c4-4f0a-8553-cffd718640b\", \"message\": \"Exception occurred - Exception Source: System.Activities stuff, stuff\" } }", "{\"Level\": \"Error\", \"MessageTemplate\": \"Exception occurred - something something\", \"Properties\": { \"jobId\": \"8ef3e2f8-2903-4f0a-8553-cffd718640b\", \"message\": \"Exception occurred - Exception Source: System.Activities stuff, stuff\" } }", "{\"Level\": \"Info\", \"MessageTemplate\": \"Exception did not occurr - something else\", \"Properties\": { \"jobId\": \"8ef3e2f8-2903-4f0a-8572-cffd718640b\", \"message\": \"Exception won't happen - blah\" } }", "{\"Level\": \"Info\", \"MessageTemplate\": \"Not exception - something else\", \"Properties\": { \"jobId\": \"8ef3e2f8-2903-4f0a-8553-cffd718640b\", \"message\": \"Nothing to see here - don't worry\" } }" ) | mvexpand data | rename data AS _raw | spath | eval source = "user2"] ``` the above emulates index="<indexname>" (source = "user1" OR source = "user2") ``` | search [makeresults | eval data = mvappend( "{\"Level\": \"Error\", \"MessageTemplate\": \"Exception occurred - something something\", \"Properties\": { \"jobId\": \"8ef3e2f8-35c4-4f0a-8553-cffd718640b\", \"message\": \"<ProcessNotName2> Exception occurred - Exception Source: System.Activities stuff, stuff\" } }", "{\"Level\": \"Error\", \"MessageTemplate\": \"Exception occurred - something more\", \"Properties\": { \"jobId\": \"8ef3e2f8-2903-4f0a-8553-cffd718640b\", \"message\": \"<ProcessName> Exception occurred - Exception Source: System.Activities stuff, stuff\" } }", "{\"Level\": \"Info\", \"MessageTemplate\": \"Exception did not occurr - something else\", \"Properties\": { \"jobId\": \"8ef3e2f8-1234-4f0a-8572-cffd718640b\", \"message\": \"Exception won't happen - blah\" } }", "{\"Level\": \"Info\", \"MessageTemplate\": \"Not exception - something else\", \"Properties\": { \"jobId\": \"8ef3e2f8-5678-4f0a-8553-cffd718640b\", \"message\": \"Nothing to see here - don't worry\" } }" ) | mvexpand data | rename data AS _raw | spath | eval index = "<indexname>", source = "user1" | append [| makeresults | eval data = mvappend( "{\"Level\": \"Error\", \"MessageTemplate\": \"Exception occurred - something more\", \"Properties\": { \"jobId\": \"8ef3e2f8-35c4-4f0a-8553-cffd718640b\", \"message\": \"Exception occurred - Exception Source: System.Activities stuff, stuff\" } }", "{\"Level\": \"Error\", \"MessageTemplate\": \"Exception occurred - something something\", \"Properties\": { \"jobId\": \"8ef3e2f8-2903-4f0a-8553-cffd718640b\", \"message\": \"Exception occurred - Exception Source: System.Activities stuff, stuff\" } }", "{\"Level\": \"Info\", \"MessageTemplate\": \"Exception did not occurr - something else\", \"Properties\": { \"jobId\": \"8ef3e2f8-2903-4f0a-8572-cffd718640b\", \"message\": \"Exception won't happen - blah\" } }", "{\"Level\": \"Info\", \"MessageTemplate\": \"Not exception - something else\", \"Properties\": { \"jobId\": \"8ef3e2f8-2903-4f0a-8553-cffd718640b\", \"message\": \"Nothing to see here - don't worry\" } }" ) | mvexpand data | rename data AS _raw | spath | eval source = "user2"] | search "<ProcessName>" "Exception occurred" ``` the above emulates index="<indexname>" (source = "user1" OR source = "user2") "ProcessName" "Exception occurred" ``` | stats values(Properties.jobId) as Properties.jobId ]

The output is these three events:

source	_raw
user1	{"Level": "Error", "MessageTemplate": "Exception occurred - something more", "Properties": { "jobId": "8ef3e2f8-2903-4f0a-8553-cffd718640b", "message": "<ProcessName> Exception occurred - Exception Source: System.Activities stuff, stuff" } }
user2	{"Level": "Error", "MessageTemplate": "Exception occurred - something something", "Properties": { "jobId": "8ef3e2f8-2903-4f0a-8553-cffd718640b", "message": "Exception occurred - Exception Source: System.Activities stuff, stuff" } }
user2	{"Level": "Info", "MessageTemplate": "Not exception - something else", "Properties": { "jobId": "8ef3e2f8-2903-4f0a-8553-cffd718640b", "message": "Nothing to see here - don't worry" } }

Re: How to use the regex matched variables from the first search into the other search to get all matching results

bowesmana — Thu, 02 Jan 2025 00:50:23 GMT

If you run the search that gives you that output in Verbose mode, you will see the fields that are automatically extracted.

If jobId is a field that is automatically extracted, then you should write a basic search that looks for all the jobIds you want - you tried to do that with your rex statement, but you actually included the text "jobId:..." in the dynamic_text, you actually want the jobId data without "jobId:".

As @isoutamo says, if jobId is NOT auto-extracted, then use spath to get it and then do the stats on the jobId, e.g. this is the SUBSEARCH - which if you run it on its own will return a single field called jobId with all the jobIds you want.

index="<indexname>" source = "user1" OR source = "user2" where "<ProcessName>" "Exception occurred" | spath Properties.jobId ``` This uses spath to extract the jobId ``` | search Properties.jobId!=null | stats values(jobId) AS jobId ]

Then use this as the subsearch to the outer search and it will then find all records that have a jobId matching the ones you are selecting.

Note that if your jobId is NOT auto extracted, then you cannot make a search for jobId=X, so you will need to either configure Splunk to auto extract the JSON or create a calculated field with this type of expression

| eval jobId=spath(_raw, "Properties.jobId")

which will mean jobId will always be a field in your data for search, so you won't have to use the spath expression in your search

Re: How to use the regex matched variables from the first search into the other search to get all matching results

bowesmana — Thu, 02 Jan 2025 00:51:56 GMT

In your screenshot, the field jobId had a lower case J, whereas you're using JobId - field names are case sensitive. Also when you use simple spath to extract all fields, they will have the JSON hierarchy in their field names, i.e. the jobId is the field Properties.jobId, not jobId

Also, this is all achievable without using append, so try the subsearch to do the constraints for the outer

Re: How to use the regex matched variables from the first search into the other search to get all matching results

sarathi125 — Thu, 02 Jan 2025 01:54:16 GMT

Hi @bowesmana,

With the below query able to achieve what I have tried to get, Thank you for your input.

index="<index>" (source="user1" OR source="user2") "The transaction reference id is" | rex field=_raw "\"jobId\":\s?\"(?<jobId>[a-fA-F0-9\-]+)\"" | join jobId [ search index="<index>" (source="user1" OR source="user2") ("<ProcessName>" AND "Exception occurred") | rex field=_raw "\"jobId\":\s?\"(?<jobId>[a-fA-F0-9\-]+)\"" | table jobId, _time, _raw ] | table _time, jobId, _raw

Re: How to use the regex matched variables from the first search into the other search to get all matching results

bowesmana — Thu, 02 Jan 2025 02:50:32 GMT

@sarathi125 FYI: Although you have a solution, using join is not a Splunk way of doing things, joining data sets should really be done using stats, it's faster, more efficient and does not have the limitations of join, which will silently discard results if the join subsearch exceeds 50,000 results - this may not be an issue in your case, but it's good practice to get your head around using stats to achieve joins.

I also recommend you sort out the automatic field extraction so that you don't have to manually extract jobId - which then means you can use the fields in subsearches and only then have to make a single search.