Use other events fields as a field for your search

[pmgahan]

Hi,

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

Example:

ID . | Name . |Type . | IDCategory
0|Category1|Category|null
1|Categoryt2|Category|null
3|Item1|Item|0
4|Item2|Item|1

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

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

Thank you!

[HiroshiSatoh]

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

[pmgahan]

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!

[HiroshiSatoh]

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.

[gcusello]

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

[pmgahan]

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.

ID . | Name . |Type . | IDCategory
0|Category1|Category|null
1|Categoryt2|Category|null
3|Item1|Item|0
4|Item2|Item|1

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

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

Thank you!

[pmgahan]

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

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]
| table ID Name Type ID_Key Category_Name

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!

[gcusello]

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

[pmgahan]

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

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

instead of

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

Patrick

[gcusello]

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