Need a Help with Query

[satyaallaparthi]

I have two lookups, 1 with 460K rows and another with 10K rows. 

I used join to get the 10K results from 460K rows, however join is not working and not returning any results. 

I used table and stats in both lookups though no results. 

Below is the query I used: 

| inputlookup unix.csv
| eval sys_name = lower(FQDN)
| join sys_name
[| inputlookup inventory.csv
| eval sys_name = lower("*".sys_name."*")
| table Status sys_name host-ip  "DNS Name"  ]

& 

| inputlookup unix.csv
| eval sys_name = lower(FQDN)
|stats values(*) as * by sys_name
| join sys_name
[| inputlookup inventory.csv
| eval sys_name = lower("*".sys_name."*")
| table Status sys_name host-ip  "DNS Name"  ]

Any help would be greatly appreciated. 

[gcusello]

Hi @satyaallaparthi,

whats the result you're waiting for?

do you want to filter the first lookup using the second?

in this case try this:

| inputlookup unix.csv
| eval sys_name = lower(FQDN)
| search [ 
   | inputlookup inventory.csv
   | eval sys_name = lower("*".sys_name."*")
   | fields sys_name ]
| table Status sys_name host-ip  "DNS Name" 

If instead you want to take values between both the lookups, yu can use the lookup command (https://docs.splunk.com/Documentation/SCS/current/SearchReference/LookupCommandOverview) in this way:

| inputlookup unix.csv
| eval sys_name = lower(FQDN)
| lookup inventory.csv sys_name
| table Status sys_name host-ip  "DNS Name" 

My only doubt is that in the two lookups the sys_name has different format.

In this case, my hint is to elaborate the lookup to have another lookup with the correct sys_name.

Ciao.

Giuseppe

[satyaallaparthi]

Great! The 'search' function worked as intended, instead of 'join'.

[gcusello]

Hi @satyaallaparthi ,

good for you, see next time!

let me know if I can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