Splunk Search
Highlighted

Splunk _time is not working with Inner join

New Member

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

Tags (3)
0 Karma
Highlighted

Re: Splunk _time is not working with Inner join

SplunkTrust
SplunkTrust

Hi,

Could you paste your query here?

Thanks,
J

0 Karma
Highlighted

Re: Splunk _time is not working with Inner join

New Member

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, **

0 Karma
Highlighted

Re: Splunk _time is not working with Inner join

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: Splunk _time is not working with Inner join

New Member

No its not working

0 Karma
Highlighted

Re: Splunk _time is not working with Inner join

SplunkTrust
SplunkTrust

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.

Thanks,
J

0 Karma
Highlighted

Re: Splunk _time is not working with Inner join

Explorer

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

0 Karma
Highlighted

Re: Splunk _time is not working with Inner join

New Member

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

0 Karma
Highlighted

Re: Splunk _time is not working with Inner join

SplunkTrust
SplunkTrust

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