topic Re: Combine data from multiple sourcetypes in Security

Combine data from multiple sourcetypes

olaskallberg — Thu, 14 Mar 2024 12:37:05 GMT

I have two sourcetypes containing login information and user information

Sourcetype1: Login information (useful paramaters: UserId, status)

Sourcetype1: Id = accountId

Sourcetype2: User information (useful parameters: username. Id)

Sourcetype2; Id = userId

Both sourcetypes contains the parameter Id but refers to different information.

I want to get a list/table with number of logins and the result for each user

Mapping login data with user data: UserId (Sourcetype1) = Id (Sourcetype2)

Example:

username status count

aa@aa.aa success 3

Re: Combine data from multiple sourcetypes

ITWhisperer — Thu, 14 Mar 2024 13:14:41 GMT

Please provide sample (anonymised) events for your two sourcetypes, preferably in a code block </>

Re: Combine data from multiple sourcetypes

olaskallberg — Thu, 14 Mar 2024 13:22:47 GMT

sourcetype1:

{ [-]
ApiType: xxxxx
ApiVersion: xxxxx
Application: xxxxx
Browser: xxxxx
ClientVersion: xxxxx
Id: xxxxx
LoginGeo: {[+]
}
LoginGeoId: xxxxx
LoginTime: xxxx-xx-xx xx:xx:xx
LoginType: xxxxx
LoginUrl: xxxxx
Platform: xxxxx
SourceIp: xxxxx
Status: xxxxx
UserAccountId: xxxxx
UserId: xxxxx
attributes: { [+]
}
}

sourcetype2:

{ [-]
City: xxxxx
CompanyName: xxxxx
Country: xxxxx
Email: xxxxx
FirstName: xxxxx
Id: xxxxx
IsActive: xxxxx
LastLoginDate: xxxx-xx-xx xx:xx:xx
LastModifiedDate: xxxx-xx-xx xx:xx:xx
LastName: xxxxx
Latitude: xxxxx
Longitude: xxxxx
MobilePhone: xxxxx
Name: xxxxx
PostalCode: xxxxx
Profile: { [+]
}
ProfileId: xxxxx
State: xxxxx
UserAccountId: xxxxx
UserRoleId: xxxxx
UserType: xxxxx
Username: xxxxx
attributes: { [+]
}
}

Re: Combine data from multiple sourcetypes

ITWhisperer — Thu, 14 Mar 2024 13:32:35 GMT

Assuming these fields have already been extracted, try something like this

| eval commonId = if(sourcetype = "sourcetype1", UserId, id) | stats values(Status) as Status values(Username) as Username by commonId

Re: Combine data from multiple sourcetypes

olaskallberg — Thu, 14 Mar 2024 13:37:19 GMT

Returns empty username field

commonId Status Username
xxxxxxxxx Success

Re: Combine data from multiple sourcetypes

ITWhisperer — Thu, 14 Mar 2024 13:45:12 GMT

Please share the raw source (not formatted) version of your events

Re: Combine data from multiple sourcetypes

olaskallberg — Thu, 14 Mar 2024 14:24:11 GMT

{"attributes": {"type": "User", "url": "xxxxx"}, "LastModifiedDate": "xxxxx", "City": xxxxx, "Country": xxxxx, "FirstName": "xxxxx", "Id": "xxxxx", "IsActive": xxxxx, "LastLoginDate": "xxxxx", "LastName": "xxxxx", "Latitude": xxxxx, "Longitude": xxxxx, "MobilePhone": xxxxx, "Name": "xxxxx", "PostalCode": xxxxx, "State": xxxxx, "Username": "xxxxx", "UserRoleId": xxxxx, "UserType": "xxxxx", "Email": "xxxxx", "CompanyName": xxxxx, "ProfileId": "xxxxx", "Profile": {"attributes": {"type": "Profile", "url": "xxxxx"}, "PermissionsApiEnabled": xxxxx, "PermissionsModifyAllData": xxxxx, "PermissionsViewSetup": xxxxx}, "UserAccountId": "xxxxx"}

{"attributes": {"type": "LoginHistory", "url": "xxxxx"}, "ApiType": xxxxx, "ApiVersion": "xxxxx", "Application": "xxxxx", "Browser": "xxxxx", "ClientVersion": "", "Id": "xxxxx", "LoginTime": "xxxxx", "LoginType": "xxxxx", "LoginUrl": "xxxxx", "LoginGeoId": "xxxxx", "xxxxx": {"attributes": {"type": "xxxxx", "url": "xxxxx"}, "City": "xxxxx", "Latitude": xxxxx, "Longitude": xxxxx}, "Platform": "xxxxx", "SourceIp": "xxx.xxx.xxx.xxx", "Status": "xxxxx", "UserId": "xxxxx", "UserAccountId": "xxxxx"}

Re: Combine data from multiple sourcetypes

ITWhisperer — Thu, 14 Mar 2024 14:04:45 GMT

This is not valid JSON - please supply event in valid format

Re: Combine data from multiple sourcetypes

ITWhisperer — Thu, 14 Mar 2024 14:15:28 GMT

OK I was missing some capitalisation

