We have an inner join on two indexes. When we are querying with time controller its not showing data properly with Today, Yesterday. Only All Time is working fine(Most probably its using *). But if i use left join its showing data correctly with left table _time.
My requirement is we need inner join and work with time controller properly.
Here is the query which is not working
*index="device" | dedup deviceid | join deviceid [search index=“asset" | rename assocdeviceid as deviceid |dedup deviceid] | table _time, **
but if i use left join it worked properly
*index="device" | dedup deviceid | join type=left deviceid [search index=“asset" | rename assocdeviceid as deviceid |dedup deviceid] | table _time, **
Ok, if the left join is matching and the inner one is not, it means there are no device_id matches between asset and device indexes.
If you think this is not the case then run your left one and select and deviceid that you think it should be matching. Then run individual searches against both indexes but filtering by deviceid=theoneyoupicked.
Only one of the indexes should return an entry.
You can also try the append command instead of join and then do a stats count by device_id. Because of the dedup there should be entries where count =1 and entries where count=2 (those that matched).
In general I wouldn't use join as you are not dealing with SQL tables here. I am writing this from my phone so can't easily show you any examples but so a quick search and you'll find lots of good examples on how to use stars instead of join.
Hope that helps.
can you try this
index="device" | dedup deviceid | join deviceid [search index=“asset" | rename assocdeviceid as deviceid |dedup deviceid|table time,deviceid] | table time, deviceid
In almost 95% of the use cases join or any other sub searches is not the work horse you are looking for because of many reasons, like the limit of events returned but main because they are expensive to run!
Take a look at this answer to learn some other commands that will do the same but much faster https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...