How to get matching AND non matching events with left join

[claudiaG]

Hi I have the use case that i need to find some direct links between different events of the same index and sourcetype.

The result should show me three different bars:

• bar 1: count of the existing links (incl. filter criteria matching)
• bar 2: count of the existing links where filter criteria dont match
• bar 3: count of the events where there is no existing link at all

I came so far to make use of the "left join" to not loose the "not matching" events but now I dont know how to differiance them into a bar diagram or with an if condition to count them. It needs to be counted weekly. Can you help me please?

This is my current query state:

index=A
| rename Name as TargetName
| join type=left max=0 TargetName
   [ search index=A
   | fields TargetName ID Status]
| join type=left SourceID
   [ search index=A
   | fields SourceID, type]
| join type=left TargetID
   [ search index=A
   | fields TargetID]
| bin span=1w@w0 _time
| eval state=if(match(status,"Done") OR match(status,"Pending"), "Link + State is there", if (NOT match(status,"Done") OR NOT match(status,"Pending"), "State is missing", "No Link"))
| dedup ID _time sortby -state
| timechart span=1w@w0 count by state

Somehow I can not make it work to get all "non matching" aka. the "No Link" events. Is the "if" the right way to get what I need? Do i need to add another "eval" within each join? And if yes, how to do that?

Thank you for every help!

This should be my result (see screenshot).

[gcusello]

Hi @claudiaG ,

probably my hint will not cover your requisite, but using a search with three joins, you'll wait for hours.

Did you tried to correlate events using stats?

see my approach and try to adapt it to your Use Case, remembering that Splunk isn't a DB.

something like this:

index=A
| rename Name as TargetName
| bin span=1w@w0 _time
| stats 
   values(Status) AS Status
   dc(Status) AS Status_count
   values(SourceID) AS SourceID
   values(type) AS type
   BY TargetID _time
| eval state=case(
   Status_count=1, Status,
   match(status,"Done") OR match(status,"Pending"), "Link + State is there",
   NOT match(status,"Done") OR NOT match(status,"Pending"), "State is missing",
   1=1, "No Lynk")
| timechart span=1w@w0 count by state

Ciao.

Giuseppe

[isoutamo]

Hi
one old answer about joins in splunk.
https://community.splunk.com/t5/Splunk-Search/What-is-the-relation-between-the-Splunk-inner-left-joi...
r. Ismo