Give result of different field if first field matches

[johnblakley]

I have a search that I want to do. If the search comes back true, then I want to take the srcIP and search in a different index. That search needs to match on that index and then provide me with a username. I have the following, but I can't get past the second search:

index=dnsserver | lookup malwaredomains domain | search isbad=true | table srcIP,domain | join srcIP [ search index=main ]

What I'm doing is searching index=dnsserver and then doing the lookup. The table is created with srcIP and domain as column headers. Then I want to join the main index. The main index has two fields: Source_Network_Address and User_Name. I figured I could get my result from the first set, join on srcIP from the first to Source_Network_Address to find a match. When that match is found, I want to only give the User_Name value in a table aligned with the first table. I'm stuck at this point.

I have 1.1.1.1 in the first search, and 1.1.1.1 and bob in the second. I want a table to look like:

srcIP domain User_Name
1.1.1.1 test.com bob

Thank you for any help you can give!
John

[somesoni2 · Accepted solution]

Try this

index=dnsserver OR index=main | stats values(domain) as domain values(User_Name) as User_Name by srcIP | lookup malwaredomains domain | search isbad=true | table srcIP domain User_Name

OR
Updated

index=dnsserver | lookup malwaredomains domain | search isbad=true | table srcIP,domain | join srcIP type=left [search index=main | stats count by Source_Network_Address User_Name | fields - count | rename Source_Network_Address as srcIP] | table srcIP domain User_Name 

Updated2 for faster execution

index=dnsserver | lookup malwaredomains domain | search isbad=true | stats count by srcIP,domain | fields - count | join srcIP type=left [search index=main [search index=dnsserver | lookup malwaredomains domain | search isbad=true | stats count by srcIP | table srcIP | rename srcIP as Source_Network_Address]| stats count by Source_Network_Address User_Name | fields - count | rename Source_Network_Address as srcIP] | table srcIP domain User_Name 

[johnblakley]

Okay...I let your query run...it takes forever, but it did work this time. I'm not 100% certain why it didn't work earlier, so I'm wondering if I'm searching a good field. I'll mark your original answer as correct though. I have some learning to do 🙂 Thank you for all of your help!

[johnblakley]

Yes they're extracted: (Italicized)

domain = storage.stgbssint.com host = myServer source = c:Windowssystem32dnsdns.log sourcetype = dns srcIP = x.x.x.x

ComputerName = myComputerName Source_Network_Address = x.x.x.x (matches above in lookup query) User_Name = Bob-Smith$ host = myComputerName source = WMI:WinEventLog:Security sourcetype = WMI:WinEventLog:Security

[somesoni2]

Are all the fields srcIP, Source_Network_Address and User_Name all extracted? (shown on the field list if you do "index=main" or "index=dnsserver")

[somesoni2]

Well, That's what the updated search does.
1) Gets the list of srcIP and domain (after filter based on lookup)

index=dnsserver | lookup malwaredomains domain | search isbad=true | table srcIP,domain

2) Gets the list of srcIP(Source_Network_Address renamed) and User_Name

index=main | stats count by Source_Network_Address User_Name | fields - count | rename Source_Network_Address as srcIP

3) correlates the data using join based on srcIP

| join type=left srcIP

The User_Name field will be empty if there are no matching srcIP between two search results.

[johnblakley]

I then need to print out the User_Name field if the Source_Network_Address and srcIP match.

[johnblakley]

Here is the "index=main Source_Network_Address=x.x.x.x" query:

7/15/14
11:07:21.000 AM
20140715110721.000000
Category=2
CategoryString=Logon/Logoff
EventCode=540
EventIdentifier=540
Show all 46 lines
ComputerName = myComputerName Source_Network_Address = x.x.x.x (matches above in lookup query) User_Name = Bob-Smith$ host = myComputerName source = WMI:WinEventLog:Security sourcetype = WMI:WinEventLog:Security

The first query has the srcIP field that I need to tie into the second queries Source_Network_Address field.

[johnblakley]

Sure.. below is the "index=dnsserver | lookup malwaredomains domain | search isbad=true" query:

7/15/14
12:11:08.000 PM
7/15/2014 12:11:08 PM 0F10 PACKET 0000000002863AE0 UDP Rcv x.x.x.x 12f6 Q [0001 D NOERROR] A .storage.stgbssint.com.
domain = storage.stgbssint.com host = myServer source = c:\Windows\system32\dns\dns.log sourcetype = dns srcIP = x.x.x.x

[somesoni2]

Is below query shows list of all srcIP and corresponding User_Name values from index main??

index=main | stats count by Source_Network_Address User_Name | fields - count | rename Source_Network_Address as srcIP

From the first query we get srcIP, domain and we then try to associate them based on srcIP to the 2nd search/subsearch. This should work if there are matching data between both the searches. Would it be possible for you to provide some sample data from both the indexes?

[johnblakley]

It still didn't show a username under the User_Name column unfortunately. I get the IP that matches on the first search along with the domain. The User_Name is in the second search, but it's not a field being searched on. I need that field based off of the match from the IP address in the first search and cross reference that to the second search. Once the association is done, I need to show the username from the User_Name field.

Thank you for all of your help again! 🙂

[somesoni2]

Try the updated answer. I believe index=main doesn't have srcIP but the equivalent field is Source_Network_Address. Updated query reflects that.

[johnblakley]

Neither of these gave the result I'm needing unfortunately. The first search takes way too long to run in that I had to stop it. The second one gave the result that I received with an earlier query where it gave the srcIP and domain, but the User_Name is empty.

Thank you for the response!
John