Splunk Search

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

lisa_1
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

steveyz
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

Sayanta_Basak_I
Explorer

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

richgalloway
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, Karma would be appreciated.
0 Karma

carasso
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

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