How handle case-sensitive results from a subsearch?

[bacchussr]

I have created a dashboard that allows me to search my sendmail logs for some component of a mail transaction (e.g. mail from, rcpt to, subject, etc) and uses transaction to find and group all events related to any hits:

index=sendmail host=mail-gw* [ search index=sendmail host=mail-gw* to="someone@example.org" | fields qid ] | transaction qid

It's fairly simple and works for most cases. However, sendmail qid's are case sensitive and there are a number of times where the qid returned matches another qid, only due to case insensitivity. I understand that search, by default treats field key names as case-sensitive and field values as case-insensitive and that most of the time, that is desired. The problem is that whatever is returned from the subsearch is case-sensitive, but the outer search is not case-sensitive. Any efficient way around this?

I apologize if this is a repost of a common question (trust me I know because I searched for hours), but I could not find any solutions that fit this particular situation.

[bfontneau]

bacchussr,

I got this to work by modifying the output of the subsearch using a combination of renaming the data field to query so only fields values are passed back to the main search and reformatting what is passed back to use the CASE() function using the format command.

I did this by adding the following to the end of your subsearch:

| rename qid AS query| format "(" "CASE(" "AND" ")" "OR" ")"

So the entire search is this:

index=sendmail host=mail-gw* [ search index=sendmail host=mail-gw* to="someone@example.org" | fields qid | rename qid AS query | format "(" "CASE(" "AND" ")" "OR" ")" ] | transaction qid

Behind the scenes, this changes the litsearch from looking something like this:

index=sendmail  ( ( index=sendmail) AND ( qid=v6F1cQ7Q008732 ) ) OR  ( ( index=sendmail) AND ( ( qid=v6F1OVwW030445 ) )  OR  ( ( index=sendmail) AND ( ( qid= v6F1DS7p016069 ) ) OR ...

to looking something like this:

index=sendmail ( CASE( v6F1cQ7Q008732 ) OR CASE( v6F1OVwW030445 ) OR CASE( v6F1DS7p016069 ) OR CASE( v6F18fBE012916 ) OR CASE( v6F0X9nx005820 ) OR CASE( v6EAoQkO026448 ) OR ... 

Which causes the main search to interpret the qid values passed into the CASE() function and transact the qid's preserving case.

You can find more information about this in the splunk formatting exceptions documentation, the search reference page for the format command, and this excellent post.

I hope this helps, I know it has helped us get accurate results from case sensitive subsearches!

[MuS]

Hi bacchussr,

there is a nice fiction of eval where you can use the {} to create new fields based on a value of another field.

 index=_internal sourcetype=splunkd or sourcetype=scheduler
| eval hour-{sourcetype}=date_hour

will create fields that are named hour-splunkd or hour-scheduler with the value of the corresponding date_hour field of splunkd or scheduler.

Or as written in the docs http://docs.splunk.com/Documentation/Splunk/6.4.1/SearchReference/Eval

You can also use the value of another field as the name of the destination field by using curly brackets, { }. For example, if you have an event with the following fields, aName=counter and field aValue=1234, use | eval {aName}=aValue to return counter=1234.

So this means you could try something like this:

 index=sendmail [ search index=sendmail host=mail-gw* to="someone@example.org" | fields qid ] 
| eval my_{qid} = qid
| transaction my_qid

Another hint: try to avoid sub searches for multiple reasons like limits and performance implications. You can get some nice example to replace transaction search with stats in the March 2016 session here http://wiki.splunk.com/Virtual_.conf or some other example to replace sub searches here https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

Hope this helps ...

cheers, MuS

[bfontneau]

This doesn't provide a solution to the question. Your eval command creates fields named "my_$qid$" with the fields value as the qid in question. For example: my_v6EJhPul006166=v6EJhPul006166
This means the field you attempt to transact on, "my_qid" is never created. I'm also not sure how case is retained as this command doesn't use the CASE function and search is case insensitive by default.

I have the exact same desire as bacchussr. I need the search results of a subsearch to be treated as a case sensitive search by the main search. [Edit: I figured this out and posted an answer below.]