Subsearch / query with inputlookup

[Cranie]

Apologies, I am quite new to Splunk so not sure if this is possible, I have the following simple query:

| inputlookup appJobLogs
| where match(MessageText, "(?i)general error")
| rex mode=sed field=MessageText "s/, /\n/g"
| sort RunStartTimeStamp asc, LogTimeStamp asc, LogID ASC

This works and gets the data I need for the error I am after, but, I want all associated values for the error by RunID.

So the headers are:
Host, InvocationID, Name, LogID, LogTS, LogName, MessageID, MessageText, RunID, RunTS, RunName

I would like to do something like:

| inputlookup appJobLogs
| where RunID in [
  | search appJobLogs
  | where match(MessageText, "(?i)general error")
  | fields RunID
]

I have tried various forms and closest I got was a join which gave me the not found fields (should be fixable) but limited to 10,000 results so that seems like the wrong solution.

[ITWhisperer · Accepted solution]

Try something like this

| inputlookup appJobLogs where [
  | search appJobLogs
  | where match(MessageText, "(?i)general error")
  | fields RunID
]

[Cranie]

After a little tweaking this gives the desired results:

| inputlookup appJobLogs
| search [ | inputlookup appJobLogs
| where match(MessageText, "(?i)general error")
| fields RunID
| uniq
| format
]
| rex mode=sed field=MessageText "s/, /\n/g"
| sort RunStartTimeStamp asc, LogTimeStamp asc, LogID ASC

[Cranie]

I got an error 

"The 'NOT ()' filter could not be optimized for search results."

I'll look into. Thanks for the suggestion

[richgalloway]

If you want events grouped by one or more fields then you want the stats command.

| inputlookup appJobLogs
| where match(MessageText, "(?i)general error")
| rex mode=sed field=MessageText "s/, /\n/g"
| stats values(*) as * by RunID

[Cranie]

This is pretty much what I want, but there are other RunID lines that do not have the "general error" message that I want to capture also. So your example groups all RunID's and the MessageText with "general error".

What I need is, all RunID entries for the RunID with MessageText "general error".

I.e:

RunId	MessageText
1	Start
1	There has been a general error.
1	Finish
2	Start

So I find the RunID 1 having the error and I want to output the start, finish and the error too. If that is possible, and in this example, not RunID 2.