Dashboards & Visualizations

How can I use a subsearch with a join command to bring in a field token?

mihall
Path Finder

My search is as follows:
index=windows Logon_Type=10 EventCode=4624 svc_* | stats count by user host src_ip | join user host src_ip [search index=windows $src_ip$ eval user_new=mvindex(Security_ID, 1) | fields user_new]

Everything before the join works. I am trying to take src_ip from those results, and join them to user_new, which uses the src_ip to find user_new.

Any suggestions to how I could change this query?

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi mihall,
you used in the join three fields (user, host, src_ip), instead in your subsearch there is only one field after the fields commans (user_new) in addition fields in join must have the same name both in search and subsearch.
In addition in the subsearch there's a missed pipe (|) before eval.
See http://docs.splunk.com/Documentation/Splunk/6.6.1/SearchReference/Join
Bye.
Giuseppe

0 Karma

mihall
Path Finder

Thanks for the help cleaning this up. I am still confused by the specifications of the join command. Could you possibly give me an example of how to format this properly?

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi mihall,
Try something like this

index=windows Logon_Type=10 EventCode=4624 svc_* 
| stats count by user host src_ip 
| join user host src_ip [search index=windows | fields user host src_ip]
| table count user host src_ip other_fields

I don't know exactly which fields you have in Windows index, if in windows index your have different fields your have to rename them.

index=windows Logon_Type=10 EventCode=4624 svc_* 
| stats count by user host src_ip 
| join user host src_ip [search index=windows | rename field1 AS user field2 AS src_ip | fields user host src_ip]
| table count user host src_ip other_fields

Bye.
Giuseppe

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@mihail - basically, you join on fields, whatever you are matching. In the following code, both sides of the join have user and host. The join then creates one record for each combination of user and host that is in both sides.

(some search that gives user host src_ip)
| join user host [ some search that gives user host myotherfield]
| table user host src_ip myotherfield

Okay, technically, it creates one record for each event in the left side where the combination of user and host in the left side exists as a combination in the right side. If there are dups coming down the left side, then there will be dups going out the bottom.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

You can't pass a column value as token (like you're trying) using join. Before I suggest anything, the subsearch for getting user_new field, is it just any event from that src_ip OR there is any specific event code you look at? If any event with that src_ip can be used to create user_new field, then why not do directly on the base search itself. Like this

index=windows Logon_Type=10 EventCode=4624 svc_* |eval user_new=mvindex(Security_ID, 1)| stats count values(user_new) as user_new by user host src_ip

mihall
Path Finder

So I'm new to this, and trying to analyze a lot of information. This search works much better, but It's not exactly what I'm looking for.
I'm trying to find logins made on svc_ accounts, and match the IP address that logged on at that time back to the IP address of a user.
I thought the best way to do that would be to first search to find the initial IP where the login occurred, and use a subsearch take that IP and find the user.
Any ideas how this search could be modified to do that? Or do I need to take a different approach?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Do you have a specific query where, give a src_ip, you can find user name associated with that IP? Do the src ip appears in different field place in both type of events (event with svc_* and other to detect user associated with that IP). If you're able to narrow down that subsearch, we can extract the src_ip (if not already) and correlate based on that common field (without join, possible much more efficient way).

mihall
Path Finder

I do not have a specific query, hence I was trying to accomplish that through the subsearch. I was looking into svc_* and then searched the IP address and found a Security_ID on that IP address at the same time that linked me to the username accessing the account.
So I'm not sure how to make a query that will extract that IP field to be able to correlate it.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

A subsearch is a search also, so when you run the search manually with some hardcoded value of src_ip, you must get some specific events. So what you can do is take a sample src_ip value from the first/base search, run the subsearch independently and try to find criteria to narrow down the query (any specific Event Code, or any other field)

index=windows "static src_ip value from first search" 

The subsearch has several limitation including timeouts and it's always better to narrow your search down the specific events that you're interested in.

mihall
Path Finder

So the subsearch should be able to work as its own search?
I can search for a specific IP and find the Security_ID I'm looking for, but my goal was to produce a query that would pull all IP addresses that apply to the initial search, and apply them to the subsearch.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

I understand your final requirement but to be able to merge both the queries (main search and subsearch) to produce desired result, you need a working, possibly optimized query. When you search for a specific IP to find Security_ID, do you just get a single Event? What is the sourcetype/EventCode/any other field value for that search?

0 Karma

mihall
Path Finder

I'm having trouble creating that query. I found it while looking through multiple events, but I can't figure out how to find it with a query.
The sourcetype is the same, I've also been using EventCode=4624. Apart from that, I'm not sure why I haven't been able to find it again.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Just run this a see if you can find some pattern (do all events have same sourcetype and/or EventCode.

index=windows "a sample src ip value" | eval user_new=mvindex(Security_ID, 1)| table sourcetype EventCode user_new

mihall
Path Finder

Awesome. They all have the same sourcetype. And I was simplifying it looking for logon events with EventCode=4624.
But, the user_new value has a lot of nulls. Some of the values are svc_* accounts, while others are the usernames I was looking for.

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...