Splunk Search

sub-search search query

lelanb
Engager

I'm trying to run a search query like this:

host=linux1 DHCPACK | rex field=_raw "on (?<ip>.*) to (?<mac>.*)" | [search host="node1" OR host="stadc01" OR host="stadc02" <<username  goes here>> EventCode=540 | top limit=1 Source_Network_Address | fields + Source_Network_Address] 

Which finds the latest login from the given username, extracts the ip address from the event log, and then finds and returns the mac address via some DHCP logs (matching the IPs) this is so that we can find the physical location of the machine. It's giving me various errors, however- not to mention that the current query is incomplete. Any ideas to get the query working right? Thanks for the help 🙂

Example DHCP entry:

8/11/10 2:29:19.000 PM

Aug 11 14:29:19 linux1 dhcpd: DHCPACK on 10.182.171.65 to 00:xx:12:xx:x0:xc via 10.182.171.2

* host=linux1   Options|  
* sourcetype=syslog   Options|  
* source=/var/log/syslog   Options

Example EventLog login entry:

8/11/10 2:36:03.000 PM

08/11/10 02:36:03 PM LogName=Security SourceName=Security EventCode=540 EventType=8 Type=Success Audit ComputerName=DC02 User=<> Sid=S-1-5-21-767897961-102478171-4665678964-895678 SidType=1 Category=2 CategoryString=Logon/Logoff RecordNumber=1384567698 Message=Successful Network Logon:

User Name:  <<USERNAME>>

Domain:     STAFF

Logon ID:       (0x0,0x1E1EA75)

Logon Type: 3

Logon Process:  Kerberos

Authentication Package: Kerberos

Workstation Name:   

Logon GUID: {9e4539d92-ba06-83435-22td-9ddsfg45b23ec8}

Caller User Name:   -

Caller Domain:  -

Caller Logon ID:    -

Caller Process ID: -

Transited Services: -

Source Network Address: 10.1.23.123

Source Port:    0

Collapse back to 10 lines

* host=dc2   Options|  
* sourcetype=WinEventLog:Security   Options|  
* source=WinEventLog:Security   Options|  
* linecount=45   Options|  
* splunk_server=logger   Options|  
* index=main   Options

Attempted search query (part of the one above):

host=linux1 DHCPACK | rex field=_raw "on (?<ip>.*) to (?<mac>.*)" 
1 Solution

Stephen_Sorkin
Splunk Employee
Splunk Employee

Your search is nearly correct. You just need to tell Splunk that you want the output of the subsearch to be an argument to the search command and also that the Source_Network_Address field should be called "ip" in the outer search:

host=linux1 DHCPACK | rex field=_raw "on (?<ip>.*) to (?<mac>.*)" | search [search host="node1" OR host="stadc01" OR host="stadc02" <<username  goes here>> EventCode=540 | top limit=1 Source_Network_Address | fields Source_Network_Address | rename Source_Network_Address as ip]

Now if you configure an automatic extraction in props.conf for this data say:

[source::/var/log/syslog]
EXTRACT-dhcp = DHCPACK on (?<ip>.*) to (?<mac>.*)

Then your search can be much shorter, and faster as well, since we can use the index to look for the ip, rather than perform a full table scan:

host=linux1 DHCPACK [search host="node1" OR host="stadc01" OR host="stadc02" <<username  goes here>> EventCode=540 | top limit=1 Source_Network_Address | fields Source_Network_Address | rename Source_Network_Address as ip]

View solution in original post

Stephen_Sorkin
Splunk Employee
Splunk Employee

Your search is nearly correct. You just need to tell Splunk that you want the output of the subsearch to be an argument to the search command and also that the Source_Network_Address field should be called "ip" in the outer search:

host=linux1 DHCPACK | rex field=_raw "on (?<ip>.*) to (?<mac>.*)" | search [search host="node1" OR host="stadc01" OR host="stadc02" <<username  goes here>> EventCode=540 | top limit=1 Source_Network_Address | fields Source_Network_Address | rename Source_Network_Address as ip]

Now if you configure an automatic extraction in props.conf for this data say:

[source::/var/log/syslog]
EXTRACT-dhcp = DHCPACK on (?<ip>.*) to (?<mac>.*)

Then your search can be much shorter, and faster as well, since we can use the index to look for the ip, rather than perform a full table scan:

host=linux1 DHCPACK [search host="node1" OR host="stadc01" OR host="stadc02" <<username  goes here>> EventCode=540 | top limit=1 Source_Network_Address | fields Source_Network_Address | rename Source_Network_Address as ip]

lelanb
Engager

Ah, thanks. I already got similar functionality using a join statement, but the auto-extraction looks like it would work even better.

0 Karma

Pete_Bassill
Path Finder

Hi

I have a simple sub search working for IP addresses,

source="/var/log/iptables.log" [search source="/var/log/iptables.log" SRC!=192.168.* | top SRC limit=1 | fields SRC]

Is that pipe before your sub string causing the issue?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...