topic Re: How to join this search with our existing search? in Splunk Search

How to join this search with our existing search?

splunker9999 — Mon, 11 Jul 2016 21:09:59 GMT

Hi,

Need help on a Splunk subsearch.

Below is our Splunk basic search which gives us few fields if it satisfies the below condition:

index=idx sourcetype=jobs NOT "User has reached the per-user job slot limit of the queue" 
|rex field=_raw "loadSched(?<loadSchedule>[\waA-zZ0-9\s\-\.]+)loadStop"
| rex field=loadSchedule "[\d\.\-\s*]{6}(?<util>[\d\.]+)\s"
|fillnull value=0
|rename host to dns_name
    | join type=left dns_name [|inputlookup sas_servers.csv|eval dns_name=lower(dns_name)] 
| search Environment="IPC2 Loyalty"
    | eval totalCount=if(status!="" OR status!=0, jobId, null()) 
| eval pend= if(status="PEND", jobId, null())
| eventstats dc(totalCount) as totalCount, dc(pend) as pend
| eval  pct=(pend/totalCount)*100  
| eval  pct=round(pct,2) 
| eval  PendingPerc=(pct + "%")
| search status="PEND"
| dedup jobId
| rename pend as Totalpendcount
|where pend>25
| table _time dns_name Environment jobId queue status user Totalpendcount util
| rename _time as "Job Submitted"
| convert ctime("Job Submitted")

Now, we have another search below: where we have extracted field name UT

index=idx1  sourcetype=load host="*" ut=*|rename host as dns_name

We need to join this search to the above search such that our table should get values of UT (we need to join this search with host (dns_name) and _time field):

Can some one please help us in getting results for the UT field?

Re: How to join this search with our existing search?

somesoni2 — Mon, 11 Jul 2016 21:21:36 GMT

How about this

index=idx sourcetype=jobs NOT "User has reached the per-user job slot limit of the queue" 
 |rex field=_raw "loadSched(?<loadSchedule>[\waA-zZ0-9\s\-\.]+)loadStop"
 | rex field=loadSchedule "[\d\.\-\s*]{6}(?<util>[\d\.]+)\s"
 |fillnull value=0
 |rename host to dns_name
     | join type=left dns_name [|inputlookup sas_servers.csv|eval dns_name=lower(dns_name)] 
 | search Environment="IPC2 Loyalty"
     | eval totalCount=if(status!="" OR status!=0, jobId, null()) 
 | eval pend= if(status="PEND", jobId, null())
 | eventstats dc(totalCount) as totalCount, dc(pend) as pend
 | eval  pct=(pend/totalCount)*100  
 | eval  pct=round(pct,2) 
 | eval  PendingPerc=(pct + "%")
 | search status="PEND"
 | dedup jobId
 | rename pend as Totalpendcount
 |where pend>25
 | table _time dns_name Environment jobId queue status user Totalpendcount util 
| join type=left dns_name [search  index=idx1  sourcetype=load host="*" ut=*|stats count by host ut | table host ut| rename host as dns_name]
 | rename _time as "Job Submitted"
 | convert ctime("Job Submitted")

Re: How to join this search with our existing search?

Stevelim — Tue, 12 Jul 2016 11:22:46 GMT

base search | appendcols [ search index=idx1  sourcetype=load host="*" ut=*|rename host as dns_name | table dns_name, ut ]

Re: How to join this search with our existing search?

splunker9999 — Tue, 12 Jul 2016 17:26:49 GMT

Thanks Somesh, now UT field is appended to my table, but I could'nt see any values for UT field.

Re: How to join this search with our existing search?

somesoni2 — Tue, 12 Jul 2016 17:52:20 GMT

Can you confirm if the subsearch is returning result and is matching with main search?

index=idx1  sourcetype=load host="*" ut=*|stats count by host ut | table host ut| rename host as dns_name