How to join information into one table?

[syazwani]

Hi peeps,

I want to join below information result in one table:

1st query
index=sslvpn
| iplocation src_ip
| search Country != Malaysia
| eval Country = if(isnull(Country),"unknown",Country)
| table _time, user,src_ip,Country,action
| rename user as "User ID", src_ip as "Source IP", action as "Status"

2nd query
index=sslvpn group_path="ADL"
| iplocation accessIP
| where Country !="Malaysia"
| table _time, user,accessIP,Country,action

i try to join this table as below query:

index=sslvpn
| iplocation src_ip
| search Country != Malaysia
| eval Country = if(isnull(Country),"unknown",Country)
| table _time, user,src_ip,Country,action
| append
     [search index=sslvpn group_path="ADL"
     | iplocation accessIP
     | where Country !="Malaysia"
     | rename accessIP as src_ip]
| rename user as "User ID", src_ip as "Source IP" action as "Status"

but the result is not consist of 2nd query information. please help. thankyou.

[richgalloway]

The append command gives you two separate sets of results and it's up to the author to put them together.  That's usually done with the stats command.

index=sslvpn
| iplocation src_ip
| search Country != Malaysia
| eval Country = if(isnull(Country),"unknown",Country)
| table _time, user,src_ip,Country,action
| append
     [search index=sslvpn group_path="ADL"
     | iplocation accessIP
     | where Country !="Malaysia"
     | rename accessIP as src_ip]
| stats values(*) as * by src_ip
| rename user as "User ID", src_ip as "Source IP" action as "Status"

If you want to do the same with using join:

index=sslvpn
| iplocation src_ip
| search Country != Malaysia
| eval Country = if(isnull(Country),"unknown",Country)
| table _time, user,src_ip,Country,action
| join src_ip
     [search index=sslvpn group_path="ADL"
     | iplocation accessIP
     | where Country !="Malaysia"
     | rename accessIP as src_ip]
| rename user as "User ID", src_ip as "Source IP" action as "Status"