Splunk Search

Appending search results with subsearch fields

Explorer

Hi, folks,

I'm building an alert to detect anomalous logons, intending to use the following (simplified) logic,

  1. Search Windows logs for user logon events generated by LAN workstations - Output fields = Username, WorkstationIP, LANLogon_Time
  2. Search VPN gateway logs for user logon events generated by remote devices whose IP's geolocate outside the country - Output fields = Username, RemoteIP, Country, VPNLogon_Time
  3. Search for usernames which appear in the results of both Search 1 and Search 2 - any results are anomalous (unless the user hopped on a plane right after work and jetted halfway around the world in a couple of hours)
  4. Present results as: Username, WorkstationIP, LANLogonTime, RemoteIP, Country, VPNLogonTime

The Splunk search I've built to do this looks as follows (omitting unnecessary details - I'm renaming and reformatting the pertinent fields in both searches correctly, dedupping, coalescing and trimming where necessary),

{Search - Windows logs} | search Username [search {Search-VPN logs} | fields Username] | table Username WorkstationIP LANLogonTime RemoteIP Country VPNLogonTime

The results of the search are correct insofar as the returned Username values are concerned, but I can't figure out how to pass the Remote_IP, Country and VPNLogonTime fields from the subsearch into the results of the main search, so that every Username hit, which tells us the LAN logon details, gets appended with the applicable VPN logon details.

Any ideas?

1 Solution

Explorer

Turns out all I had to do was learn how to use join correctly.

The query now reads.

{Search - Windows logs} |* join** Username [search {Search-VPN logs}] | table Username WorkstationIP LANLogonTime RemoteIP Country VPNLogonTime*

and functions exactly as I need it to.

Sorry for wasting everyone's time...

View solution in original post

Explorer

Turns out all I had to do was learn how to use join correctly.

The query now reads.

{Search - Windows logs} |* join** Username [search {Search-VPN logs}] | table Username WorkstationIP LANLogonTime RemoteIP Country VPNLogonTime*

and functions exactly as I need it to.

Sorry for wasting everyone's time...

View solution in original post

SplunkTrust
SplunkTrust

Hi malat_UoM,

maybe not the answer you did expect, but take a look at this answer http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi... to get some nice examples on how you could do this without using append.

Hint: eventstats or streamstats are your friends ....

cheers, MuS

0 Karma