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
How about this;
FooValueChangeId OR ( FooAutopopulateInitialValueId WasAutoPopulated=true )
| chart count over OrganizationID by FieldName
How about this;
FooValueChangeId OR ( FooAutopopulateInitialValueId WasAutoPopulated=true )
| chart count over OrganizationID by FieldName
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.
Yes, you're correct.
The chart will do;
For Each OrganizationID, count of events per FieldsName.