Splunk Search

Give result of different field if first field matches

johnblakley
Explorer

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

Tags (4)
0 Karma
1 Solution

somesoni2
Revered Legend

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 

View solution in original post

somesoni2
Revered Legend

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
Explorer

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
Explorer

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

0 Karma

somesoni2
Revered Legend

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")

0 Karma

somesoni2
Revered Legend

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.

0 Karma

johnblakley
Explorer

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

0 Karma

johnblakley
Explorer

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.

0 Karma

johnblakley
Explorer

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

0 Karma

somesoni2
Revered Legend

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?

0 Karma

johnblakley
Explorer

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! 🙂

0 Karma

somesoni2
Revered Legend

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

0 Karma

johnblakley
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...