topic Re: How to use field inside quoted search as a variable? in Splunk Search

How to use field inside quoted search as a variable?

Bleepie — Wed, 08 Feb 2023 11:41:17 GMT

Hi, I have the following joined Splunk query:

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?

Re: How to use field inside quoted search as a variable?

ITWhisperer — Wed, 08 Feb 2023 12:13:38 GMT

Does this work for you?

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

Re: How to use field inside quoted search as a variable?

Bleepie — Wed, 08 Feb 2023 12:41:53 GMT

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.

Re: How to use field inside quoted search as a variable?

Bleepie — Wed, 08 Feb 2023 12:55:43 GMT

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.

Re: How to use field inside quoted search as a variable?

ITWhisperer — Wed, 08 Feb 2023 13:09:14 GMT

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

Re: How to use field inside quoted search as a variable?

Bleepie — Wed, 08 Feb 2023 13:30:17 GMT

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.

Re: How to use field inside quoted search as a variable?

Bleepie — Wed, 08 Feb 2023 13:31:09 GMT

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

Re: How to use field inside quoted search as a variable?

Bleepie — Wed, 08 Feb 2023 14:35:53 GMT

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.