How to join two sourcetypes together from same index?

[markturner14]

Hi All,

I`m looking to combine the two  searches below.  I have been messing around with it, but I don`t do this alot! - but I thought rather than put in my ramblings I would ask the basic need of the question.

I basically want the 'state', 'startTime' and 'completeTime' from the second search to be added to the first search

# search 1
index=vmware-taskevent sourcetype=vmware_inframon:events fullFormattedMessage="Task:*"
| stats by info.entityName fullFormattedMessage info.entity.type info.queueTime userName vm.name computeResource.name createdTime info.task.moid
| sort createdTime
| table info.entityName fullFormattedMessage info.entity.type info.queueTime userName vm.name computeResource.name createdTime info.task.moid

# search 2

index=vmware-taskevent sourcetype="vmware_inframon:tasks"
| stats by entityName name queueTime startTime completeTime entity.type state reason.userName task.moid
| table entityName name queueTime startTime completeTime entity.type state reason.userName task.moid


There are common results from fields but not common field names.  Ie sourcetype=vmware_inframon:events has 'info.task.moid' and sourcetype="vmware_inframon:tasks" has 'task.moid' and the results from this field matches.
This is the same for info.entityName and entityName

[markturner14]

Hi Giuseppe,  thanks for the quick response.

This is related to VMware and and the tasks and events data - so the value in the info.task.moid and task.moid will match - but will be different for each task/event

So i was hoping i could join the two fields from the different sourcetypes based on the same unique values that return from those two fields in those two sourcetypes.

It is not essential for what I`m doing - I`m just looking to enrich the data a little more/

thanks

[gcusello]

Hi @markturner14 ,

as I said, you can assign the same field name to both the fields using eval and coalesce.

Ciao.

Giuseppe

[gcusello]

Hi @markturner14,

if the problem is only the different filenames, you can use eval coalesce to have one field name:

| eval task_moid=coalesce("info.task.moid","task.moid")

applied to all the fields with different names.

The only real problem is: are there values in these fields for both the sourcetypes? 

if yes you can put both the searches in one.

If not, you cannot put these fields in the BY clause because in this case you don't have results, these fields must be inserted using the values option.

Then I see different fields in the two stats in the BY clause, this means that you have to move some fields from the BY clause to the values option.

Ciao.

Giuseppe