Splunk Search

How to get at two fields from a subsearch that has a subsearch?

Explorer

I am working with a search like this:

dovecot 
  [ search DHCPACK 
    [ search host="airport*" "Associated with station" | rename mac_addr as src_mac | fields src_mac ] 
  | rename src_ip as rip | fields rip,src_mac ] 
| dedup rip

The problem is that if I specify both rip and src_mac as fields of interest from the child subsearch (DHCPACK level) I end up with a parent search (dovecot level) that looks like this:

dovecot (src_mac="aa:bb:cc:dd:ee:ff" AND rip="1.2.3.4") OR (src_mac=... AND rip=...)...

However, what I'm really interested in is correlating the MAC from the airports to an IP from dhcpd and that IP to a user ID from Dovecot's IMAP logs (I'll expand the search to use other log sources such as Postfix SASL authentication at another time). The end product should be a table displaying the mac address, IP address and user ID.

Sample data is at http://pastie.org/1449528

Tags (2)
1 Solution

Splunk Employee
Splunk Employee

You probably want to use the join command. Perhaps something like:

dovecot   [ search DHCPACK     [ search host="airport*" "Associated with station" | rename mac_addr as src_mac | fields src_mac ]   | rename src_ip as rip | fields rip] | join rip [ search DHCPACK     [ search host="airport*" "Associated with station" | rename mac_addr as src_mac | fields src_mac ]   | rename src_ip as rip | fields rip, src_mac] | dedup rip

Note that this is less than optimal since we are essentially doing the same subsearch twice. To fix that, we can take advantage of outputcsv and inputcsv:

dovecot   [ search DHCPACK     [ search host="airport*" "Associated with station" | rename mac_addr as src_mac | fields src_mac ]  | rename src_ip as rip | outputcsv mac_ip_table.csv | fields rip] | join rip [inputcsv mac_ip_table.csv | fields rip, src_mac] | dedup rip

View solution in original post

Hello,

I am stuck with a similar scenario and can not figure out the right way out. I want my Sub search to retrieve 2 fields out of which 1 exist in primary search the other one really needs to just hang out in the end result. I might not be able to use the join in the right manner

my search:
index = ids [search index=vm sourcetype="vuln" daysago=30 cvss>7 | dedup dest | table dest | rename dest as DstIP]|join dest[search index=vm sourcetype="vuln" daysago=30 cvss>7| dedup signature | table signature ] AND [search index=windows sourcetype="script" |stats first(SymantecDefsDate) as SymantecDefDate by host |eval Symdef_exp_date=relative_time(now(), "-3d@d" ) |eval SymDef_Date=strptime(SymantecDefDate,"%Y-%m-%d") |eval SymDefination_Date=if(SymDef_Date7 | dedup dest | table dest | rename dest as DstIP]|join
dest[search index=vm sourcetype="vuln" daysago=30 cvss>7| dedup signature | table signature ]
Subsearch 2: [search index=windows sourcetype="script" |stats first(SymantecDefsDate) as SymantecDefDate by host |eval
Symdef_exp_date=relative_time(now(), "-3d@d" ) |eval SymDef_Date=strptime(SymantecDefDate,"%Y-%m-%d") |eval
SymDefination_Date=if(SymDef_Date<Symdef_exp_date,"noncomplaint","complaint")| search
SymDefination_Date=noncomplaint | lookup dnslookup clienthost AS host | fields clientip | dedup clientip |rename clientip as DstIP]

But My join here does not give back the correct result for signature/sig in the final output. I have tried both join with 'dest' and 'DstIP' but no luck . Need your expert advise

@steveyz @woodcock

0 Karma

SplunkTrust
SplunkTrust

@Sayanta_Basak_Infy You are adding on to a very old thread that has an accepted solution. For better chances at a helpful response, please post a new question describing your problem.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Splunk Employee
Splunk Employee

Alternatively, you can do a transaction command on mac_addr and src_ip and get all the values, filtering out any that don't meet the airport requirement, normalizing your field names first.

0 Karma

Splunk Employee
Splunk Employee

You probably want to use the join command. Perhaps something like:

dovecot   [ search DHCPACK     [ search host="airport*" "Associated with station" | rename mac_addr as src_mac | fields src_mac ]   | rename src_ip as rip | fields rip] | join rip [ search DHCPACK     [ search host="airport*" "Associated with station" | rename mac_addr as src_mac | fields src_mac ]   | rename src_ip as rip | fields rip, src_mac] | dedup rip

Note that this is less than optimal since we are essentially doing the same subsearch twice. To fix that, we can take advantage of outputcsv and inputcsv:

dovecot   [ search DHCPACK     [ search host="airport*" "Associated with station" | rename mac_addr as src_mac | fields src_mac ]  | rename src_ip as rip | outputcsv mac_ip_table.csv | fields rip] | join rip [inputcsv mac_ip_table.csv | fields rip, src_mac] | dedup rip

View solution in original post

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!