Combine data from multiple sourcetypes

[olaskallberg]

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

[olaskallberg]

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: { [+]
}
}

[ITWhisperer]

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

[olaskallberg]

Returns empty username field

commonId Status Username
xxxxxxxxx Success

[ITWhisperer]

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

[olaskallberg]

{"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"}

[ITWhisperer]

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

[ITWhisperer]

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

[olaskallberg]

Now the Status field is missing.

commonId Status Username

xxxxxx                        xxxxxxxxxx

[ITWhisperer]

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.

[olaskallberg]

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

[ITWhisperer]

Please share the search which is giving you these results

[olaskallberg]

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.

[olaskallberg]

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

[ITWhisperer]

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.

[olaskallberg]

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

[ITWhisperer]

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

[olaskallberg]

trim did not make any difference.

[ITWhisperer]

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.

[ITWhisperer]

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