How to do looping search by lookup table

[JuliaCheng]

Hi:
I am trying to do looping search using lookup tables and map command, however, I cannot get the correct result. If possible, please help me get correct search command.

It is my logs.

Blockquote
md5=7e8b33fdaf6ff8a8e228883019bf7049 filetype="PE32 executable (GUI) Intel 80386, for MS Windows" dnsinfo_hostname=etsiunjour.fr dnsinfo_ip=176.31.255.41
Blockquote

First, I would like to get the value of dnsinfo_hostname field. Then I do lookup from the following csv file

dnsinfo_hostname, resolved_IP
etsiunjour.fr, 90.156.201.31
etsiunjour.fr, 90.156.201.71
etsiunjour.fr, 90.156.201.94
etsiunjour.fr, 90.156.201.113
aaa.com, 90.156.201.94
bbb.com, 90.156.201.71
ccc.com, 90.156.201.94

When I did the search to get dnsinfo_hostname=etsiunjour.fr with its resolved_Ip=[90.156.201.31, 90.156.201.71 ,90.156.201.94, 90.156.201.113] . For each resolve_IP, do lookups csv fil again to get:

90.156.201.94 ->[aaa.com, ccc.com]
90.156.201.71 ->[bbb.com]

Finally. I would like to show :
hostname=etsiunjour.fr, resolved_IP=[90.156.201.31, 90.156.201.71 ,90.156.201.94(aaa.com, ccc.com), 90.156.201.113]，

Is it possible Splunk can help me do this ? Or I have to do it using external python code.
Thanks!

[alacercogitatus]

I wouldn't use the map command, it is a very expensive command to use in terms of search processing. Try this one:

sourcetype=YOURSOURCETYPE index=*| lookup YOURFILE.csv dnsinfo_hostname OUTPUT resolved_IP|stats values(dnsinfo_hostname) as original_hostname by resolved_IP|lookup YOURFILE.csv resolved_IP
OUTPUT dnsinfo_hostname

[Ayn]

It's fairly straightforward to get something that KIND OF works. To get a list of all the IP's and domains associated with the input domain, you could do:

| inputlookup yourlookup | search [| inputlookup yourlookup | search dnsinfo_hostname="etsiunjour.fr" | fields resolved_IP] | stats values(dnsinfo_hostname) by resolved_IP

This will first of all run a subsearch that gets all IP's associated with the "etsiunjour.fr" domain in the lookup, then once again check the lookup which domains are associated with those IP's. The output will be the resolved IP's and a list of associated domains for each.

Moving on from that, there are a number of tricky things that need to be solved in order to get the exact format you're looking for. You need to find the domains that are NOT the input domain, and add them to a comma separated list that is then put within parantheses after the IP number. Finally, all these IP's including any extra info should be put into a multivalued field. After messing around a bit, I came up with the following search that should do what you want:

| inputlookup dns.csv
| search
  [| inputlookup dns.csv 
   | search dnsinfo_hostname="etsiunjour.fr" | fields resolved_IP ]
| mvcombine dnsinfo_hostname
| eval resolved_IP=if(mvcount(dnsinfo_hostname)>1,resolved_IP+"("+mvjoin(mvfilter(NOT match(dnsinfo_hostname,"etsiunjour.fr")),",")+")",resolved_IP)
| eval dnsinfo_hostname="etsiunjour.fr"
| mvcombine resolved_IP

While I can't say it looks pretty, it does work and shows some of the flexibility you get with Splunk's search language. 🙂

Note that the input domain ("etsiunjour.fr") is used in three places in the search. This is because once the initial resolving and reverse resolving is done, it's hard to know what the original input domain was. The easiest would be to implement this search as a macro, or in a form that can just refer to the input domain as a variable name.

[Ayn]

Awesome! If you feel your question was answered, could you please mark the answer as accepted? Thanks!

[JuliaCheng]

Thank you very much. And I really appreciate your answer and it works SUPER fine.
finally, it shows

dnsinfo_hostname resolved_IP
etsiunjour.fr 90.156.201.113(aaa.com)
90.156.201.31
90.156.201.71
90.156.201.94(bbb.com)