Join not working with sub search

[RohitJ]

Hi,

Join is not returning the data with subsearch, I tried many options from other answers but nothing working out.

Target is to check how many departments are using latest version of some software compare to all older versions together. 

My search query

index=abc version!="2.0"  | dedup version thumb_print | stats count(thumb_print) as OLD_RUNS by department | join department  [search index=abc  version="2.0"  | dedup version  thumb_print | stats count(thumb_print) as NEW_RUNS by department ] | eval total=OLD_RUNS + NEW_RUNS| fillnull value=0 | eval perc=((NEW_RUNS/total)*100) | eval department=substr(department, 1, 50) | eval perc=round(perc, 2) | table department OLD_RUNS NEW_RUNS perc | sort -perc

Overall this search over 1 week time period expected to return more than 100k events. 

[RohitJ]

There was issue with key department for older version, after correcting that using eval, both solutions worked.

thanks a lot guys.

[RohitJ]

thanks @PaulPanther  @ITWhisperer  

Issue is still there with Output

department                     OLD_RUNS    NEW_RUNS   total  PERC

--------------------------------------------------------------------------
Department1                    10           0                 10  0%

Department1                     0            20               20  100%

Basically old and new count of same department not in same row so with respect to new runs all percentage comes as 100% as old runs shows as 0. 

[ITWhisperer · Accepted solution]

Did you even try my solution?

Here is a runanywhere example showing it working with dummy data

| makeresults count=100
| fields - _time
| eval department="Department ".mvindex(split("ABCDE",""),random()%5)
| eval version=round(random()%3,1)
| eval thumb_print=random()%10
``` The lines above create some dummy data and can be replaced by your index search ```
| dedup version thumb_print department
| eval version=if(version="2.0","NEW_RUNS","OLD_RUNS")
| chart count(thumb_print) by department version
| fillnull value=0
| eval total=NEW_RUNS+OLD_RUNS
| eval perc=round(100*NEW_RUNS/total,2)
| eval department=substr(department, 1, 50)
| table department OLD_RUNS NEW_RUNS perc
| sort -perc

[RohitJ]

Yes @ITWhisperer 

I tried, let me check my data again if something missing in events or try with the data you have generated. 

[RohitJ]

Hi Paul,

Thanks for help.  But this still has some issues.

Output

department                     OLD_RUNS    NEW_RUNS   total  PERC

--------------------------------------------------------------------------
Department1                    10           0                 10  0%

Department1                     0            20               20  100%

Basically old and new count of same department not in same row so with respect to new runs all percentage comes as 100% as old runs shows as 0. 

[PaulPanther]

Sorry, made a mistake with the calculation of totals. I adjusted the search in my previous answer.

[ITWhisperer]

Subsearches are limited to 50k events which is one of the issues with using joins. Also, your dedup seems to ignore whether more than one department has the same version and thumb_print (of course, unless thumb_prints or versions are unique to department). Try something like this

index=abc
| dedup version thumb_print department
| eval version=if(version="2.0","NEW_RUNS","OLD_RUNS")
| chart count(thumb_print) by department version
| fillnull value=0
| eval total=NEW_RUNS+OLD_RUNS
| eval perc=round(100*NEW_RUNS/total,2)
| eval department=substr(department, 1, 50)
| table department OLD_RUNS NEW_RUNS perc
| sort -perc

[PaulPanther]

index=abc  
 | dedup version thumb_print
| stats count(eval(if(version!="2.0",thumb_print,null()))) as OLD_RUNS count(eval(if(version="2.0",thumb_print,null()))) as NEW_RUNS by department
| fillnull value=0
| eval total=NEW_RUNS+OLD_RUNS
 | eval perc=((NEW_RUNS/total)*100) 
| eval department=substr(department, 1, 50)
 | eval perc=round(perc, 2) 
 | sort -perc