topic Re: Use other events fields as a field for your search in Splunk Search

Use other events fields as a field for your search

pmgahan — Thu, 19 Oct 2017 13:14:20 GMT

Hi,

I need to use events on a data source as a reference for other events

Example:

What I need is to output the following for items 2 and 4:

Thank you!

Re: Use other events fields as a field for your search

gcusello — Thu, 19 Oct 2017 14:34:31 GMT

Hi pmgahan,
try something like this

index=your_index
| rename IDCategory AS ID_Key
| join ID_Key [ search index=your_index | rename IDCategory AS ID_Key Name AS Category_Name| fields ID_Key  Category_Name]
| table ID Name Type ID_Key Category_Name

Put attention that the search is the same in both main and sub search.
Bye.
Giuseppe

Re: Use other events fields as a field for your search

HiroshiSatoh — Thu, 19 Oct 2017 14:41:26 GMT

Try this!

(your search)
|eval Name=if(Type="Category","",Name)
|eval Category=if(Type="Category",Name,"")
|eval IDCategory=if(Type="Category",ID,IDCategory)
|eval ID=if(Type="Category","",ID)
|eval Type=if(Type="Category","",Type)
|stats earliest(ID) as ID,
            earliest(Name) as Name,
            earliest(Type) as Type,
            earliest(Category) as Category by IDCategory

Re: Use other events fields as a field for your search

pmgahan — Thu, 19 Oct 2017 18:34:20 GMT

It doesn't seem to be working...
Take into account that from my expected results below, what I'm painting bold comes from rows 3 & 4 and what is italic comes from matching the IDCategory with rows 0 & 1 and adding the name as a new column.

What I need is to output the following for items 2 and 4:

Thank you!

Re: Use other events fields as a field for your search

pmgahan — Thu, 19 Oct 2017 18:52:14 GMT

This is adding a column and moving the Name to this new column but what I need is to add the Category name to the row where the categoryID matches the Category's row ID

Thank you!

Re: Use other events fields as a field for your search

pmgahan — Tue, 29 Sep 2020 16:17:53 GMT

I figured that what I may have to do is the following

This should match the IDCategory of one row to the ID for the category and output the Name. It is still not working.

Thank you!

Re: Use other events fields as a field for your search

HiroshiSatoh — Fri, 20 Oct 2017 00:53:52 GMT

The Joins command links events with common fields, but it is a very heavy search command.
Please link as much as possible with stats or transaction command.

Re: Use other events fields as a field for your search

gcusello — Fri, 20 Oct 2017 12:05:52 GMT

Hi pmgahan,
in other words, you want only the rows where there's the match between IDCategory and ID, is it correct?
if I correctly understood, try something like this

index=your_index
| rename IDCategory AS ID_Key
| join ID_Key [ search index=your_index | rename IDCategory AS ID_Key Name AS Category_Name| fields ID_Key  Category_Name]
| search Category_Name=*
| table ID Name Type ID_Key Category_Name

In this way you'll have only

3|Item1|Item|0|Category1
4|Item2|Item|1|Category2

Bye.
Giuseppe

Re: Use other events fields as a field for your search

pmgahan — Fri, 20 Oct 2017 13:07:59 GMT

Thanks Giuseppe, but the result from your search would result as follows:

instead of

Patrick

Re: Use other events fields as a field for your search

gcusello — Fri, 20 Oct 2017 13:31:07 GMT

Hi pmgahan,
sorry I did an error in subsearch, try:

index=your_index 
| rename IDCategory AS ID_Key
| join ID_Key [ search index=your_index | rename ID AS ID_Key Name AS Category_Name | fields ID_Key  Category_Name ]
| search Category_Name=*
| table ID Name Type ID_Key Category_Name

it should run
after try

index=your_index IDCategory=*
| rename IDCategory AS ID_Key
| join ID_Key [ search index=your_index NOT IDCategory=* | rename ID AS ID_Key Name AS Category_Name| fields ID_Key  Category_Name]
| table ID Name Type ID_Key Category_Name

that should be quicker.

Bye.
Giuseppe