topic Re: rex command by comparing two different indexes in Splunk Search

How to write rex command by comparing two different indexes?

RanjiRaje — Tue, 06 Jun 2023 14:36:19 GMT

Hi all, I need your help in validating my query. Please help..

in indexA , fields are: user, login
(user=firstname, login=login_id)
in indexB , fields are: userName, city
(city: location of the employee, userName:firstname comma lastname)

I have userName in indexA but it was not extracted under any field name. So I am extracting this field and based on that userName combination, I need to get location of that employee.
I am trying with the below query, but it is not giving location detail. Location is emplty for all rows

(index=indexA sourcetype="A" user=*) OR (index=indexB sourcetype="B" userName=*) | rex field=_raw "user=(?<userName>[^.]*)\s+cat" | fields userName city login | stats count as events values(city) as city by userName login

eg:user=aaa, login=aabb
city=xyz, userName=aaa, bbb

with my query I have to get result as

userName	login	events	city
aaa, bbb	aabb	1	xyz

But Iam getting empty in city. please help.. Thanks

Re: rex command by comparing two different indexes

gcusello — Tue, 06 Jun 2023 14:36:40 GMT

Hi @RanjiRaje ,

please try this regex:

| rex field=userName "^(?<user>[^,]*)"

that you can test at https://regex101.com/r/NcAZbu/1

so you could try something like this:

(index=indexA sourcetype="A" user=*) OR (index=indexB sourcetype="B" userName=*) | rex field=userName "^(?<user>[^,]*)" | stats count AS events values(city) AS city values(login) AS login BY user

Ciao.

Giuseppe

Re: rex command by comparing two different indexes

RanjiRaje — Tue, 06 Jun 2023 15:08:52 GMT

hi sir, thanks for your reply.

I need to extract the field userName from indexA, which starts with user (i.e. firstname)

so, i have to extract from _raw.

userName field is already available in indexB and giving the same name for newly extracted field

Also, i tried using rex command separately and the result is as expected

index=indexA sourcetype="A" user=*
| rex field=_raw "user=(?<userName>[^.]*)\s+cat"

Re: How to write rex command by comparing two different indexes?

yuanliu — Tue, 06 Jun 2023 17:18:42 GMT

Either the problem is ill-defined or there's some key characteristics missing from your description.

in indexA , fields are: user, login
(user=firstname, login=login_id)
in indexB , fields are: userName, city
(city: location of the employee, userName:firstname comma lastname)

I have userName in indexA but it was not extracted under any field name. So I am extracting this field and based on that userName combination, I need to get location of that employee.
...

| rex field=_raw "user=(?<userName>[^.]*)\s+cat"

You first stated that a field "user" exists in indexA that only gives you only first name. Obviously the field "user" cannot be used to match userName in indexB. Then, in the rex, you are trying to extract a full name that is in the same format as being used in indexB, namely "userName:firstname comma lastname", by looking after prefix "user=" for any string that doe not contain a dot (".") and preceding a pattern matching any blank followed by string "cat". If this rex would extract a pattern like "userName:firstname comma lastname", I don't see why Splunk would not have already populated field "user" with the same pattern. Can you illustrate raw data to see what is being extracted by that rex in both indexA and indexB? (But especially in indexA.) You can use this as an alternative test so we don't risk overriding existing value in indexB

| rex field=_raw "user=(?<AuserName>[^.]*)\s+cat"

Also, can you confirm that "city" is fully populated in indexB?

Re: rex command by comparing two different indexes

gcusello — Wed, 07 Jun 2023 07:17:28 GMT

Hi @RanjiRaje,

I cannot test your regex because you didn't share your raw events.

Anyway, you said that in indexA you have user=firstname and in indexB you have userName=firstname,login.

You already have the user field from indexA and using my regex you can extract the firstname from the userName field of the indexB and can you use it for matching with indexA.

Isn't this your requirement?

if you want, you can also rename user as userName at the end of the search.

I could be more detailed, if you could share a sample of your raw logs from indexA and indexB.

Ciao.

Giuseppe