Splunk Search

Conditional searching

ryanholland
Explorer

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?

Tags (3)
1 Solution

lguinn2
Legend

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.

View solution in original post

0 Karma

ryanholland
Explorer
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)
0 Karma

lguinn2
Legend

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.

0 Karma

lguinn2
Legend

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.

0 Karma

ryanholland
Explorer

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.

0 Karma

somesoni2
Revered Legend

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 ]
0 Karma
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...