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
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
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
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
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
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
Hi @_pravin,
ok, let me summarize information, correct me if I'm wrong:
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
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.