Dashboards & Visualizations

Is there a possibility of like join?

_pravin
Path Finder

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

Labels (1)
0 Karma

_pravin
Path Finder

Hi @richgalloway,

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

_pravin_0-1645785244390.png

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.

_pravin_1-1645785436951.png

Thanks,

Pravin

0 Karma

gcusello
Esteemed Legend

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.

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

0 Karma

_pravin
Path Finder

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

 

 

0 Karma

gcusello
Esteemed Legend

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

0 Karma

_pravin
Path Finder

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

0 Karma

gcusello
Esteemed Legend

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

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...