Join with Stats

[onthakur]

index=abcd "API : access : * : process : Payload:"
|rex "\[INFO \] \[.+\] \[(?<ID>.+)\] \:"
|rex " access : (?<Event>.+) : process"
|stats count as Total by Event
|join type=inner ID
[|search index=abcd "API" AND ("Couldn't save")
|rex "\[ERROR\] \[API\] \[(?<ID>.+)\] \:"
|dedup ID
|stats count as Failed ]
|eval Success=Total-Failed
|stats values(Total),values(Success),values(Failed) by Event

Event values(Total) values(Success) values(Failed)
Event1 76303    76280   23
Event2 4491   4468   23
Event3 27140   27117   23
Event4 118305 118282 23
Event5 318810 318787 23
Event6 9501 9478 23

I am trying to join to different search (index is common) on ID field and then trying to group them by "Event" field but the Failed column is showing the same value for all the events.

[bowesmana]

What is the relationship between ID and Event, because you don't appear to be doing anything with ID in you  current search. Does Event exist in your second dataset (ERROR API [ID])

Assuming that your purpose of trying to join in the first place on ID is because you don't have Event in the second dataset and ID has a 1:1 relationship with Event, then try this

index=abcd ("API : access : * : process : Payload:") OR
           ("API" AND ("Couldn't save"))

``` You could combine this rex into a single statement to extract ID
    for both INFO and ERROR cases if you can make the regex ```
| rex "\[INFO \] \[.+\] \[(?<InfoID>.+)\] \:"
| rex "\[ERROR\] \[API\] \[(?<ErrorID>.+)\] \:"

``` Assume this will ONLY occur in Info events, so will be null for ERROR ```
| rex " access : (?<Event>.+) : process"

``` Get the common ID ```
| eval ID=coalesce(InfoID, ErrorID)

``` t is a base event, forms Total ```
| eval t=if(isnotnull(InfoID), 1, 0)

``` Summing 't' gives total and count of unique ErrorID gives failed ```
| stats sum(t) as Total dc(ErrorID) as Failed values(Event) as Event by ID
| eval Success=Total-Failed

This uses the unique count of ErrorID to determine failures, which is effectively the dedup of the error Id, but it is assuming that one ID is one Event so at the end with the values(Event) it will have the Event extracted from the Info events and join on the common ID.

Hope this helps and always use the principle that join in Splunk is never a good way to go.

[onthakur]

What is the relationship between ID and Event, because you don't appear to be doing anything with ID in you  current search. Does Event exist in your second dataset (ERROR API [ID])

#### ID is the command field in both the data sets . while Event is only present in 1st data set i.e

("API : access : * : process : Payload:")

[bowesmana]

OK, so try the search as I expect that will give you what you want

[richgalloway]

Group the Failed counts by Event.

...
[|search index=abcd "API" AND ("Couldn't save")
|rex "\[ERROR\] \[API\] \[(?<ID>.+)\] \:"
|dedup ID
|stats count as Failed by Event ]
...

You could do the same thing without a join for better performance.

index=abcd ("API : access : * : process : Payload:") OR ("Couldn't save")
|eval status = if(searchmatch("Couldn't save"), "Failed", "Success")
|stats count as Total, sum(eval(status="Failed")) as Failed by Event
|eval Success=Total-Failed
|table Event Total Success Failed