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?
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
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?
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
@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.
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
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?
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).
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.
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.
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.
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?
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.
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
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.