Joining fields from 2 indexes- Why am I showing 0 results found?

[neerajs_81]

Hi All,  Before i post here i have tried everything under https://community.splunk.com/t5/Splunk-Search/How-to-join-2-indexes/m-p/560334  but couldnt figure out my search. 

Index01 contains  fields of interest as follows : 
host, hostname, agent_version,agent_date

The difference between host & hostname fields is host contains  name of HF server (which i dont want to correlate)  while hostname contains the list of device names  (which i want to correlate with Index02). 

In Index02., the fields of interest are: 
host (default field), _time (default field)

 To summarize, the field hostname from Index01 matches the values of the field host from Index02 . So this is the common denominator.

Requirement is for all the devices from index01,  find  out the latest time stamp (as in when the device last logged) from Index02.    Below is what i need to achieve:

hostname(Index01)	agent_date (Index01)	agent_version (Index01)	LastSeen (Index02)
xxx	xxx	xxx	xxxx

Have tried below 2 queries but no luck.   It shows 0 results found.  But if i run the search individually they show data.

index=index01
| rex field=dns "(?P<hostname>[a-zA-Z0-9-]+)."
| dedup hostname 
[ search index=Index02
 | stats latest(_time) as lastSeen_epoch  BY host
 | eval LastSeen=strftime(lastSeen_epoch,"%m/%d/%y %H:%M:%S")
 | fields host LastSeen ]
| table hostname agent_date agent_version LastSeen

OR  

index=index01 
[ search index=Index02
 | stats latest(_time) as lastSeen_epoch  BY host
 | eval LastSeen=strftime(lastSeen_epoch,"%m/%d/%y %H:%M:%S")
 | fields host LastSeen ]
| rex field=dns "(?P<hostname>[a-zA-Z0-9-]+)."
| dedup hostname 
| table hostname agent_date agent_version LastSeen

[gcusello]

Hi @neerajs_81,

please try something like this:

index=indexA OR index=indexB
| eval 
   hostname=if(index=indexA,hostname,host), 
   LastSeen=if(index=indexB,_time,"") 
| stats values(agent_date) AS agent_date values(agent_version) AS agent_version latest(LastSeen) AS LastSeen BY hostname
| eval LastSeen=strftime(LastSeen,"%Y-%m-%d %H:%M:%S")

Ciao.

Giuseppe

[neerajs_81]

No luck.  It shows 0 results.  I had to customize  your search as follows:  We need to compare against a lookup file too which has a column called host

(index=IndexA sourcetype=xx os_version IN ("Windows")) OR (index=IndexB)
| rex field=dns "(?P<hostname>[a-zA-Z0-9-]+)."
| search [inputlookup lookup_file.csv | rename host as hostname | fields hostname]
| eval LastSeen=if(index=IndexB,_time,"") 
| stats values(agent_version) values(agent_date) latest(LastSeen) AS LastSeen BY hostname
| eval LastSeen=strftime(LastSeen,"%Y-%m-%d %H:%M:%S")

[ITWhisperer]

An empty string "" is still a value - use null() to discount the events which are not from IndexB

(index=IndexA sourcetype=xx os_version IN ("Windows")) OR (index=IndexB)
| rex field=dns "(?P<hostname>[a-zA-Z0-9-]+)."
| search [inputlookup lookup_file.csv | rename host as hostname | fields hostname]
| eval LastSeen=if(index=IndexB,_time,null()) 
| stats values(agent_version) values(agent_date) latest(LastSeen) AS LastSeen BY hostname
| eval LastSeen=strftime(LastSeen,"%Y-%m-%d %H:%M:%S")

[neerajs_81]

Hi So whats happening now, is it is showing the values of the fields from IndexA only.  The LastSeen column (that comes from IndexB) is empty .  Earlier with my 2 queries it was showing 0 results.

hostname	agent_version	agent_date	LastSeen
xxx	xxxx	xxxx

[ITWhisperer]

Try with quotes around the string constants

(index="IndexA" sourcetype=xx os_version IN ("Windows")) OR (index="IndexB")
| rex field=dns "(?P<hostname>[a-zA-Z0-9-]+)."
| search [inputlookup lookup_file.csv | rename host as hostname | fields hostname]
| eval LastSeen=if(index="IndexB",_time,null()) 
| stats values(agent_version) values(agent_date) latest(LastSeen) AS LastSeen BY hostname
| eval LastSeen=strftime(LastSeen,"%Y-%m-%d %H:%M:%S")