I'm unsure how to do the following. In our environment, some clients receive private IP addresses (and are translated to public) and others receive public addresses. I need to be able to enter a public IP address and then sift through logs to find the associated mac address and username.
If it's a translated public IP address, I need to FIRST check for the IP in sourcetype=firewall for src_translated_ip=
So basically, first see if it's translated; if it's not, proceed using the IP. If it is translated, find the "real" IP address, then proceed using the real IP.
I have both searches figured out independently, but I want to allow for a user to simply provide the one IP address and then use if/then/else or an equivalent to do the heavy lifting.
Ideas?
You didn't give much information about the actual log files, but I think I have pieced together this much:
sourcetype=dhcp
[ search (sourcetype=firewall src_translated_ip="$inputip$") OR (sourcetype="what" src_ip="$inputip$")
| eval src_ip=if(sourcetype=="firewall",src_translated_ip,src_ip)
| fields src_ip ]
| fields src_ip src_mac ]
$inputip$
represents the initial ip address. If you put this in a macro (or a form), then it will be easier to enter the inital ip, which can be either the public or private ip address.
This will return the public src_ip
and src_mac
. Without more information, I can't tell you how to get the username as well.
index=my_index (sourcetype=cisco:asa src_translated_ip=$IP$ src_translated_port=$PORT$) OR (sourcetype=dhcpd src_ip=$IP$) | eval endtime=strftime(_time+300,"%m/%d/%Y:%H:%M:%S") | eval starttime=strftime(_time-300,"%m/%d/%Y:%H:%M:%S") | stats last(starttime) as starttime, first(endtime) as endtime by src_ip | map search="search index=my_index sourcetype=dhcpd starttime=$starttime$ endtime=$endtime$ src_ip=$src_ip$ dhcp_message=DHCPACK" | eval endtime=strftime(_time+300,"%m/%d/%Y:%H:%M:%S") | eval starttime=strftime(_time-86400,"%m/%d/%Y:%H:%M:%S") | stats last(starttime) as starttime, first(endtime) as endtime, by src_mac src_ip | map search="search index=my_index sourcetype=aruba_authmgr starttime=$starttime$ endtime=$endtime$ src_mac=$src_mac$ IP=$src_ip$" | stats count(_raw) by _time username MAC IP role server AP host | fields - count(_raw)
You didn't give much information about the actual log files, but I think I have pieced together this much:
sourcetype=dhcp
[ search (sourcetype=firewall src_translated_ip="$inputip$") OR (sourcetype="what" src_ip="$inputip$")
| eval src_ip=if(sourcetype=="firewall",src_translated_ip,src_ip)
| fields src_ip ]
| fields src_ip src_mac ]
$inputip$
represents the initial ip address. If you put this in a macro (or a form), then it will be easier to enter the inital ip, which can be either the public or private ip address.
This will return the public src_ip
and src_mac
. Without more information, I can't tell you how to get the username as well.
Yes! I find that a lot of folks tend to use join
(if they have an SQL background like I do) - when OR
is a far better choice in Splunk.
Thanks! The "OR" usage for sourcetype is what really kicked me off in the right direction. For whatever its worth, here's what I'm using for a macro. It takes an entered IP and port and sees if it's in the firewall logs, and if so, gets the src_ip tied to that entered IP. Then in all cases it looks for the src_ip in dhcp logs to get the mac address, then takes the IP and mac address and searches back through aruba (wireless) logs in order to find the username.
Try this
index=<yourindex> sourcetype=<yoursourcetype> | join type=outer src_translated_ip
[search sourcetype=firewall | stats count by publicip | rename publicip as src_translated_ip
| eval matchFouund=1 | fields - count]
|eval src_ip=case(isnoutnull(matchFound),src_ip,1=1,src_translated_ip)
|join type=outer src_ip [search sourcetype=dhcp | stats count by src_mac| fields src_mac]
|join type=outer src_ip,src_mac [search sourcetype=auth | stats count by src_ip, src_mac, username |fields - count ]