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