topic Re: Is there a possibility of like join? in Dashboards & Visualizations

Is there a possibility of like join?

_pravin — Thu, 24 Feb 2022 18:42:29 GMT

Hi Community,

I am working on building SPL to combine results from two tables where there is a column field but with a complication.

One of the tables to be combined has matching values as well as subset values from the other table. Is there a possibility to combine them using a join or other command and get common values?

Regards,

Pravin

Re: is there a possibility of like join?

richgalloway — Thu, 24 Feb 2022 18:26:06 GMT

Probably, but you'll have to tell us more about the use case for us to know for sure. Sample data will be helpful.

In general, one or both sides of the join just needs to normalize a field (or create a new normalized field) to be used for joining events.

Re: Is there a possibility of like join?

gcusello — Fri, 25 Feb 2022 07:38:36 GMT

Hi @_pravin,

at first I hint to use join only if you haven't another choice: all the people that arrive to Splunk from DB hardly use join, but it's a very heavy and slow command!

Anyway, as @richgalloway said, you have to normalize the fields for joining, e.g.

if in indexA you have a complete url (e.g. https://www.my_url.com/aaaa/bbbb/cccc)
in the second index a pattern of a single site (e.g. www.my_url.com)
the field in indexA is "url"
the field in indexB is "pattern"

you could try something like this:

index=indexA OR index=indexB | rex field=url "^\w+:\/\/(?<pattern_url>[^\/]+)" | eval pattern_for_search=coalesce(pattern_url,pattern) | stats dc(index) AS dc_index values(index) AS index BY pattern_for_search | eval status=if(dc_index=2,"present in both","present in one") | table pattern_for_search status index

Please, see my approach and try to apply to your Use Case.

Ciao.

Giuseppe

Re: Is there a possibility of like join?

_pravin — Fri, 25 Feb 2022 10:38:34 GMT

Hi @richgalloway,

Attached below is the data from the first SPL which is generated using a data model.

Attached below is the second result, which is obtained from a lookup table.

The field FullCommand is a subset of the field Activity from the first result.

Thanks,

Pravin

Re: Is there a possibility of like join?

_pravin — Fri, 25 Feb 2022 10:41:48 GMT

Hi @gcusello ,

Thanks for the response, but I am not sure if I could use your approach as either of the SPL have an index except for the fact that they have common or almost common fields.

Thanks,

Pravin

Re: Is there a possibility of like join?

gcusello — Fri, 25 Feb 2022 10:55:18 GMT

Hi @_pravin,

start describing the two tables: index and fields

then give us two o three samples for each table

than tell us which are the fields for joining.

at the end what is the result you'd like.

Ciao.

Giuseppe

Re: Is there a possibility of like join?

_pravin — Fri, 25 Feb 2022 11:01:33 GMT

Hi @gcusello ,

I had responded to @richgalloway 's message, wherein I have added details about the table.

Please let me know if that is sufficient.

Regards,

Pravin

Re: Is there a possibility of like join?

gcusello — Fri, 25 Feb 2022 11:23:41 GMT

Hi @_pravin,

ok, let me summarize information, correct me if I'm wrong:

you elaborated the data in an index called "your_index" and obtained the columns in first screenshot
the fields (after elaboration) in your_index are:
- Activity
- count
- Average_minutes
- GlobalTechnicalStatus
- Functional_Status
then you have a lookup called "your_lookup",
the fields are.
- FullCommand
- Owner
the fields for joining are:
- Activity
- FullCommand
I don't see anything common but I can suppose from your first request that Activity contains FullCommand
I suppose that you want to add the Owner to the first table

Is all of this correct?

If yes, you could try something like this

your search and elaborations to arrive to first table (probably stats) | lookup your_lookup.csv FullCommand AS Activity OUTPUT Owner | table Activity count Average_minutes GlobalTechnicalStatus Functional_Status Owner

Ciao.

Giuseppe