writing a splunk query to isolate login event

[pc1234]

im trying to write a splunk search to extract the user id and time of a login. 

log sample below:

trx# datetime                           remaining text in event

10    1/17/2025 15:03:20   account record user100 does not exist

12   1/17/2025  15:03:20   login as admin, raising privileges

both results represent a successful login. both results have the same datetime but different trx# (10 or 12)

ive tried streamstats count  by _time which generates a count for each result. the issue is how do I isolate the first result (trx# =10) so i can extract the userid (user100)? the streamstats  command doesnt always assign the same count value (1 or 2) to the two logs. 

Thanks in advance for your help.

[yuanliu]

Your question seems to be really about extracting user ID.  In other words, given the illustrated data, you want

_raw	restoftext	trx	user
10 1/17/2025 15:03:20 account record user100 does not exist	account record user100 does not exist	10	user100
12 1/17/2025 15:03:20 login as admin	login as admin	12	admin

 Is this correct?

The above can be achieve by regex.  But you have to enumerate all those login events and write alternative expressions.  For these two,

| rex "^(?<trx>\d+)\s+\S+\s+\S+\s+(?<restoftext>.+)"
| rex field=restoftext "(login as|account record) (?<user>\S+)"

Here is an emulation for you to play with and compare with real data

| makeresults format=csv data="_raw
10    1/17/2025 15:03:20   account record user100 does not exist
12   1/17/2025  15:03:20   login as admin, raising privileges"
``` data emulation above ```

Hope this helps.