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.
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
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
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?
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.
@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
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?