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