Dashboards & Visualizations
Highlighted

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

Explorer

My search is as follows:
index=windows LogonType=10 EventCode=4624 svc* | stats count by user host srcip | join user host srcip [search index=windows $srcip$ eval usernew=mvindex(SecurityID, 1) | fields usernew]

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

Any suggestions to how I could change this query?

0 Karma
Highlighted

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

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 usernew field, is it just any event from that srcip OR there is any specific event code you look at? If any event with that srcip can be used to create usernew 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
Highlighted

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

Explorer

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
Highlighted

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

SplunkTrust
SplunkTrust

Do you have a specific query where, give a srcip, 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).

Highlighted

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

Explorer

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 SecurityID 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
Highlighted

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

SplunkTrust
SplunkTrust

A subsearch is a search also, so when you run the search manually with some hardcoded value of srcip, you must get some specific events. So what you can do is take a sample srcip 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.

Highlighted

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

Explorer

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
Highlighted

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

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
Highlighted

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

Explorer

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
Highlighted

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

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