Splunk Search

Splunk _time is not working with Inner join

sarfarajsayyad
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

MuS
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

dpkumaran
New Member

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

0 Karma

sameera123
Explorer

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

0 Karma

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

0 Karma

sarfarajsayyad
New Member

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

0 Karma

jkat54
SplunkTrust
SplunkTrust

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

0 Karma

sarfarajsayyad
New Member

No its not working

0 Karma

javiergn
SplunkTrust
SplunkTrust

Hi,

Could you paste your query here?

Thanks,
J

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...