black-out/ simple way to combine events from two sourcetypes on same Id

[Mike6960]

I must have a blackout because the case does not seem to difficult but i cant get it working. I have two sourcetypes, when an event in one sourcetype gets an error it will appear in the other sourcetype with some kind of a description. The Id in both the sourcetypes will be unique. How can I get a result from both sourcetypes when the error occures?

[Mike6960]

maybe i was not complete in my question, but the field with the error is only present in one of the sourcetypes, so if i filter on the value of the error i dont get both the sourcetypes and their data

[somesoni2]

Is the string with error description (which is same in both sourcetypes) been extracted as a field? If not, we need to extract it so that we can correlate events from both using that common description. Something like this

(your sourcetype1 or st1 base search) OR (your sourcetype2 or st2 base seach)
| ..if err_description not extracted.. add rex command to get it extracted..
| stats values(st1field1) as st1field1 values(st1field2) as st1field2... values(st2field1) as st2field1 values(st2field2) as st2field2... values(Id) as Ids by err_description | where mvcount(Id)=2

Last where clause is to ensure that error description was present in both sourcetypes (with different Uniq Id).

[Mike6960]

the error is not present in both sourcetypes only in one. One sourcetype contains actions, the other contains errors (if there are any) when the action cant be executed. The Id is unique in both

[somesoni2]

Would you mind sharing which sourcetypes have what fields that you need to display in the final report/table? Also, can you share your search when you look for just the error.

[Mike6960]

sourcetype1=Id, loglevel (if its an error, loglevel=error) description (of the error)

sourcetype2=Id, action, description (of the action)

If the action from sourcetype 2 results in an error, it results in a event in sourcetype 1. Where the id is equal

[alemarzu]

@Mike6960 Something like this?

(sourcetype=sourcetype_A OR sourcetype=sourcetype_B) | stats values(_raw) as raw, values(ERROR_FIELDNAME) AS error by ID_FIELD sourcetype

edited

[Mike6960]

maybe i was not complete in my question, but the field with the error is only present in one of the sourcetypes, so if i filter on the value of the error i dont get both the sourcetypes and their data

[alemarzu]

@Mike6960 try this one

[Mike6960]

Thanks, but is not what i need. In this way i only get the errors

[gcusello · Accepted solution]

Hi Mike6960,
you can do it in many ways: transaction, join or stats.
I prefer the last one because is the quicker, adapt this example to your situation:

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2)
| stats values(field1) AS field1 values(field2) AS field2 count by Id

then you can apply all the filters you like: e.g. presence of a value from both the sourcetype or absence of a value, ...

Bye.
Giuseppe
P.S.: whan I have a black-out Community is the easiest way to pass it!

[Mike6960]

maybe i was not complete in my question, but the field with the error is only present in one of the sourcetypes, so if i filter on the value of the error i dont get both the sourcetypes and their data

[gcusello]

Let me understand:
you have in search 1 error message, Id and other interesting fields, in search2 you have Id and other interesting fields.
You want to find fields from search1 and search2 but only the ones with an Id where in search1 there's the error message, is it correct?
If this is your need, try something like this:

 (index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) [ search index=index1 sourcetype=sourcetype1 error_message="my message"  | dedup Id | fields Id ]
 | stats values(field1) AS field1 values(field2) AS field2 count by Id

Bye.
Giuseppe

[Mike6960]

Great, it works. Now trying to understand. First you select both indexes/sourcetypes? Then a subselect (between brackets) to select the errors? The stats at the end uses fields from both searches?

I noticed one strang thing, when i look at the output the fieldvalues don's align with the row. For example, one result is like this:

Id flow level count
123456 action error 2
message exe

In fact the 'error' belongs to 'message'
I hope you understand what I mean

[gcusello]

Hi Mike6960,
Yes the logic of the search is the one you understood: I take events from both the searches and, using subsearch, I filter only events with an Id related to the error_message.

About the alignment, let me understand: message and exe which fields are: flow or level or none?
if you have more than one value in a field, you can try to use only one value using mvindex command or put in the same row using nomv command, it's difficult to help more because I haven't your data.

Bye.
Giuseppe

[Mike6960]

let me try to rephrase. The result of the search contains both sourcetypes. In one row the error is presented next to the sourcetype which does not contain the error. Maybe splunk does an order by alphabet?

[gcusello]

Yes in values, Splunk puts values in alphabetical order.

If you don't want two different values, you can have two rows putting also sourcetype in stats BY clause.

But what is your need: to have events with Ids related to error_message or what?
Every row has both the sourcetypes so maybe you don't need to show them.
If you want to be sure that you take one event for each sourcetype you can put a filter on a field or calculate dc(sourcetype) AS num_sourcetypes in stats and put a filter num_sourcetypes =2
Bye.
Giuseppe

[Mike6960]

hmm, I do want the different values, but now it looks like the error belongs to the flow (sourcetype) "action" While actually its in flow "message"

    Id                flow            level    count
   123456             action       error         2
                       message         exe

[gcusello]

let me understand: do you have:

• in sourcetype1 flow=action and level=error
• in sourcetype2 flow=message and level=exe

?

what do you want as output?

Bye.
Giuseppe

[Mike6960]

I have:

• sourcetype "action" this contains a fieldvalue 'exe'
• sourcetype "message" this contains a fieldvalue 'error'

The search you provided workf fine, but because splunk order alphabetically the field value 'exe' is presented under 'error' . while it actually exists in sourcetype

[gcusello]

Hi Mike6960,
if it's acceptable for you to have different rows for each level, you could use something like this:

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) [ search index=index1 sourcetype=sourcetype1 error_message="my message"  | dedup Id | fields Id ]
| stats values(level) AS level count BY Id flow
| eventstats count AS Total BY Id
| table Id flow level Total

so you'll have

Id  flow    level   Total
123456  action  exe 2
123456  message error   2

Otherwise, but it's a little bit more complicated you could use

(index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) [ search index=index1 sourcetype=sourcetype1 error_message="my message"  | dedup Id | fields Id ]
| stats values(level) AS level count BY Id flow
| append [ search 
      (index=index1 sourcetype=sourcetype1) OR (index=index2 sourcetype=sourcetype2) [ search index=index1 sourcetype=sourcetype1 error_message="my message"  
      | dedup Id 
      | fields Id
       ]
  ]
| table Id flow level Total
| sort Id -Total
| eval Id=if(Total>0,Id,"")

And you'll have

Id  flow    level   Total
789              2
     action exe  
     message    error    
123456               2
     action exe  
     message    error    

Bye.
Giuseppe