Appending search results with subsearch fields

[malat_UoM]

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, Workstation_IP, LAN_Logon_Time
2. Search VPN gateway logs for user logon events generated by remote devices whose IP's geolocate outside the country - Output fields = Username, Remote_IP, Country, VPN_Logon_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, Workstation_IP, LAN_Logon_Time, Remote_IP, Country, VPN_Logon_Time

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 Workstation_IP LAN_Logon_Time Remote_IP Country VPN_Logon_Time

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 VPN_Logon_Time 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?

[malat_UoM · Accepted solution]

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 Workstation_IP LAN_Logon_Time Remote_IP Country VPN_Logon_Time*

and functions exactly as I need it to.

Sorry for wasting everyone's time...

[MuS]

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