Conditional searching

[ryanholland]

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

• If it finds a result, take the associated src_ip (i.e., the private IP address) and then search in sourcetype=dhcp for the src_mac, and then map to sourcetype=auth with the src_ip and src_mac in order to get the username.
• If it does NOT find a result, use the original src_translated_ip and search with it as "src_ip" in sourcetype=dhcp for the src_mac, etc....

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?

[ryanholland]

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)

[lguinn2 · Accepted solution]

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.

[lguinn2]

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.

[ryanholland]

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.

[somesoni2]

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 ]