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?
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
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.
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.
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
Not sure if I replied directly under you, check my response a few seconds ago.
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.
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.