| makeresults format=json data="[{\"attributes\": {\"type\": \"LoginHistory\", \"url\": \"xxxxx\"}, \"ApiType\": \"xxxxx\", \"ApiVersion\": \"xxxxx\", \"Application\": \"xxxxx\", \"Browser\": \"xxxxx\", \"ClientVersion\": \"\", \"Id\": \"xxxxx\", \"LoginTime\": \"xxxxx\", \"LoginType\": \"xxxxx\", \"LoginUrl\": \"xxxxx\", \"LoginGeoId\": \"xxxxx\", \"xxxxx\": {\"attributes\": {\"type\": \"xxxxx\", \"url\": \"xxxxx\"}, \"City\": \"xxxxx\", \"Latitude\": \"xxxxx\", \"Longitude\": \"xxxxx\"}, \"Platform\": \"xxxxx\", \"SourceIp\": \"xxx.xxx.xxx.xxx\", \"Status\": \"xxxxx\", \"UserId\": \"xxxxx\", \"UserAccountId\": \"xxxxx\"},{\"attributes\": {\"type\": \"User\", \"url\": \"xxxxx\"}, \"LastModifiedDate\": \"xxxxx\", \"City\": \"xxxxx\", \"Country\": \"xxxxx\", \"FirstName\": \"xxxxx\", \"Id\": \"xxxxx\", \"IsActive\": \"xxxxx\", \"LastLoginDate\": \"xxxxx\", \"LastName\": \"xxxxx\", \"Latitude\": \"xxxxx\", \"Longitude\": \"xxxxx\", \"MobilePhone\": \"xxxxx\", \"Name\": \"xxxxx\", \"PostalCode\": \"xxxxx\", \"State\": \"xxxxx\", \"Username\": \"xxxxx\", \"UserRoleId\": \"xxxxx\", \"UserType\": \"xxxxx\", \"Email\": \"xxxxx\", \"CompanyName\": \"xxxxx\", \"ProfileId\": \"xxxxx\", \"Profile\": {\"attributes\": {\"type\": \"Profile\", \"url\": \"xxxxx\"}, \"PermissionsApiEnabled\": \"xxxxx\", \"PermissionsModifyAllData\": \"xxxxx\", \"PermissionsViewSetup\": \"xxxxx\"}, \"UserAccountId\": \"xxxxx\"}]" | streamstats count as sourcetype | eval sourcetype="sourcetype".sourcetype | eval commonId = if(sourcetype = "sourcetype1", UserId, Id) | stats values(Status) as Status values(Username) as Username by commonId

Re: Combine data from multiple sourcetypes

olaskallberg — Thu, 14 Mar 2024 14:24:25 GMT

Now the Status field is missing.

commonId Status Username

xxxxxx xxxxxxxxxx

Re: Combine data from multiple sourcetypes

ITWhisperer — Thu, 14 Mar 2024 15:00:59 GMT

The runanywhere example I shared shows it working. However, this is based on the events that you shared, so if it isn't working for your real data, there is likely to be some discrepancy between your real data and the sample events that you shared. This is why it is important to share accurate representative examples of your data. Check your actual field names and event structure and modify the search accordingly.

Re: Combine data from multiple sourcetypes

olaskallberg — Fri, 15 Mar 2024 07:14:31 GMT

Also I get no count value.

I need number of logins per user and the status of each login

E.g.

Username, Status Logins

xx@xx.xx success 5

failed 2

yy@yy.yy success 2

failed 4

Re: Combine data from multiple sourcetypes

ITWhisperer — Fri, 15 Mar 2024 08:19:14 GMT

Please share the search which is giving you these results

Re: Combine data from multiple sourcetypes

olaskallberg — Fri, 15 Mar 2024 10:35:36 GMT

I do not know how to type a search to get the output that I stated.

That is what I'm looking for a way to present the information that way.

Re: Combine data from multiple sourcetypes

olaskallberg — Fri, 15 Mar 2024 10:44:59 GMT

The problem is that I need to count the sourcetype1 events and get the status.

Combine this with the Username from sourcetype2.

Either I get correct count and Status but no username or I get username but wrong count and status

Re: Combine data from multiple sourcetypes

ITWhisperer — Fri, 15 Mar 2024 10:58:35 GMT

I have shown you how to do this, with a runanywhere example included. If this isn't working for you, you need to provide some example events (in raw source format) where it is not working, because what you have provided so far has been shown to work.

Re: Combine data from multiple sourcetypes

olaskallberg — Fri, 15 Mar 2024 12:11:06 GMT

The raw data that I have provided is what the two log events look like.

But when I run your search I do not get all data

This is what the result looks like

Re: Combine data from multiple sourcetypes

ITWhisperer — Fri, 15 Mar 2024 12:23:43 GMT

While the commonId fields look like they might match, they obviously don't. This could be due to "invisible" white spaces. Try trimming the commonId field before the stats command

Re: Combine data from multiple sourcetypes

olaskallberg — Fri, 15 Mar 2024 12:42:08 GMT

trim did not make any difference.

Re: Combine data from multiple sourcetypes

ITWhisperer — Fri, 15 Mar 2024 13:08:30 GMT