How to use field inside quoted search as a variable?

[Bleepie]

Hi, I have the following joined Splunk query:

index="myIndex" 
source="mySource1"
| fields _time, _raw
| rex "Naam van gebruiker: (?<USER>.+) -"
| dedup USER
| table USER
| sort USER
| join type=left 
[ search
    index="myIndex"
    source="mySource2"
    "User:myUserID The user is authenticated and logged in." 
    | stats latest(_raw)
]

The results look like this:

Green is myUserID. Red is some other persons user ID. Because I am using my hardcoded user ID, every person gets the "latest(_raw)" record corresponding to my user id. I want each user to get their own event. I believe this can be done if I use the USER field in the second search, but I don't know the syntax to get it to work. I tried:

"User:'USER' The user is authenticated and logged in."

And also

"User:\USER\ The user is authenticated and logged in."

But these don't work. What is the correct syntax?

[ITWhisperer]

Does this work for you?

index="myIndex" source="mySource2"
    "User:myUserID The user is authenticated and logged in." 
    [search index="myIndex" 
    source="mySource1"
    | rex "Naam van gebruiker: (?<USER>.+) -"
    | dedup USER
    | table USER
    | sort USER
    | format] 
| stats latest(_raw) by USER

Basically, use the search on mySource1 to find a list of USERs which you use to filter mySource2

[Bleepie]

Hi, this does not return any results. If I run the outher query and inner query both seperately, they work. But together Splunk replies "No results found. Try expanding the time range."

Also, in this query you still use a hardcoded user id.

[Bleepie]

Maybe I need to clarify a bit:

Source 1 :

2023-02-01 17:00:01 - Naam van gebruiker: hank - Rol van gebruiker: operator

2023-02-02 17:00:01 - Naam van gebruiker: skylar - Rol van gebruiker: operator

2023-02-03 17:00:01 - Naam van gebruiker: walt - Rol van gebruiker: operator

Source 2 :

2023-02-06 13:49:57,654 User:hank The user is authenticated and logged in.

2023-02-07 13:49:57,654 User:skylar The user is authenticated and logged in.

2023-02-08 13:49:57,654 User:walt The user is authenticated and logged in.

2023-02-03 13:49:57,654 User:hank The user is authenticated and logged in.

2023-02-02 13:49:57,654 User:skylar The user is authenticated and logged in.

2023-02-01 13:49:57,654 User:walt The user is authenticated and logged in.

I need a table. In that table I need a field called USER. USER is the name fetched from source 1. I also need a field called LATEST. In that field I want the entire row from source 2, but only the latest. So for example, I want the following output:

USER, LATEST
hank, 2023-02-03 13:49:57,654 User:hank The user is authenticated and logged in.
skylar, 2023-02-02 13:49:57,654 User:skylar The user is authenticated and logged in.
walt, 2023-02-01 13:49:57,654 User:walt The user is authenticated and logged in.

[ITWhisperer]

Have you already extracted USER from source2, or do you need to add a rex to do that too? (Your original post didn't include it, so I assumed it was already extracted.) Try something like this

index="myIndex" source="mySource2"
    "The user is authenticated and logged in."
| rex "User:(?<USER>\w+) The user is authenticated and logged in."
| search [search index="myIndex" 
    source="mySource1"
    | rex "Naam van gebruiker: (?<USER>.+) -"
    | dedup USER
    | table USER
    | sort USER
    | format] 
| stats latest(_raw) by USER

[Bleepie]

Not sure if I replied directly under you, check my response a few seconds ago.

[Bleepie]

Yes, both sources need the user extracted. I tried to ran your latest query but no results show up.

I tried running it piece by piece:

index="myIndex"  source="mySource2"
    "The user is authenticated and logged in."
| rex "User:(?<USER>\w+) The user is authenticated and logged in."
| table USER
| dedup USER

This will give me a list of users found within source 2. If I don't use the table and dedup I get all events instead.

If I run the other query:

index="myIndex" 
    source="mySource1"
    | rex "Naam van gebruiker: (?<USER>.+) -"
    | dedup USER
    | table USER
    | sort USER

It returns a list of users found in the first source. Again, if I don't use dedup and table I get all the events.

[Bleepie]

I have been playing and managed to get both sources like so:

index="myIndex"  source="mySource2"
    "The user is authenticated and logged in."
| rex "User:(?<USERLEFT>\w+) The user is authenticated and logged in."
| dedup USERLEFT
| append
 [search index="myIndex" 
    source="mySource2"
    | rex "Naam van gebruiker: (?<USERRIGHT>.+) -"
    | dedup USERRIGHT
]

I can display both users from either source. The only thing I am missing now is how to build and compare both sources to get what I need.