Splunk Search

How to combine my two search queries using join or subsearch?

allladin101
Explorer

Hi,

I have 2 queries which do not have anything in common, how ever i wish to join them can somebody help :

query 1 :

index=whatever* tms_action="*someaction*"| rex "'(?[^']+)\s*' .*"| table userhandle tms_blob_type
| join userhandle [search index=summary source=*Username* 
| table userhandle user company]

query 2:

index=whatever* sourcetype=server "_DATE_FROM_IN =*" "*_DATE_TO_IN =*" |REGEX _DATE_TO_IN ="'\d+'" | rex field= "_DATE_FROM_IN (?<_DATE_FROM_IN>.*)"|rex field= "_DATE_TO_IN (?<_DATE_TO_IN>.*)"| table _DATE_TO_IN _DATE_FROM_IN

The only way to manually join them is as shown below over the userhandle field:

index=whatever2 _threadid=12128 host=whatever18 Starting handle blob earliest=1415036143.447343 latest=1415036623.447343 | eval tdiff=_time-strptime("2014november3 17:39:43.447343","%Y%B%e %H:%M:%S.%6N")  | search tdiff<0  | sort -tdiff  | head 1  | rex field=_raw " for '(?[^']*)'"  | rename _blob_type  AS ThreadAction  | table _time userhandle ThreadAction

is there a way to do it by a join or subquery or something ?

Plesae help.

tachifelix
Path Finder

try to use this subsearch in your search string

index=whatever* tms_action="*someaction*"| rex "'(?P<FIELDNAME>[^']+)\s*' .*"| join userhandle [search index=summary source=*Username*]|join [search  index=whatever* sourcetype=server "_DATE_FROM_IN =*" "*_DATE_TO_IN =*" |rex   "(?P<DATE_TO_IN>)'\d+'" | rex field=DATE_TO_IN  "_DATE_FROM_IN (?<_DATE_FROM_IN>.*)"|table  userhandle user company _DATE_TO_IN _DATE_FROM_IN
0 Karma

MuS
SplunkTrust
SplunkTrust

because this is not the answer to your question I will post it as comment:

Maybe you will have a look at this answer http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi... to get an idea how to do it without the use of join or any subsearch

0 Karma

vasanthmss
Motivator

Try this,

index=whatever* (sourcetype=server "_DATE_FROM_IN =*" "*_DATE_TO_IN =*") OR (tms_action="*someaction*")  | rex "'(?[^']+)\s*' .*" |REGEX _DATE_TO_IN ="'\d+'" | rex field= "_DATE_FROM_IN (?<_DATE_FROM_IN>.*)"|rex field= "_DATE_TO_IN (?<_DATE_TO_IN>.*)"  | table userhandle tms_blob_type _DATE_TO_IN _DATE_FROM_IN  | join userhandle [search index=summary source=*Username* | table userhandle user company]

davebrooking
Contributor

Is there something missing from the rex command you posted in query1 in the original question?

I think the supplied answer is trying to use the same rex command as shown in query 1 from the original question.

0 Karma

allladin101
Explorer

It says that - Error in 'rex' command: Encountered the following error while compiling the regex ''(?[^']+)\s*' .*': Regex: unrecognized character after (? or (?-

also the query is not returning a table .. please help

even this part is not working :

index=tms_uat* (sourcetype=tms_server "DEAL_DATE_FROM_IN =" "*DEAL_DATE_TO_IN =") OR (tms_action="DS::QueryDealMessage3") |rex "'(?['^']+)\s*' ."|REGEX DEAL_DATE_TO_IN ="'\d+'" | rex field= "DEAL_DATE_FROM_IN (?.)"|rex field= "DEAL_DATE_TO_IN (?.*)" | table ULH tms_blob_type DEAL_DATE_TO_IN DEAL_DATE_FROM_IN

0 Karma

lakromani
Builder

You are trying to add it to a dashboard. Instead of "<" and ">" user "<" and ">"

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.