Join fields of different events

[radekpitr]

Hello All,

I would need help to join two efferent events together and create one table with all information from both.
First even as user access, so time; user; IP address is wanted
Second event is creation of user account by Spore.

So basically I need to see each access and additionally information in this event from second one. They do have one same common field but its not unique as after certain time reused. I have tried join but Join and subserach matching first event from historical point of view and not closes one to main search. I have seek for answer bu was not successfull

search string:
index=cisco_logs UserType=GuestUser
| rename User_Name as GuestUserName
| rename MESSAGE_TEXT as Details
| join type=left GuestUserName
[search index=cisco_logs MESSAGE_CLASS=guest]
| eval "Sponsor details"="User: "+SponsorUser+","+"Email: "+EmailAddress+","+"Address: "+IpAddress
| eval "Guest details"="Company: "+GuestCompanyname+","+"Email: "+GuestEmailAddress+","+"Name: "+GuestFirstname+","+"Surname: "+GuestLastname+","+"Phone: "+GuestPhoneNumber+","+"From: "+GuestFromDate+","+"To: "+GuestToDate
| makemv delim="," "Sponsor details"
| makemv delim="," "Guest details"
| rename GuestUserName as "Guest account"
| rename src_mac as "Guest MAC Address"
| rename Address as "Guest IP Address"
| rename EndPointMatchedProfile as "Endpoint Profile"
| table _time "Endpoint Profile" "Guest MAC Address" "Guest IP Address" "Guest account" "Sponsor details" "Guest details" Details

Appreciate any help as spend a lot time already.

[DalJeanis]

The best way to do something like this is usually

 (index=cisco_logs UserType=GuestUser) OR (index=cisco_logs MESSAGE_CLASS=guest)

 | rename COMMENT as "list the fields you need to have from either source"
 | fields User_Name  UserType MESSAGE_CLASS MESSAGE_TEXT 
    src_mac Address
    SponsorUser EmailAddress IpAddress 
    GuestCompanyName GuestEmailAddress 
    GuestFirstname GuestLastname GuestPhoneNumber GuestFromDate GuestToDate
    EndPointMatchedProfile

 | rename COMMENT as "set the match field based on which record it is"
 | eval GuestUserName=coalesce(GuestUserName,User_Name)

 | rename COMMENT as "roll them together by the match field"
 | stats values(*) as * by GuestUserName

The above is the code for if there is only going to be unique data on one record of each type for each GuestUserName. That seems unlikely to be true for this use case, so we need to do a slightly different procedure. We need to roll the data forward from one type of record to the other type of record, using a streamstats command.

Let's suppose that for every record of UserType=GuestUser, you want the details from the most recent previous record of type MESSAGE_CLASS=guest. Here's one way to do that...

 | rename COMMENT as "set the match field based on which record it is"
 | eval GuestUserName=coalesce(GuestUserName,User_Name)
 | eval rectype=if(UserType=GuestUser,1,2)

 | rename COMMENT as "if it is possible that the MESSAGE_CLASS=guest record will not be there first"
 | rename COMMENT as "then modify its _time value by the expected delay, here before the sort"

 | rename COMMENT as "roll data forward, then drop the records that aren't needed "
 | sort 0 _time 
 | streamstats last(*) as * by GuestUserName
 | where rectype=1

If you want to change which direction the data gets copied, then just reverse the eval rectype= statement.

[radekpitr]

Hi, I have used your suggestion but was not able to get it work as seems to me streamstats not enrich logs. Can you review it for me please?

index=cisco_logs source="tcp:5519" sourcetype="cisco:ise:syslog" (UserType=GuestUser OR MESSAGE_CLASS=guest)
| fields UserName src_mac SponsorUser GuestUserName UserType
| eval GuestUserName=coalesce(GuestUserName,UserName)
| eval RECTYPE=if(UserType="GuestUser",1,2)
| streamstats last(*) as * by GuestUserName
| where RECTYPE=1

[radekpitr]

I guess I get it work, at least seems to. I have avoid fields contained in both logs. So used fields specific of log type.

index=cisco_logs  source="tcp:5519" sourcetype="cisco:ise:syslog" (UserType=GuestUser OR (MESSAGE_CLASS="guest" AND GuestStatus!=EXPIRED)) 
| fields OriginalUserName src_mac src_ip GuestUserName UserType GuestFromDate GuestToDate MESSAGE_TEXT SponsorUser EmailAddress IpAddress GuestCompanyname GuestEmailAddress GuestFirstname GuestLastname GuestPhoneNumber GuestFromDate GuestToDate GuestStatus SSID EndPointMatchedProfile
| eval GuestUserName=coalesce(GuestUserName,OriginalUserName) 
| eval rectype=if(UserType="GuestUser",1,2) 
| sort 0 _time 
| streamstats last(*) as * by GuestUserName 
| where rectype=1
| sort - _time

[radekpitr]

Hi, thank you for suggestions but was not able to get it work. I can not "see" anything coming from stremstats

