topic Re: Extract value from first index and search in second index in Splunk Search

Extract value from first index and search in second index

ank15july96 — Tue, 02 Feb 2021 19:17:37 GMT

Hi, I'm new to splunk so pardon if its a straightforward query

I want to extract userIds from my first index and check how many does not exist in second index

Example: index=auth-app would have field like UID: H0XF7PQU1

So, I want to extract H0XF7PQU1 from first query and check if it exist in second query (index=main-app) and get count of ids that exist one first index but not in second.

Conceptually, I want to get count of users that passed authentication (first index) but still did not make it to main application (second index)

Re: Extract value from first index and search in second index

inventsekar — Tue, 02 Feb 2021 20:06:36 GMT

Hi.. do you know if UID is extracted?

pls try

(index=auth-app OR index=main-app) UID

or, simply please try..

(index=auth-app OR index=main-app) H0XF7PQU1

update us your results, thanks.

Re: Extract value from first index and search in second index

ank15july96 — Tue, 02 Feb 2021 20:28:42 GMT

Its a bit trickier than that
Here's my first query

index=auth-app "createSession" | rex field=_raw "UID: (?<uid>.*)"

And second query should be something like index=main-app uid | stats count

How do i put above two into what you suggested -- (index=auth-app OR index=main-app) UID | stats count

Re: Extract value from first index and search in second index

ank15july96 — Tue, 02 Feb 2021 20:50:31 GMT

Okay, I tried this and this sorta works but one issue
Query:

index=main-app [search index=auth-app "createsession" | rex field=_raw "UID: (?<uid>......)" | table uid ] | stats count

The subquery results in something like this UID="XYZ" OR UID="ABC" etc so overall query becomes likes this

index=main-app UID="XYZ" OR UID="ABC"

But I just want to search as keyword in second index not as a UID field. So basically like this

index=main-app "XYZ" OR "ABC"

How can I achieve this?

Re: Extract value from first index and search in second index

inventsekar — Tue, 02 Feb 2021 21:44:33 GMT

Please try...

index=main-app | join uid [search index=auth-app "createsession" | rex field=_raw "UID: (?<uid>......)" | fields uid ] | stats count

Re: Extract value from first index and search in second index

inventsekar — Tue, 02 Feb 2021 21:51:00 GMT

I am not sure, but, lets try this once...

index=main-app OR (index=auth-app "createsession") | rex field=_raw "UID: (?<uid>......)" | stats count(uid)