Joining data from 2 data sources in Splunk

[wlouisharris]

I am trying to join data from 2 data sources. The first data source contains events; source=events. The second source contains Service tickets; source=tickets. So I need to display event data along with the ticket data. The event source contains a ticket# field called scnumber; while the ticket source contains the ticket# in a field called NUMBER. I want to join the data between scnumber and NUMBER where the ticket # value is equal; they represent the same data but the fields have different names. Both have several fields of data I need; event: node, summary, scnumber; ticket: assignment group, class, and sub-class.

So I thought the query would be either of the 2:

sourcetype=event | rename scnumber as NUMBER | join type=outer NUMBER [search sourcetype=ticket] | table node summary NUMBER class sub-class

or

sourcetype=event OR sourcetype=ticket | eval ticket=coalesce(scnumber,NUMBER) | table node summary NUMBER class sub-class

So far I'm having no luck. Any help would be appreciated.

[gkanapathy]

I would suggest:

sourcetype=event OR sourcetype=ticket
| eval ticket = coalesce(scnumber,TICKET)
| stats first(node) as node 
        first(summary) as summary
        first(assignment) as assignment
        first(class) as class
        first(sub_class) as sub_class
  by ticket

as the most efficient (in terms of performance and scalability). The implementations of join and transaction are generally less efficient and scalable than stats. If there are field name conflicts between the sourcetypes and those matter, you can eval/rename them before stats, or you can do things like:

... first(eval(if(sourcetype=="event",node,null()))) as node

which will ensure that if a field "node" exists for a ticket number in both "event" and "ticket", that the one from the "event" sourcetype will be selected. If you don't do it this way, Splunk will just pick the first (most recent) value of "node" it finds for the ticket, regardless of sourcetype (which may be okay for you).

[alexandermunce]

@gkanapathy

In the second line of your code above:
| eval ticket = coalesce(scnumber,TICKET)

Is the second arguement for the coalesce function correct?

If so, what does TICKET refer to exactly?

[lguinn2]

BTW, the field name sub-class violates Splunk's field naming rules. It may work in many commands, but I would change it.

I assume that you can have multiple events per ticket? Or should there be an exact 1-1 match?

sourcetype=event OR sourcetype=ticket
| rename scnumber as NUMBER
| transaction NUMBER 
| table node summary NUMBER class sub-class
| sort NUMBER

Try not to use join in Splunk unless absolutely necessary. Since Splunk will retrieve data across sources, sourcetypes, hosts, etc. in a normal search, it is "joining" data already. This is a big difference between how Splunk works vs. a relational database.

BTW, the transaction command may be overkill here. If I could see exactly what you want for output, there might be a better option.