Reporting

Creating a report determines values by mv / transaction fields

brettcave
Builder

I have a report that allows me to get a list of certain values per user:

data:

visitorID: A, category: foo, SomeMetric: 32
visitorID: A, category: foo, SomeMetric: 27
visitorID: A, category: bar, SomeMetric: 17
visitorID: A, action: someAction, actionValue: 3
visitorID: A, action: someAction, actionValue: 0

In above, fields are extracted. The first 3 events are MetricEvents, and the last one is an ActionEvent. Query:

eventtype="MetricEvent" OR eventtype="ActionEvent | stats first(SomeMetric) as average_field by visitorId category | stats sum(average_field) as total_metric by VisitorID

Result: visitor: A, total_metric: 49

I can also categorize visitors based on action events:

eventtype="MetricEvent" OR eventtype="ActionEvent | transaction visitorId | eval isActionValueVisitor=if(eventtype="ActionEvent" AND mvindex(ActionValue,mvcount(ActionValue)) > 0, 1, 0) | table visitorId isActionValueVisitor

I now can get a table with a list of users that have completed action with actionvalue > 0, vs visitors that have not completed action. But I would like to combine the 2 to get a report that shows the average of total_metric by isActionValueVisitor (from there I can run through stats to see how many users there are within each category). However, I can't get the 2 queries to work together, because of the use of stats / transactions in the 2 queries. (I could work it out if mvindex returned the natural order, mvindex / mvfind with a transaction sorts the field alphanumerically, so I have no way of getting the equivalent of "first()" from stats ).

How could I go about getting the value of visitors that are categorized based on transactions? (or a completely different approach?)

desired result:

visitor: A, total_metric: 49, isValueVisitor: 1
visitor: B, total_metric: 23, isValueVisitor: 0

Thanks
Brett

0 Karma
1 Solution

brettcave
Builder

Figured it out, using a subsearch:

eventtype="MetricEvent" [ search eventtype="ActionEvent" ActionValue>0 | dedup VisitorID | fields + VisitorID  ] 
  | stats first(SomeMetric) as average_field by VisitorID category
  | stats sum(average_field) as total_metric by VisitorID 
  | eval actionVisitor=1 
  | append [ search eventtype=MetricEvent NOT [ search eventtype="ActionEvent" ActionValue>0 | dedup VisitorID | fields + VisitorID ]
    | stats first(SomeMetric) as average_field by VisitorID category 
    | stats sum(average_field) as total_metric by VisitorID 
    | eval actionVisitor=0 
  ] 
  | stats avg(total_metric) by actionVisitor

View solution in original post

0 Karma

brettcave
Builder

There must be a simple way to pivot the results more easily than what I am doing...

From:

actionVisitor       average_total_metric
0                   33
1                   18

To:

Metric           Action Visitors        Non Action Visitors
Average Met      18                     33

This is what I have, I know there must be a simpler way to do this.

 .... | eval Metric="Average Met" | eval ActionVisitorAvg=if(actionVisitor==1,avg_total_metric,0) | eval NonActionVisitorAvg=if(actionVisitor==0, avg_total_metric,0) | stats sum(NonActionVisitorAvg) as NonActionVisitorAvg sum(ActionVisitorAvg) as ActionVisitorAvg by Metric
0 Karma

brettcave
Builder

Figured it out, using a subsearch:

eventtype="MetricEvent" [ search eventtype="ActionEvent" ActionValue>0 | dedup VisitorID | fields + VisitorID  ] 
  | stats first(SomeMetric) as average_field by VisitorID category
  | stats sum(average_field) as total_metric by VisitorID 
  | eval actionVisitor=1 
  | append [ search eventtype=MetricEvent NOT [ search eventtype="ActionEvent" ActionValue>0 | dedup VisitorID | fields + VisitorID ]
    | stats first(SomeMetric) as average_field by VisitorID category 
    | stats sum(average_field) as total_metric by VisitorID 
    | eval actionVisitor=0 
  ] 
  | stats avg(total_metric) by actionVisitor
0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...