topic Re: How to use append and join in same search in Splunk Search

How to use append and join in same search

umsundar2015 — Tue, 29 Sep 2020 16:30:15 GMT

Hi ,

I need to use both append and join in same commmand .Please help me to change the below sql to splunk search

SELECT sum(q.total)
FROM [SE_COMP].[Q] q -- need in first row

SELECT sum(q.total)
FROM [SE_COMP].[Q] q
INNER JOIN [SE_COMP].[R] r ON q.StIP = r.StIP AND q.EnIP = r.EnIP -- need in second row after stats at the end of search.

I am in need of two rows values with ,

sum(q.total) in first row and
combined values in second search in second row after stats.

Please hep in framing the search .

Re: How to use append and join in same search

HiroshiSatoh — Fri, 27 Oct 2017 13:36:54 GMT

How is this search sentence?

(Search q)
|join StIP EnIP  type=outer [search (Search r)|eval flg=1]
|stats sum(total) as all_total,sum(eval(if(flg=1,total,0))) as r_total

※Please be careful when increasing the number of sub search because there is a limit of 10000 defaults.

Re: How to use append and join in same search

umsundar2015 — Fri, 27 Oct 2017 14:13:40 GMT

Hi Hiroshi,

Thank you for your search.

I am getting no results in this .Can you please help me with some other search

Re: How to use append and join in same search

DalJeanis — Tue, 29 Sep 2020 16:26:22 GMT

So, first, go read this so you understand how to think about SPL, as opposed to SQL. A direct translation is almost always inefficient.

https://answers.splunk.com/answers/561130/sql-analogy-for-my-log-search-join-two-tables-wher.html

Second, you need to turn your SQL specifications into plain English

1) There is a table called Q in SE_Comp
2) I want the sum of the field "Total" from all of Q
3) There is also a table called R in SE_COMP
4) I want the sum of the field "Total" from Q, but only when the StIP and EnIP from Q match a record in R.

Third, code the SPL to achieve the English result.

There are at least 5 ways to code this one, depending on the characteristics of the underlying data.

Here's one....

 (your search that gets all Q) OR (your search that gets all R)
| eval FoundR=case(test to see if it is a record from R, 1.00)
| eval QTotal=case(test to see if it is a record from Q, Total from Q)
| eventstats max(FoundR) as FoundR by StIP EnIP
| eval  RTotal=FoundR*QTotal
| stats sum(QTotal) as QTotal sum(RTotal) as RTotal

Some of the details may be unneeded. For example, if there is a field total on the Q records and no field by that name on the R records, then the eval QTotal= line is unneeded. If there is any field that always appears on R that never appears on Q, then field that can be used instead of FoundR, and so on.

The above general method is always preferred to the join.

Here's another method...

 (your search that gets all Q) 
 | join type=left StIP EnIP [ your search that gets all R | table StIP EnIP | eval FoundR=1.00] 
 | stats sum(Total from Q) as QTotal sum(eval(FoundR*Total From Q)) as RTotal

This second method is equivalent to the one posted by @HiroshiSatoh. I use a couple of different tricks, but the performance should be similar.

This third method can be used when the R records are fairly static, so they can be made into a lookup table.

  (your search that gets all Q) 
 | lookup MyRLookupName StIP EnIP OUTPUT anyfieldinlookupsuchasStIP as FoundR 
 | stats sum(Total from Q) as QTotal sum(eval(case(isnotnull(FoundR),Total From Q))) as RTotal

Re: How to use append and join in same search

HiroshiSatoh — Fri, 27 Oct 2017 23:41:36 GMT

Does StIP and EnIP exist in both Q and R search results?

Please show me the search sentences you are running.

Re: How to use append and join in same search

umsundar2015 — Tue, 29 Sep 2020 16:26:46 GMT

Its working , but i am finding difference of 90k les when comparing to sql query with splunk search

Can you please help to equate the counts

Re: How to use append and join in same search

HiroshiSatoh — Mon, 30 Oct 2017 11:01:21 GMT

If there are more than 10,000 sub search results, you need to set limits.conf.
As DalJeanis explains, looking up JOIN is better. Please try lookup.

Re: How to use append and join in same search

umsundar2015 — Mon, 30 Oct 2017 11:05:00 GMT

ok. But we should not use lookup Hiroshi.

so i need to done this in this search itself.Using join also i am getting the same difference .

Re: How to use append and join in same search

HiroshiSatoh — Mon, 30 Oct 2017 11:42:51 GMT

↓There are no more than 10 thousand items and no duplication exists

search index=R|stats count by StIP,EnIP

If it is over 10,000
set limit in limits.conf for subsearch
http://docs.splunk.com/Documentation/Splunk/6.2.1/Admin/Limitsconf

↓Is this result correct?
index=q|join StIP EnIP type=outer [search index=R]
|stats sum(Total) as r_total

Re: How to use append and join in same search

umsundar2015 — Tue, 31 Oct 2017 07:51:58 GMT

hi,

How to bring the same two rows as mentioned above with only one index with Total_ip field ??

can u pls help me