How do I tell which items have a match in a left-joined table?

[LordVoldemort]

Another question about getting things to come out in a table. That seems to be my biggest stumbling point with splunk queries.

For each record in table_one there may be a record in table_two. I want a table showing me which records have a corresponding record in table_two, hence the left join.

sourcetype=table_one

| rename id as common_field
| join type=left usetime=true earlier=false common_field [ search sourcetype=table_two ]
| table table_one_column, table_two_column

My problem is that nothing I have tried has revealed which records have a value in table_two. In addition to the above, I tried:

sourcetype=table_one

| rename id as common_field
| join type=left usetime=true earlier=false common_field [ search sourcetype=table_two | stats count as table_two_match ]
| table table_one_column, table_two_match

but in the resulting table, both table_two_match, and table_two_column are always empty. I'm testing against a small artificial data set to get the query right (I know the match exists) and the column to indicate the presence of a match is always empty. I should also note that I do want to return all the rows from table_one that don't have matches, I just want to be able to distinguish the ones that do.

PS. the usetime=true earlier=false part is correct. Entries in table_two appear later than table_one.

[gkanapathy · Accepted solution]

this will

sourcetype=table_one | rename id as common_field
| join type=left usetime=true earliest=false common_field
    [ search sourcetype=table_two | eval table_two_match="yes" ]
| eval table_two_match=coalesce(table_two_match,"no")
| stats count by table_two_match

this may be better depending on your data and what field are in the tables and what you care about.

sourcetype=table_one OR sourcetype=table_two
| eval common_field=case(sourcetype=="table_one",id,sourcetype=="table_two",common_field)
| stats count(eval(sourcetype=="table_one")) as c1
        count(eval(sourcetype="=table_two")) as c2
         first(interesting_table1field) as interesting_table1field
   by common_field
| where c1 > 0

[LordVoldemort]

That works perfectly, thank you. I'm still unclear on the specific rules surrounding when columns from joined searches will show up in a final result table. Do you have any links or recommended reading on the subject?