How to edit my subsearch syntax to combine the results of my two searches?

[tinylund]

Thanks in advance for any assistance..

I am trying to create an alert that creates a table that shows sourceIP, country, LogMessageID, VPNuser and VPNgroup for attempted VPN connections. I get the sourceIP for successful session creations to the IP address of the VPN devices.

     host=<perimeterFW> session_state!="denied" destIP=<VPN device IP> | iplocation sourceIP | table sourceIP country

That search works - I get a table showing all the sourceIPs and corresponding Countries.

I have extracted some fields from the internal firewall logs, LogMessageID, VPNsourceIP, VPNuserID and VPNgroupID.

Then I want to search the internal firewall logs to see if the sessions was successful (LogMessageID = 113039) or unsuccessful (LogMessageID = 302014). If I create a search:

     host=<internal firewall> (LogMessageID = 113039 OR LogMessageID=302014) | table LogMessageID VPNsourceIP VPNuserID VPNgroupID

Again, I successfully produce a table with the LogMessageID, VPNsourceIP, VPNuserID, and VPNgroupID.

Now I want to combine the two searches in an alert that triggers each time a VPN session is attempted:

  host=<perimeterFW> session_state!="denied" destIP=<VPN device IP> | iplocation sourceIP | (haven't found the correct command) [ host=<internal firewall> VPNsourceIP=sourceIP (LogMessageID = 113039 OR LogMessageID=302014) | return LogMessID=LogMessageID VPNuser=VPNuserID VPNgroup=VPNgroupID ] | table sourceIP Country LogMessID VPNuser VPNgroup

Currently I get a table with the sourceIP and country, but the other 3 columns are blank.

Thanks for your assistance.

[sundareshr]

See if this helps

(host=<perimeterFW> session_state!="denied" destIP=<VPN device IP>) OR (host=<internal firewall> (LogMessageID = 113039 OR LogMessageID=302014)) | eval ip=coalesce(sourceIP, VPNsourceIP) | iplocation ip | stats values(Country) as Country, values(LoGMessID) as LogMessID values(VPNuser) as VPNuser values(VPNgroup) as VPNgroup by ip

[tinylund]

Went back to:

 (host= session_state!="denied" destIP=) OR (host= (LogMessageID = 113039 OR LogMessageID=302014)) | eval ip=coalesce(sourceIP, VPNsourceIP) | iplocation ip | stats values(Country) as Country, values(LoGMessID) as LogMessID values(VPNuser) as VPNuser values(VPNgroup) as VPNgroup by ip

removed the LogMessageID=302014 - for unsuccessful and now I get a table with successful connections populating the message, user and group column (unsuccessful connections have the 3 columns unpopulated). Now I will just audit for accuracy.

[sundareshr]

@tinylund did this check out OK. If it did, please mark the answer as accepted to close out the question.

[tinylund]

Stepped backwards - that gives me 0 events

[tinylund]

If I remove the sourceIP=VPNsourceIP from the second search, it tries to populate the other 3 columns - is there not a way to tie the second search to the sourceIP of the first search?

[sundareshr]

Try this

   host=<perimeterFW> session_state!="denied" destIP=<VPN device IP> | iplocation sourceIP | join sourceIP [ host=<internal firewall> (LogMessageID = 113039 OR LogMessageID=302014) | rename VPNSourceIP as SourceIP | return LogMessID VPNuser VPNgroup ] | table sourceIP Country LogMessID VPNuser VPNgroup

[tinylund]

I still only get the IP and country in the table, the firewall message ID, VPN user and VPN group are still unpopulated.

[sundareshr]

Can you verify the fields names in this search? This should give all the fields (provided their the right fields).

[kiran331]

Use Join command:

 host= session_state!="denied" destIP= | iplocation sourceIP |join sourceIP [search  host=  (LogMessageID = 113039 OR LogMessageID=302014) | rename VPNsourceIP  as sourceIP ]| table sourceIP Country LogMessID VPNuser VPNgroup

[tinylund]

The join documentation states:

A join is used to combine the results of a search and subsearch if specified fields are common to each.

The fields in the subsearch are not common to the first search.

[JoshuaJohn]

host= session_state!="denied" destIP= | iplocation sourceIP | append [ host= VPNsourceIP=sourceIP (LogMessageID = 113039 OR LogMessageID=302014) | return LogMessID=LogMessageID VPNuser=VPNuserID VPNgroup=VPNgroupID ] | table sourceIP Country LogMessID VPNuser VPNgroup

Did you try append?
or the join command?

https://answers.splunk.com/answers/144351/what-are-the-differences-between-append-appendpipe.html
http://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Join

[tinylund]

The append command states that is will not work in real-time, I am trying to get an alert that will monitor VPN connections in real-time.

The join documentation states:

A join is used to combine the results of a search and subsearch if specified fields are common to each.

The fields in the subsearch are not common to the first search.