Multisearch query

[DonBaldini]

I am analysing Incident to Problem linkage by doing a search of the Incident table and then using a Join to the Problem to get supporting data for linked problems. Problem I have is with Join I am close to threshold for time periods for the search to fail

I have tried to use multisearch and OR search but I need to retain Incident results where there is no problem linked, hope this makes sense, code I have written...

| multisearch [search index=servicenow sourcetype="incident" ] [search index=servicenow sourcetype="problem" ]
| eval incident=if(sourcetype="incident",number,null), problem=if(sourcetype="incident",dv_problem_id,dv_number)
| stats latest(eval(if(sourcetype="incident",dv_opened_at,null()))) as inc_opened, latest(problem) as problem, latest(eval(if(sourcetype="problem",dv_state,null()))) as prb_state by incident

[gcusello]

Hi @DonBaldini ,

I'd use OR to avoid subsearches.

Anyway, I suppose that the issue is related to the fact thta you're using the incident field that could be null.

Please chech the first eval to find a value for the incident field also for the sourcetype "problem".

Ciao.

Giuseppe

[DonBaldini]

Yeah, the issue I have is that the problem ID is the only common field but by using problem ID I wouldn't return the unlinked Incident data

Thanks

[gcusello]

Hi @DonBaldini,

the only way to correlate heterogeneous data sources is to find a common key and give these values to a common key to use in the stats command.

So you need to find this common key that has always a common value between the two data sources.

Ciao.

Giuseppe

[DonBaldini]

I'm thinking what I want to do is perhaps not possible, tried another variation on the code that doesnt pull in the problem values

(sourcetype="incident") OR (sourcetype="problem")
| eval incident=if(sourcetype="incident",number,null), problem=if(sourcetype="incident",dv_problem_id,null), prb_field=if(sourcetype="problem",dv_number,dv_problem_id)
| stats latest(eval(if(sourcetype="incident",dv_opened_at,null()))) as inc_opened, latest(problem) as problem, latest(eval(if(sourcetype="problem",dv_state,null()))) as prb_state by incident, prb_field