topic Re: How to chart the results of two joined searches? in Splunk Search

How to chart the results of two joined searches?

jdhux — Wed, 08 Jun 2016 19:19:32 GMT

I have two types of log events:

FIELD INITIAL VALUE
Message: 
{
        "FieldName":"Field_A",
        "OrganizationID":1234,
        "FooDocumentId":01,
        "WasAutoPopulated":true,
        "FooAutopopulateInitialValueId":567,
}

FIELD UPDATE
Message: 
{
        "FieldName":"Field_A",
        "OrganizationID":1234,
        "FooDocumentId":01,
        "FooValueChangeId":890,
}

that I am trying to chart out using two joined searches.
FooDocumentId is a primary key, FieldName can have 1 of 10 values.

I want to count the number of times when a field had an initial value event AND an update event.

I have two separate queries to get these counts (that I think work):

FIND INITIAL FIELD VALUE EVENTS
    WasAutoPopulated=true 
    | chart dc(FooAutopopulateInitialValueId) by OrganizationID, FieldName

FIND FIELD UPDATES
    FooValueChangeId 
    | dedup FooValueChangeID 
    | chart COUNT(eval(FooValueChangeID)) by OrganizationID, FieldName

But I've been struggling to get the join right. I've done:

FooValueChangeId 
| dedup FooValueChangeID 
| join type=left FooDocumentId, FooFieldName, FooOrgID
  [search FooAutopopulateInitialValueId WasAutoPopulated=true]
| chart COUNT(eval(FieldName)) by OrganizationID, FieldName

but the DocumentIds for update events don't line up with the DocumentIds for initial value events

I'm looking for output like:

OrganizationID  Field_A Field_B Field_C
1234            2       1       0
0978            4       3       1
etc...

where the numbers under each fieldname are the counts of when that field for that organization had both an initial field value event and a field update event.

Am I miles off base?

Thanks

Re: How to chart the results of two joined searches?

Masa — Wed, 08 Jun 2016 22:38:00 GMT

How about this;

 FooValueChangeId OR ( FooAutopopulateInitialValueId WasAutoPopulated=true )
 | chart count over OrganizationID by FieldName

Re: How to chart the results of two joined searches?

jdhux — Fri, 10 Jun 2016 20:04:30 GMT

Saw your reply. There are some logging issues with my data and I'm re-evaluating what I'm using to gauge "success" of any query at this point.

If I'm reading your query right, though, this would get me all changes as well as initial values - then essentially dedup when you combine them in a chart?

Not sure I have that right.

Re: How to chart the results of two joined searches?

Masa — Fri, 10 Jun 2016 23:00:18 GMT

Yes, you're correct.

The chart will do;
For Each OrganizationID, count of events per FieldsName.