topic Re: Foreign key in Splunk Search

Foreign key

yurykiselev — Tue, 29 Sep 2020 14:37:21 GMT

Hi!
I have two indexes: patients and examination

How could I get a table of all examinations for males?
Thank you!

Re: Foreign key

gcusello — Wed, 21 Jun 2017 09:55:59 GMT

Hi yurykiselev,
there are many choices: join, transaction o stats:
join

index=examination
| rename user_id AS id_name
| join id_name type=left [ search index=patients | fields id_name gender date_of_birth ]
| table _time user_id gender date_of_birth exam_type

transaction

index=examination OR  index=patients
| rename user_id AS id_name
| transaction id_name 
| table _time user_id gender date_of_birth exam_type

stats

index=examination OR  index=patients
| rename user_id AS id_name
| stats values(gender) AS gender values(date_of_birth) As date_of_birth values(exam_type) AS exam_type by user_id

Stats is the quickest.

Bye.
Giuseppe

Re: Foreign key

niketn — Tue, 29 Sep 2020 14:33:04 GMT

Is id_name the foreign key? i.e. can we correlate id_name and user_id?

If so you can create a field alias in one of the indexes to be the same as that in the other which is similar to,

| rename id_name as user_id

PS: Once you create Field Alias you will not need rename command.

(index=patient AND id_name=* gender="male") OR (index=examination AND user_id=*)
| rename id_name as user_id
| stats values(examination) as examinations by user_id

Re: Foreign key

yurykiselev — Wed, 21 Jun 2017 19:26:43 GMT

Thank you all!