Splunk _time is not working with Inner join

[sarfarajsayyad]

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.

/Sarfaraj

[MuS]

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

cheers, MuS

[dpkumaran]

This means there is no common device id between two different indexes.

[sameera123]

can you try this
index="device" | dedup device_id | join device_id [search index=“asset" | rename assoc_device_id as device_id |dedup device_id|table _time,device_id] | table _time, device_id

[javiergn]

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 device_id that you think it should be matching. Then run individual searches against both indexes but filtering by device_id=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.

Thanks,
J

[sarfarajsayyad]

Here is the query which is not working

*index="device" | dedup device_id | join device_id [search index=“asset" | rename assoc_device_id as device_id |dedup device_id] | table _time, **

but if i use left join it worked properly

*index="device" | dedup device_id | join type=left device_id [search index=“asset" | rename assoc_device_id as device_id |dedup device_id] | table _time, **

[jkat54]

Did you try ...|join usetime=true ...

[sarfarajsayyad]

No its not working

[javiergn]

Hi,

Could you paste your query here?

Thanks,
J