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

[sarathi125]

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!

[yuanliu]

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:

1. What is a meaningful dataset to illustrate my problem? Action: Illustrate said dataset using text. (Screenshot does not apply.  Anonymize as needed.)
2. What is the information I am trying to obtain?  Action: Illustrate your desired output based on the dataset.
3. 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.
4. 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

| 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")
```

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" } }

[bowesmana]

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.

[sarathi125]

@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

[bowesmana]

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

[isoutamo]

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

[sarathi125]

@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

[bowesmana]

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

[sarathi125 · Accepted solution]

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

[bowesmana]

@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.