In a subsearch, retrieve last-logged-on-user, for IPs in a search for most-blocked connections from Firewall

[VexenCrabtree]

I have a search that returns the IPs that have recently been blocked the most, and I want to add the "Last Logged On User" to each row of results. Here's the first part:

 index=firewall earliest=-5m msg="Deny TCP (no connection) from *"
| stats count as Q by src_ip| sort -Q
| head 3

Results:

IP. . . . . . . BLOCKS
10.1.2.3 . . . . 20
10.2.3.4 . . . . 16
10.9.8.7 . . . . 15

I have a search that will return the last logged-on user, but I have to run this manually each time. I need to make a version of this using appendCols where I can insert "IP_from_parent_search" instead of the IP (i.e. 1.2.3.4):

search index=windows sourcetype="wineventlog:cef" 1.2.3.4 eventID=ZZZZ 
| rex "duser=(?<duser>[^ ]*)"    
| rex "dhost=(?<dhost>[^ ]*)" 
| search dhost=1.2.3.4
| head 1

The IP (1.2.3.4) appears twice because at first I scan the raw to see if the IP is there. If so, I then look at the particular field (dhost) to see that the IP is there, AND, in the correct part of the event. But I have to manually do this search, and put in the IP.

The question is... how do I combine these? The results should look like:

IP                BLOCKS      LASTUSER       LastUser_LoginDate
10.1.2.3          20          smithp         20190405T08:00
10.2.3.4          16          joness         20190405T07:52
10.9.8.7           15         admin3         20190405T07:22

My main trouble is how to make the appendCols subsearch refer to the row details for the parent search to get the IP.

[somesoni2 · Accepted solution]

Give this a try (may need to include rename commands at the end per your need)

index=firewall earliest=-5m msg="Deny TCP (no connection) from *"
 | stats count as BLOCKS by src_ip| sort -BLOCKS
 | head 3 | table src_ip BLOCKS
| map search="search  index=windows sourcetype=\"wineventlog:cef\" $src_ip$ eventID=ZZZZ 
 | rex \"duser=(?<duser>[^ ]*)\"    
 | rex\ "dhost=(?<dhost>[^ ]*)\" 
 | search dhost=$src_ip$
| eval BLOCKS=$BLOCKS$
| table dhost BLOCKS duser _time "

[VexenCrabtree]

Dude, awesome! I had been trying appendCols and subsearch... it was the MAP function I needed! Here's the finished product:

index=firewall feed_source=XXX earliest=-5m msg="Deny TCP (no connection) from *" 
| stats count as BLOCKS by src 
| sort -BLOCKS 
| head 5 
| table src BLOCKS 
| map search="search index=windows sourcetype=wineventlog:cef externalId=4624 src=$src$
  | rex \"duser=(?<duser>[^ ]*)\"
  | rex \"dhost=(?<dhost>[^ ]*)\"
  | sort -_time  | head 1
  | eval BLOCKS=$BLOCKS$
  | table src dhost duser _time BLOCKS"