topic Re: sub-search search query in Splunk Search

sub-search search query

lelanb — Thu, 12 Aug 2010 04:46:53 GMT

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>.*)"

Re: sub-search search query

Pete_Bassill — Fri, 13 Aug 2010 22:30:03 GMT

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?

Re: sub-search search query

Stephen_Sorkin — Sat, 21 Aug 2010 05:40:50 GMT

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]

Re: sub-search search query

lelanb — Fri, 27 Aug 2010 01:16:00 GMT

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