I need to know how to pass multiple fields of subsearch to main search?

[Ankush_Kumar]

Hi Team,

My question is i have antivirus events and firewall traffic and i want to run antivirus search as a subsearch with keyword "trojan", take values like ip and user information from that subsearch and then pass those two fields to main search of firewall to see at that time of detection whether traffic was present for that ip or not and what was the username field of firewall and antivirus?

My search is:

index=cisco_asa earliest=-15m [search index=bitdefender "trojan" earliest=-15m | fields src_ip, user | rename src_ip as dest_ip | rename user as bitdefender_user] | stats values(dest_ip), values(dest_port), values(url), values(user) as firewall_user, values(bitdefender_user) by src_ip

Now my challenge is after running the above query I am not getting any results but when I will run below query after removing bitdefender_user field, I am getting results but without getting bitdefender user name. I want to see both firewall as well as bitdefender username name field in the output, how to achieve that result:-

index=cisco_asa earliest=-15m [search index=bitdefender "trojan" earliest=-15m | fields src_ip | rename src_ip as dest_ip] | stats values(dest_ip), values(dest_port), values(url), values(user) by src_ip

Just for information username field present in firewall and bitdefender is "user"

[richgalloway]

The problem with the first search is the Cisco ASA logs do not contain a field called 'bitdefender_user' so the main search won't find anything. If you run the subsearch by itself you will see what the main search will try to find. Adjust the subsearch so its results make sense when fed to the main search.

[Ankush_Kumar]

You are cisco asa does not have bitdefender_user field and thats the reason I am only using that in values statement and not in group by field. I have used dest_ip field in group by for that.

Actually I want to see both user fields from firewall as well as from antivirus agents to make sure firewall captured event and antivirus captured event both belongs to same user or not.

How I can do that?

[richgalloway]

Let's look at the base query.

index=cisco_asa earliest=-15m [search index=bitdefender "trojan" earliest=-15m | fields src_ip, user | rename src_ip as dest_ip | rename user as bitdefender_user]

The subsearch index=bitdefender "trojan" earliest=-15m | fields src_ip, user | rename src_ip as dest_ip | rename user as bitdefender_user will return a field called 'bitdefender_userthat will become part of the main search (that is what subsearches do). Did you run the subsearch by itself as I suggested? Try this variant:index=bitdefender "trojan" earliest=-15m | fields src_ip, user | rename src_ip as dest_ip | rename user as bitdefender_user | format` and you'll see exactly what is going into the main search.

[Ankush_Kumar]

Hi, Thanks for your reply when I am running query as recommended, I am not getting any output actually not even a single event(checked in verbose mode):

index=cisco_asa earliest=-15m [search index=bitdefender "trojan" earliest=-15m | fields src_ip, user | rename src_ip as dest_ip | rename user as bitdefender_user]

index=cisco_asa earliest=-15m [search index=bitdefender "trojan" earliest=-15m | fields src_ip, user | rename src_ip as dest_ip | rename user as bitdefender_user| format]

But when I am running below query after considering only IP address, I am getting output:-

index=cisco_asa earliest=-15m [search index=bitdefender "trojan" earliest=-15m | fields src_ip, user | rename src_ip as dest_ip]

Is there any way or query writeup which I can use where I can pass my subsearch IP field but in the final query output I would also see user output from antivirus as well as from firewall search.

Also I would also like to understand when we want to pass two fields from subsearch to main query, my query logic was right?

| fields src_ip, user ]| stats count by src_ip, user

[richgalloway]

To understand what is passed from a subsearch to a main search, run the subsearch with | format appended to it. This is what I told you to do in my last comment, but you did not run the subsearch by itself. The subsearch is the part of the query inside the square brackets.

[Ankush_Kumar]

This is what I got after running the subsearch alone:

No output in dest_ip
No output in bitdefender_user
search column showing NOT ()

[Ankush_Kumar]

So any update on this now? Question is still same how to show values of subsearch as well as main search combined in case field names and values are different. Example query is above.

[richgalloway]

If fields names are different you must make them the same using rename or eval in the subsearch.
If values are different you can use eval to make them the same, when it makes sense to do so, in the subsearch.