Splunk Search

How do I correlate time events from separate indexes?

dex31337
Loves-to-Learn Lots

I want to create alert when user approve MFA from different IP than the one he used prior to connection to VPN. So I'm gathering all successful logins from msazure index and I would like to match IP events from second index msvpn. The problem is that IPs from msvpn index (src_ip) does not occur at the same time as IPs from msazure index (properties.ipAddress). The quantity of the events in both indexes are also different. How do I find a single event IP from the second index and match it to a closest time to IP from the first one? I already tried multiple searches and none of them worked. Any help would be much appreciated. 

 

index=msazure* operationName="Sign-in activity" "properties.appliedConditionalAccessPolicies{}.enforcedGrantControls{}"=Mfa sourcetype="azure:aad:signin" "properties.authenticationDetails{}.authenticationStepResultDetail"="MFA successfully completed" properties.mfaDetail.authMethod="*" properties.ipAddress="*" properties.userPrincipalName=testuser*
| search NOT properties.networkLocationDetails{}.networkNames{} IN ("xxIPs", "yyIPs") | iplocation properties.ipAddress | rename Country as CC2
| table _time properties.userPrincipalName properties.mfaDetail.authMethod properties.ipAddress CC2
| appendcols
[search index=msvpn app="ssl:vpn" http_user_agent="xxx*" user=testuser* src_ip=*
| iplocation dvc | rename Country as CC1 | table src_ip CC1]
| search properties.ipAddress="*"

 

Labels (2)
0 Karma

diogofgm
SplunkTrust
SplunkTrust

Avoid using appendcols and transaction in search that really do not require them. Use stats

(index=msazure* operationName="Sign-in activity" "properties.appliedConditionalAccessPolicies{}.enforcedGrantControls{}"=Mfa sourcetype="azure:aad:signin" "properties.authenticationDetails{}.authenticationStepResultDetail"="MFA successfully completed" properties.mfaDetail.authMethod="*" properties.ipAddress="*" properties.userPrincipalName=testuser* NOT properties.networkLocationDetails{}.networkNames{} IN ("xxIPs", "yyIPs")) OR (index=msvpn app="ssl:vpn" http_user_agent="xxx*" user=testuser* src_ip=*)

| eval mfa_src_ip = if(index=msazure,properties.ipAddress,"")
| eval vpn_src_ip = if(index=msvpn,properties.ipAddress,"")

| stats latest(mfa_src_ip) AS mfa_src_ip latest(vpn_src_ip) AS vpn_src_ip by user

| iplocation prefix=mfa_ mfa_src_ip
| iplocation prefix=vpn_ vpn_src_ip

 

1st block - get all you're data (index=mfa) OR (index=vpn)
2nd block - eval new IPs fields base on their origin 
3rd block - use stats to get the fields you want
4th block - do the ip location for both IPs

This is a bit simplified but I think you can get the idea

------------
Hope I was able to help you. If so, some karma would be appreciated.
0 Karma

dex31337
Loves-to-Learn Lots

Thanks for reply @diogofgm . Your output shows me only users in the table. IPs and geolocation from both indexes are not displayed. 

0 Karma

diogofgm
SplunkTrust
SplunkTrust

Sorry... Splunk sometimes has issues with fields that include . 
Just rename properties.ipAddress as ipAddress and them use that in the eval if.

(index=msazure* operationName="Sign-in activity" "properties.appliedConditionalAccessPolicies{}.enforcedGrantControls{}"=Mfa sourcetype="azure:aad:signin" "properties.authenticationDetails{}.authenticationStepResultDetail"="MFA successfully completed" properties.mfaDetail.authMethod="*" properties.ipAddress="*" properties.userPrincipalName=testuser* NOT properties.networkLocationDetails{}.networkNames{} IN ("xxIPs", "yyIPs")) OR (index=msvpn app="ssl:vpn" http_user_agent="xxx*" user=testuser* src_ip=*)

| rename properties.ipAddress AS ipAddress

| eval mfa_src_ip = if(index=msazure,ipAddress,"")
| eval vpn_src_ip = if(index=msvpn,ipAddress,"")

| stats latest(mfa_src_ip) AS mfa_src_ip latest(vpn_src_ip) AS vpn_src_ip by user

| iplocation prefix=mfa_ mfa_src_ip
| iplocation prefix=vpn_ vpn_src_ip
------------
Hope I was able to help you. If so, some karma would be appreciated.
0 Karma

dex31337
Loves-to-Learn Lots

No changes after renaming the IP field... Table still only list username only.

0 Karma

diogofgm
SplunkTrust
SplunkTrust

updated the search.. it was the eval..  I replaced the "" with null()

I had to test it. Now it should work. This what I used to test it now 

| makeresults 
| eval src.ip = "1.1.1.1,1.1.2.2,1.2.3.4" 
| makemv src.ip delim=","
| mvexpand src.ip

| rename src.ip AS src_ip
| eval text = if(src_ip="1.1.1.1","mfa","vpn")
| eval user = "user"

| eval mfa = if(text="mfa",src_ip,null())
| eval vpn = if(text="vpn",src_ip,null())

| stats latest(mfa) AS mfa latest(vpn) as vpn by user

| iplocation prefix=mfa_ mfa
| iplocation prefix=vpn_ vpn

 

------------
Hope I was able to help you. If so, some karma would be appreciated.
0 Karma

dex31337
Loves-to-Learn Lots

Your search works fine when tested. However my still doesn't.  It still only display a single username row without any IPs. I would need all rows with username and IP displayed from msazure index and then match IPs from a msvpn index to the times in the first one. I see events for username also occurs at different times in both indexes...

0 Karma

venky1544
Builder

hi @dex31337 

since both the index has same ip probably coalesce should work and create a new field alias type and then probably do the transaction which might depend on the timespan you are searching for grouping the events based on timespan

index=msvpn OR index=msazure |eval newsrcip=coalesce(src_ip,properties.ipAddress) |transaction newsrcip |eval endtime=_time+duration |eval end_time=strftime(endtime,"%Y-%m-%d %H:%M:%S") |table newsrcip _time end_time

Hope this helps 

If you find the answer/suggestion helpful, an upvote/karma is appreciated

0 Karma

dex31337
Loves-to-Learn Lots

Thanks for replay @venky1544 ,  I did remove the coalesce and changed the last line to: | transaction src_ip |eval endtime=_time+duration |eval end_time=strftime(endtime,"%Y-%m-%d %H:%M:%S") |table src_ip _time end_time Username properties.ipAddress

This gives me rows with single values of src_ip from msvpn index, time, end_time and user which is great. However I would like to match those IPs to the list of IPs from msazure index. So let say I have 4 events with ip x.x.x.x (properties.ipAddress) from msazure index occured at 11.30, 11.55, 12.30, 12.45. I want to display all 4 of them in the table (together with username, host, etc). and then match src_ip from msvpn index to those events. So if I have 2 IPs in msvpn index between 11.30 and 12.45 I would like to match them closest to the rows/events from the first index. Then perform comparison etc on the data.

When I placed properties.ipAddress at the end of |table it does not display any data.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...