Subsearch Question

[lehrfeld]

I am trying to create a report that includes failed log on attempts from our windows security logs with the originating host name from the network_dhcp log files. I can pull the failed log ons using this search:

index=os_windows source="WinEventLog:Security" EventCode=4771 NOT user="*$" Failure_Code="0x18" | rex mode=sed field=src_ip "s/::ffff://" | stats count(user) as Attempts dc(src_ip) as IPs values(src_ip) as "IP Addresses" by user | where Attempts > 2 | table user Attempts IPs "IP Addresses" | sort -Attempts

This returns:

user    Attempts   IPs   IP Address
xxx        5        2    192.168.1.10
                         192.168.1.11
yyy        4        3    192.168.1.20
                         192.168.1.21
                         192.168.1.31

I would like to then lookup by IP Address in the dhcp logs to get the hostname of the offending workstation. This search works for this purpose:

index=network_dhcp dest_ip="192.168.1.102" | table nt_host dest_ip

Ideally the finished search would look like this...

user    Attempts   IPs   IP Address     nt_host
xxx        5        2    192.168.1.10   wkstation01
                         192.168.1.11   wkstation02
yyy        4        3    192.168.1.20   wkstation03
                         192.168.1.21   wkstation04
                         192.168.1.31   wkstation05

I have this subsearch but it does not return any results.

index=network_dhcp [search index=os_windows source="WinEventLog:Security" EventCode=4771 NOT user="*$" Failure_Code="0x18" | rex mode=sed field=src_ip "s/::ffff://"  | fields + src_ip] | table nt_host dest_ip

Thanks for any help!

Mike

[somesoni2 · Accepted solution]

Try this

index=os_windows source="WinEventLog:Security" EventCode=4771 NOT user="*$" Failure_Code="0x18" | rex mode=sed field=src_ip "s/::ffff://" | stats count(user) as Attempts dc(src_ip) as IPs by src_ip,user | where Attempts > 2 | table user Attempts IPs src_ip | sort -Attempts | join src_ip [search index=network_dhcp  | stats count by nt_host dest_ip | rename dest_ip as src_ip  | table nt_host src_ip] | stats list(src_ip) as "IP Addresses" list(nt_host) as "Host Names" by user,Attempts,IPs 

[richgalloway]

Perhaps something like this will do the job:

search index=os_windows source="WinEventLog:Security" EventCode=4771 NOT user="*$" Failure_Code="0x18" | rex mode=sed field=src_ip "s/::ffff://" | rename src_ip as dest_ip | join dest_ip [search index=network_dhcp] | stats count(user) as Attempts dc(src_ip) as IPs values(dest_ip) as "IP Addresses" by user | where Attempts > 2 | table user Attempts IPs "IP Addresses" nt_host | sort -Attempts