Hi @NorthropGrumman There are a number of ways you could tackle this, but generally using join is low down the list of options as it can be resource intensive. Instead you could look to bring back ...
See more...
Hi @NorthropGrumman There are a number of ways you could tackle this, but generally using join is low down the list of options as it can be resource intensive. Instead you could look to bring back all the data you need and then use stats on it. You could do this with something like index=network (etc) OR index=active_directory (etc) OR index=etc.etc.. or..you could use append, which would add the additional data to the results before then using stats to bring it all together, try the following as a starting point, it might need a little refinement as I dont have your data to test against. index=*proxy* SOCKS earliest=-90d@d latest=now
| eval destination=coalesce(dest, dest_port), userid=coalesce(user, username), type="proxy"
| rex field=url mode=sed "s/^SOCKS:\/\/|:\d+$//g"
| eval network=case(match(src_ip,"<Redacted>"),"user",1=1,"server"), Proxy_day = strftime(_time, "%d-%m-%y")
| append
[ search index=windows_events EventID=4624 NOT src_ip="-" NOT user="*$" earliest=-90d@d latest=now
| stats count by IpAddress, user
| rename IpAddress as src_ip, user as win_userid
| eval type="windows", userid=win_userid ]
| append
[ search index="active_directory" earliest=-90d@d latest=now
| stats count by username, fullname, title, division, mail
| rename username as userid
| eval type="ad" ]
| stats
values(mail) as mail, values(fullname) as fullname, values(title) as title, values(division) as division, values(userid) as userid, values(win_userid) as win_userid,
values(destination) as destination, values(network) as network, values(Proxy_day) as Proxy_day, values(url) as url, dc(url) as url_count
by src_ip, type
| where type="proxy"
| eval userid=coalesce(userid, win_userid)
| stats values(mail) as "Email Address", values(userid) as "User ID", values(destination) as Destination, values(network) as Network, dc(Proxy_day) as Day_Count, dc(url) as URL_Count by src_ip, url, Proxy_day
| stats values("Email Address") as "Email Address", values("User ID") as "User ID", values(Destination) as Destination, values(Network) as Network, values(Day_Count) as Day_Count, dc(Proxy_day) as Total_Days, values(URL_Count) as URL_Count by src_ip, url Did this answer help you? If so, please consider: Adding karma to show it was useful Marking it as the solution if it resolved your issue Commenting if you need any clarification Your feedback encourages the volunteers in this community to continue contributing