How to combine 3 queries to get a single result?

[dmenon84]

Hi ,

I have one query

index=pan_logs  "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | dedup src_ip |  table src_ip,action,application,app:subcategory

Which returns say

src_ip                 action       application                    app:subcategory
10.10.10.10            blocked         teamviewer-base               remote-access

Now for that IP I need to find username and computer name. In my winevent log the Computer Name and User Name take up the same fieldname "user". I have the following 2 queries to get this info to get username.

index=wineventlog 10.10.10.10 sourcetype="WinEventLog:Security"  eventtype=wineventlog_security name="An account was successfully logged on" Authentication_Package=Kerberos src_ip=*  eventtype=windows_logon_success user!=*$ | dedup user,src_ip| table  user,src_ip

Returns this

IP                    user
10.10.10.10           user1

To get computername, the same query except the user field has a $ in end of Computer Name

index=wineventlog 10.10.10.10 sourcetype="WinEventLog:Security" eventtype=wineventlog_security name="An account was successfully logged on"  src_ip=*   eventtype=windows_logon_success user=*$ | dedup user,src_ip| table  user,src_ip

This returns

IP                    user
10.10.10.10  ComputerName1$

Now I want to combine these 3 to get me the following

src_ip action application app:subcategory user user(rename)
10.10.10.10 blocked teamviewer-base remote-access user1 ComputerName1$
Challenge for me is to get the info our of same field "user" and then append it to first query whenever the IP matches. Should I use a join query?

Please help! Thanks in advance!

[dmenon84]

Worked Query for specific IP say 10.10.10.10

index=wineventlog sourcetype="WinEventLog:Security" eventtype=wineventlog_security name="An account was successfully logged on" src_ip=* eventtype=windows_logon_success (Authentication_Package=Kerberos user!=$) OR (user=$) [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.10.10| fields src_ip | dedup src_ip | table src_ip| rename src_ip as search ]
| dedup user,src_ip| table user,src_ip | eval ComputerName=if(like(user,"%$"),user,null()) | eval user=if(isnull(ComputerName),user,null()) | table src_ip user ComputerName
| append [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.10.10 | dedup src_ip | table src_ip,action,application,app:subcategory]
| stats values(*) as * by src_ip

Did not work when I did this - Subnet

index=wineventlog sourcetype="WinEventLog:Security" eventtype=wineventlog_security name="An account was successfully logged on" src_ip=* eventtype=windows_logon_success (Authentication_Package=Kerberos user!=$) OR (user=$) [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | fields src_ip | dedup src_ip | table src_ip| rename src_ip as search ]
| dedup user,src_ip| table user,src_ip | eval ComputerName=if(like(user,"%$"),user,null()) | eval user=if(isnull(ComputerName),user,null()) | table src_ip user ComputerName
| append [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | dedup src_ip | table src_ip,action,application,app:subcategory]
| stats values(*) as * by src_ip

I tried something else that seems to work I am not 100% sure yet . I removed this from query "| rename src_ip as search"

index=wineventlog sourcetype="WinEventLog:Security" eventtype=wineventlog_security name="An account was successfully logged on" src_ip=* eventtype=windows_logon_success (Authentication_Package=Kerberos user!=$) OR (user=$) [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | fields src_ip | dedup src_ip | table src_ip ]
| dedup user,src_ip| table user,src_ip | eval ComputerName=if(like(user,"%$"),user,null()) | eval user=if(isnull(ComputerName),user,null()) | table src_ip user ComputerName
| append [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | dedup src_ip | table src_ip,action,application,app:subcategory]
| stats values(*) as * by src_ip

[somesoni2]

Try this

index=wineventlog sourcetype="WinEventLog:Security" eventtype=wineventlog_security name="An account was successfully logged on" src_ip=* eventtype=windows_logon_success (Authentication_Package=Kerberos user!=*$) OR (user=*$) [search index=pan_logs  "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | fields src_ip | dedup src_ip |  table src_ip| rename src_ip as search ] 
| dedup user,src_ip| table  user,src_ip | eval ComputerName=if(like(user,"%$"),user,null()) | eval user=if(isnull(ComputerName),user,null()) | table  src_ip user ComputerName
| append [search index=pan_logs  "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | dedup src_ip |  table src_ip,action,application,app:subcategory]
| stats values(*) as * by src_ip

[dmenon84]

This works for just one IP not sure why and if I remove the subnet and substitute it with IPs it works everytime but I am not sure why it does that ? Can you check please?

[somesoni2]

Are you trying to provide a subnet as filter instead of a direct IP address ? Could you provide the query which didn't work for you?

[dmenon84]

Worked Query for specific IP say 10.10.10.10

index=wineventlog sourcetype="WinEventLog:Security" eventtype=wineventlog_security name="An account was successfully logged on" src_ip= eventtype=windows_logon_success (Authentication_Package=Kerberos user!=$) OR (user=$) [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.10.10| fields src_ip | dedup src_ip | table src_ip| rename src_ip as search ]
| dedup user,src_ip| table user,src_ip | eval ComputerName=if(like(user,"%$"),user,null()) | eval user=if(isnull(ComputerName),user,null()) | table src_ip user ComputerName
| append [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.10.10 | dedup src_ip | table src_ip,action,application,app:subcategory]
| stats values() as * by src_ip

Did not work when I did this - Subnet

index=wineventlog sourcetype="WinEventLog:Security" eventtype=wineventlog_security name="An account was successfully logged on" src_ip= eventtype=windows_logon_success (Authentication_Package=Kerberos user!=$) OR (user=$) [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | fields src_ip | dedup src_ip | table src_ip| rename src_ip as search ]
| dedup user,src_ip| table user,src_ip | eval ComputerName=if(like(user,"%$"),user,null()) | eval user=if(isnull(ComputerName),user,null()) | table src_ip user ComputerName
| append [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | dedup src_ip | table src_ip,action,application,app:subcategory]
| stats values() as * by src_ip

I tried something else that seems to work I am not 100% sure yet . I removed this from query "| rename src_ip as search"

index=wineventlog sourcetype="WinEventLog:Security" eventtype=wineventlog_security name="An account was successfully logged on" src_ip= eventtype=windows_logon_success (Authentication_Package=Kerberos user!=$) OR (user=$) [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | fields src_ip | dedup src_ip | table src_ip ]
| dedup user,src_ip| table user,src_ip | eval ComputerName=if(like(user,"%$"),user,null()) | eval user=if(isnull(ComputerName),user,null()) | table src_ip user ComputerName
| append [search index=pan_logs "app:subcategory"="remote-access" "teamviewer-base" src_ip=10.10.0.0/16 | dedup src_ip | table src_ip,action,application,app:subcategory]
| stats values() as * by src_ip