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]
V

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
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...