Struggling to correlate 2 sourcetypes

[rmines]

Hi,

I'm trying to correlate data from 2 different sourcetypes that share a common field. I think I should be able to use "transaction" to do this, but I'm struggling. To explain my use case, imagine the following two sets of data:

Source 1

Device      ID

Device_1    123

Device_1    456

Device_1    179

Device_2    456

Device_2    999

Device_2    111

Device_3    999

Device_3    123

Source 2

ID      Text

123    Example_1

456    Example_2

179    Example_3

456    Example_4

999    Example_5

111    Example_6

In the first instance I want to be able to correlate them using the "ID" field, and pull back the combined fields, as follows:

Correlated

Device      ID      Text

Device_1    123    Example_1

Device_1    456    Example_4

Device_1    179    Example_3

Device_2    456    Example_4

Device_2    999    Example_5

Device_2    111    Example_6

Device_3    999    Example_5

Device_3    123    Example_1

Further I want to be able to search based on the value of that correlated "Text" field, such as showing only events where Text="Example_5"

Device_2 999 Example_5

Device_3 999 Example_5

Apologies for the long winded build up, but if anyone can get me started on how to achieve this I'd be extremely grateful, as I'm not sure if I'm going down completely the wrong track in trying to use "transaction" for this.

[kristian_kolb]

While a join might be the only option, it is a fairly expensive - especially for larger data sets.

With the data set in your example, you could get away with a transaction (faster);

sourcetype=type1 OR sourcetype=type2 | transaction ID | table Device ID Text | where Text="Example_5"

Hope this helps,

Kristian

[martin_mueller]

That sounds like a join to me:

sourcetype=source1 | join ID max=0 [search sourcetype=source2 Text="Example_5"]