How to join 2 search

[supriyagaw08]

Hello all,

I want to join 2 search based upon one common filed from both the search , 

index=14 search_name="Daily Counts" |rename A_USER_NM as USER_NM|table Date USER_NM FILE_ID FILE_NM filecount| join USER_NM
[ search index=14 earliest=-24h@h latest=now sourcetype=user source=O  001
| dedup USER_NM
| table USER_NM USER_ID indicator ]

I tried above search its working but its not displaying all results, few records are getting missed, let me know where am I making mistake or any other method that i can use.

[nickhills]

A direct answer to the question using join:

index=14 search_name="Daily Counts" 
|rename A_USER_NM as USER_NM
|table Date USER_NM FILE_ID FILE_NM filecount
| join type=left max=0 USER_NM
[ search index=14 earliest=-24h@h latest=now sourcetype=user source=O  001
| dedup USER_NM
| table USER_NM USER_ID indicator ]

I am unclear if  the dedup in the subsearch is necessary if you are missing results?

However, there are normally better/more efficient ways of obtaining the same results with stats, but it might help to see some sample data to provide you with a stats example

[supriyagaw08]

HI @nickhills  thanks for the reply but i used this and its showing the fileds USER_ID indicator as blanks even i remove dedup.

The output from my search 1 is Date| A_USER_NM| FILE_ID FILE_NM |filecount and output 2 has many fields from which i want only USER_ID and indicator. Both search has common index but different search reports

[nickhills]

That would suggest the second search is not finding any matches.  Are you sure that USER_NM is the correct field to match on (does it also need renaming perhaps?)

If you just run your second search, do you see results for USER_NM and USER_ID in the same rows?

[supriyagaw08]

Hi @nickhills thanks for your help the issue was with search 2 it was not giving complete search I was looking for , fixed that and issue was resolved.

[supriyagaw08]

@nickhills user_nm is correct common field between 2 search, also yes in output of second search USER_NM and USER_ID are in same row

[nickhills]

Just because you wrote "user_nm" (and this may be a silly question) do both search results have the field USER_NM in upper case? Fieldnames (in the join) would be case sensitive.

From what you are saying, it sounds like the search should work. Are you able to provide a screenshot (with redactions etc) of all the searches?