Inner join

[Siddharthnegi]

why is inner join not working , Both searches are giving results.

| inputlookup ABCD.csv
| eval CC=mvdedup(CC)
| rename CC as "Company Code"
| streamstats first(lastchecked) as scan_check
| eval key=_key,is_solved=if(lastchecked>lastfound OR lastchecked == 1,1,0),solved=finding."-".is_solved."-".key,blacklisted=if(isnull(blfinding),0,1),scandate=strftime(lastfound,"%Y-%m-%d %H:%M:%S"),lastchecked=if(lastchecked==1,scan_check,lastchecked),lastchecked=strftime(lastchecked,"%Y-%m-%d %H:%M:%S")
| fillnull value="N.A." Asset_Gruppe Scan-Company Scanner Scan-Location Location hostname "Company Code"
| search (is_solved=1 OR is_solved=0) (severity=informational) blacklisted=0 Asset_Gruppe="*" Scan-Company="*" Location="*" Scanner="*" dns="*" pluginname="*" ip="*" scandate="***" "Company Code"="*"
| rex field=scandate "(?<new_date>\A\d{4}-\d{2}-\d{2})"
| sort 0 -new_date
| eventstats first(new_date) as timeval
| rex field=new_date "-(?<date_1>\d\d)-"
| rex field=timeval "-(?<date_2>\d\d)-"
| strcat finding "#" NessusHost sid hostid pluginid finding
| where date_1=date_2
| fields dns ip lastchecked severity pluginid pluginname scandate Asset_Gruppe Location Scan-Company "Company Code" Scan-Location solved Scanner finding
| rename dns as Hostname,ip as IP
| join type=inner Hostname
[|inputlookup device.csv
| table Hostname]

[ITWhisperer]

What is it that you are trying to achieve?

Your join appears to be joining on the Hostname field with a search which only returns a Hostname which would seem to suggest that you are just trying to determine whether Hostname (dns) exists in device.csv. Rather than using join (which is slow and has other limitations and should be avoided if possible), perhaps you should just use lookup and check for a field that exists in device.csv (assuming that device.csv contains more than one field).

[Siddharthnegi]

first search have multiple fields including Hostname , and second search only have Hostname . So I am trying to inner join to get more fields for Hostname in device.csv. Does that make sense?

[ITWhisperer]

Are you saying that device.csv has other fields apart from Hostname that you want to "join" to your events?

[Siddharthnegi]

yes thats why I am inner join devices.csv to other csv file. The Hostname in device.csv will match some Hostname from first search

[ITWhisperer]

As I said, instead of join, why not use lookup?

[Siddharthnegi]

How can i do that?

[ITWhisperer]

Instead of

| join type=inner Hostname
[|inputlookup device.csv
| table Hostname]

do something like this

| lookup device.csv
| where isnotnull(<populated field from csv>)

https://docs.splunk.com/Documentation/Splunk/9.2.2/SearchReference/Lookup