Here is event what would need to be seen and enrich of details from second log:
(<181>May 11 08:42:36 dced8103 CISE_Passed_Authentications 0012489025 1 0 2018-05-11 08:42:36.095 +00:00 0389728333 5200 NOTICE Passed-Authentication: Authentication succeeded, ConfigVersionId=122, Device IP Address=10.1.1.10, DestinationIPAddress=10.2.2.10, DestinationPort=1812, UserName=email@email.com, Protocol=Radius, RequestLatency=15, NetworkDeviceName=name1111, User-Name=email@email.com, NAS-IP-Address=10.2.2.10, NAS-Port=1, Service-Type=Login, Framed-IP-Address=10.2.2.11, Called-Station-ID=00-a2-ee-3a-a5-e0:XXX, Calling-Station-ID=3c-95-09-70-3c-65, NAS-Identifier=name1111, Acct-Session-Id=5af556d1/3c:95:09:70:3c:65/2627630, NAS-Port-Type=Wireless - IEEE 802.11, cisco-av-pair=audit-session-id=0a050284001424df5af556d1, Airespace-Wlan-Id=10, OriginalUserName=email@email.com, NetworkDeviceProfileName=Cisco, NetworkDeviceProfileId=b0699505-3150-4215-a80e-6753d45bf56c, IsThirdPartyDeviceFlow=false, RadiusFlowType=5, SSID=00-a2-ee-3a-a5-e0:XXX, AcsSessionID=dced8103/309714860/5273274, AuthenticationIdentityStore=Guest Users, AuthenticationMethod=PAP_ASCII, SelectedAccessService=YYY, SelectedAuthorizationProfiles=PermitAccess, IdentityGroup=User Identity Groups:ZZZ, IdentityGroup=Endpoint Identity Groups:Unknown, Step=11001, Step=11017, Step=15049, Step=15008, Step=15048, Step=15048, Step=15041, Step=15013, Step=24631, Step=24632, Step=22037, Step=24715, Step=15036, Step=15048, Step=15016, Step=22081, Step=22080, Step=11002, SelectedAuthenticationIdentityStores=Guest Users, AuthenticationStatus=AuthenticationPassed, NetworkDeviceGroups=Location#XXX, NetworkDeviceGroups=Device Type#All Device Types#WLC, NetworkDeviceGroups=IPSEC#Is IPSEC Device#No, IdentityPolicyMatchedRule=Default, AuthorizationPolicyMatchedRule=CCC, UserType=GuestUser, CPMSessionID=0a050284001424df5af556d1, EndPointMACAddress=3C-95-09-70-3C-65, PostureAssessmentStatus=NotApplicable, EndPointMatchedProfile=Unknown, ISEPolicySetName=VVV, IdentitySelectionMatchedRule=Default, StepData=4= Normalised Radius.RadiusFlowType, StepData=5= Radius.Called-Station-ID, StepData=7=Guest Users, StepData=13= Network Access.AuthenticationStatus, allowEasyWiredSession=false, DTLSSupport=Unknown, HostIdentityGroup=Endpoint Identity Groups:Unknown, Network Device Profile=Cisco, Location=Location#XXX, Device Type=Device Type#All Device Types#WLC, IPSEC=IPSEC#Is IPSEC Device#No, Response={State=ReauthSession:0a050284001424df5af556d1; Class=CACS:0a050284001424df5af556d1:dced8103/309714860/5273274; Session-Timeout=47783; Termination-Action=Default; cisco-av-pair=profile-name=Unknown; LicenseTypes=1; },)

2nd log from where details are needed (like sponsor user-id and email):
(<182>May 11 08:28:29 dced8203 CISE_Guest 0008327646 1 0 2018-05-11 08:28:29.059 +00:00 0261225873 86006 INFO Guest: Guest user account is created, ConfigVersionId=114, UserType=NON_GUEST, UserName=SPONSORE-USER, EmailAddress=sponsre@email.com, IpAddress=10.3.3.10, AuthenticationIdentityStore=domain.net, PortalName=NAME, IdentityGroup=S-1-5-21-160562036-3150058255-2134394716-594253, SponsorUser=SPONSORE-USER, PsnHostName=host.domain.net, GuestUserName=email@email.com, GuestFirstname=NAME, GuestLastname=LAST, GuestEmailAddress=email@email.com, GuestPhoneNumber=123456789, GuestCompanyname=COMP_NAME, GuestAuthenticationIdentityStore=Guest Users, GuestType=NAME, GuestValidDays=1, GuestFromDate=05/11/2018 08:25, GuestToDate=05/11/2018 21:59, GuestLocation=UTC+01:00 (Europe/Berlin), GuestStatus=ACTIVE,)

Serach string:
index=cisco_logs sourcetype="cisco:ise:syslog" (UserType=GuestUser OR MESSAGE_CLASS=guest)
| fields UserName src_mac SponsorUser GuestUserName UserType
| eval GuestUserName=coalesce(GuestUserName,UserName)
| eval RECTYPE=if(UserType="GuestUser",1,2)
| streamstats last(*) as * by GuestUserName
| where RECTYPE=1

THX!

[Richfez]

Could you provide a sample of the two eventtypes, and also a bit more detail about how it reuses records?

[radekpitr]

Hi,

First log has to be seen in table and enriched by some details from second log

Access log:
<181>May 11 08:42:36 dced8103 CISE_Passed_Authentications 0012489025 1 0 2018-05-11 08:42:36.095 +00:00 0389728333 5200 NOTICE Passed-Authentication: Authentication succeeded, ConfigVersionId=122, Device IP Address=10.1.1.10, DestinationIPAddress=10.2.2.10, DestinationPort=1812, UserName=email@email.com, Protocol=Radius, RequestLatency=15, NetworkDeviceName=name1111, User-Name=email@email.com, NAS-IP-Address=10.2.2.10, NAS-Port=1, Service-Type=Login, Framed-IP-Address=10.2.2.11, Called-Station-ID=00-a2-ee-3a-a5-e0:XXX, Calling-Station-ID=3c-95-09-70-3c-65, NAS-Identifier=name1111, Acct-Session-Id=5af556d1/3c:95:09:70:3c:65/2627630, NAS-Port-Type=Wireless - IEEE 802.11, cisco-av-pair=audit-session-id=0a050284001424df5af556d1, Airespace-Wlan-Id=10, OriginalUserName=email@email.com, NetworkDeviceProfileName=Cisco, NetworkDeviceProfileId=b0699505-3150-4215-a80e-6753d45bf56c, IsThirdPartyDeviceFlow=false, RadiusFlowType=5, SSID=00-a2-ee-3a-a5-e0:XXX, AcsSessionID=dced8103/309714860/5273274, AuthenticationIdentityStore=Guest Users, AuthenticationMethod=PAP_ASCII, SelectedAccessService=YYY, SelectedAuthorizationProfiles=PermitAccess, IdentityGroup=User Identity Groups:ZZZ, IdentityGroup=Endpoint Identity Groups:Unknown, Step=11001, Step=11017, Step=15049, Step=15008, Step=15048, Step=15048, Step=15041, Step=15013, Step=24631, Step=24632, Step=22037, Step=24715, Step=15036, Step=15048, Step=15016, Step=22081, Step=22080, Step=11002, SelectedAuthenticationIdentityStores=Guest Users, AuthenticationStatus=AuthenticationPassed, NetworkDeviceGroups=Location#XXX, NetworkDeviceGroups=Device Type#All Device Types#WLC, NetworkDeviceGroups=IPSEC#Is IPSEC Device#No, IdentityPolicyMatchedRule=Default, AuthorizationPolicyMatchedRule=CCC, UserType=GuestUser, CPMSessionID=0a050284001424df5af556d1, EndPointMACAddress=3C-95-09-70-3C-65, PostureAssessmentStatus=NotApplicable, EndPointMatchedProfile=Unknown, ISEPolicySetName=VVV, IdentitySelectionMatchedRule=Default, StepData=4= Normalised Radius.RadiusFlowType, StepData=5= Radius.Called-Station-ID, StepData=7=Guest Users, StepData=13= Network Access.AuthenticationStatus, allowEasyWiredSession=false, DTLSSupport=Unknown, HostIdentityGroup=Endpoint Identity Groups:Unknown, Network Device Profile=Cisco, Location=Location#XXX, Device Type=Device Type#All Device Types#WLC, IPSEC=IPSEC#Is IPSEC Device#No, Response={State=ReauthSession:0a050284001424df5af556d1; Class=CACS:0a050284001424df5af556d1:dced8103/309714860/5273274; Session-Timeout=47783; Termination-Action=Default; cisco-av-pair=profile-name=Unknown; LicenseTypes=1; },

Sponsor log:
<182>May 11 08:28:29 dced8203 CISE_Guest 0008327646 1 0 2018-05-11 08:28:29.059 +00:00 0261225873 86006 INFO Guest: Guest user account is created, ConfigVersionId=114, UserType=NON_GUEST, UserName=USER-sponsore, EmailAddress=sponsre@email.com, IpAddress=10.3.3.10, AuthenticationIdentityStore=domain.net, PortalName=NAME, IdentityGroup=S-1-5-21-160562036-3150058255-2134394716-594253, SponsorUser=USER-sponsore, PsnHostName=host.domain.net, GuestUserName=email@email.com, GuestFirstname=NAME, GuestLastname=LAST, GuestEmailAddress=email@email.com, GuestPhoneNumber=123456789, GuestCompanyname=COMP_NAME, GuestAuthenticationIdentityStore=Guest Users, GuestType=NAME, GuestValidDays=1, GuestFromDate=05/11/2018 08:25, GuestToDate=05/11/2018 21:59, GuestLocation=UTC+01:00 (Europe/Berlin), GuestStatus=ACTIVE,

Thank you for any suggestion.