Combining two searches into one search

[Zerophage]

Hi all,

I'm having an issue combining two searches into one search.

I have a sourcetype that logs information about an event over several rows with a unique ID linking them all together. So what I do is I have a search that finds all unique ID that match my criteria. I then manually copy paste those into another seach that uses transaction to group all the events with the same ID.

Example first search could be:

sourcetype=email subject="Invoice Number" 
| stats count by ID
| fields ID
| mvcombine delim "," ID
| nomv ID

The result is a list of numbers like 111,222,333,444. So here I know there are 4 emails with that subject but in order to view the whole event I need to do a transaction.

The next search I do (in another tab) is the following:

sourcetype=email ID IN (111,222,333,444)
| transaction ID maxpan 5m
| stats count by subject,sender,recipient

Is there a way I can combine both of these searches into one search?

Thanks for any help that can be offered!

[micahkemp]

Mail logs are awful, and I'm sorry you are going to be stuck dealing with them. Seriously.

While this example may be easily handled by the answer below, you'll find that the subsearch method for mail logs quickly becomes unscalable.

My recommendation is to look into summarization for your mail logs. Something like:

| stats earliest(_time) AS _time, values(subject) AS subject, values(sender) AS sender, values(recipient) AS recipient, values(attachment) AS attachment BY ID

that gets written to a summary index, such that later you can search your summary index like:

index=mail_summary subject="Invoice Number" | stats count BY subject sender recipient

[cmerriman · Accepted solution]

you could try something like:

sourcetype=email [sourcetype=email subject="Invoice Number"|stats count by ID|fields ID|format] 
| transaction ID maxpan 5m
| stats count by subject,sender,recipient

the subsearch with format with put the ID in a ((ID=111) OR (ID=222) OR....) format

[Zerophage]

Hi thanks! It looks like it almost works, hadnt thought of using format... It however returns results that slightly differ than if I just copy pasted.

If I replace the above with a "format" version, I get the same results as the IN. If i use the subsearch the results differ.. I think its something to do with subsearches?

[cmerriman]

how many results is the subsearch producing? how long is the subsearch taking? there are limits on subsearches for time and rows, and you could be hitting a limit. if you check the job inspector when you use subsearch, do you get any errors/messages?

[Zerophage]

Ah yes there is a time limit it says (60 seconds) and the search does take sometime to run.. In my current search i get 43 results with the manual method and 35 with the subsearch.. Is there a way to increase the time limit as a power user?

[cmerriman]

to change the limits, you need to have it done in limits.conf
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Search/Aboutsubsearches#Subsearch_performance...

you might be able to try tstats to speed up the search.
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/Tstats
maybe something like:

sourcetype=email [|tstats count WHERE sourcetype=email AND "Invoice Number" BY ID |fields ID|format] 
 | transaction ID maxpan 5m
 | stats count by subject,sender,recipient

however, tstats only works on fields that are on the indexed fields, so if ID is a field parsed after index time, then that might not work. Try to play with it..

[Zerophage]

Cool, thanks a lot for your help! It is most appreciated. I'll figure it out from here.