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

[lisa_1]

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

[Sayanta_Basak_I]

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

[richgalloway]

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

[carasso]

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.

[steveyz · Accepted solution]

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