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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...