How to calculate on one event into different category ?

[Jouman]

Hi all,

I want to analyze several events and the fields in them.
Origianally, I use case() to capture one field(Causse) in A_EVENT, and 2 fields(Type, Scenario) in B_event.

(index="idx_message" Name="A_EVENT" OR Name="B_Event")
| rename "Data.Cause" as A_cause `comment("belong to A_Event")`
| rename "Data.Type" as B_type `comment("belong to B_Event")`
| rename "Data.Scenario" as B_scenario `comment("belong to B_Event")`

| eval Scenario_name=case(
Name="A_EVENT" AND A_cause=0, "A Cause: X reason",
Name="A_EVENT" AND A_cause=1, "A Cause: Y reason",
Name="B_Event" AND B_type=0, "B Type: a category",
Name="B_Event" AND B_type=1, "B Type: b category",
Name="B_Event" AND B_scenario=0, "B Scenario: description 1",
Name="B_Event" AND B_scenario=1, "B Scenario: description 2",
true(), null)

| where isnotnull(Scenario_name)

| chart limit=0 count by Scenario_name 

However, while I check the output, the output will be

Scenario_name	count
A Cause: X reason	1
A Cause: Y reason	2
B Type: a category	3
B Type: b category	4

The scenario "B Scenario: description 1" and "B Scenario: description 2" are missing.

I found the reason comes from "B Scenario" and "B Type" is used to verdict the same event, if I use case(), I am unable to get any "B Scenario" because all the events will be verdicted as "B Type" already.

Is there any way to generate such output?

Scenario_name	count
A Cause: X reason	1
A Cause: Y reason	2
B Type: a category	3
B Type: b category	4
B Scenario: description 1	2
B Scenario: description 2	5

Thanks.

[yeahnah · Accepted solution]

Hi @Jouman 

OK I see and you're right the case statement will not work here.  

Here's a run anywhere example that you could try.

index=dummy
| append [ | makeresults count=10 | streamstats count 
  | eval raw=if((count%2) = 0, "Name=A_EVENT Data.Cause=0:Name=B_Event Data.Type=0 Data.Scenario=1", "Name=A_EVENT Data.Cause=1:Name=B_Event Data.Type=1 Data.Scenario=0")
        ,raw=split(raw, ":")
  | mvexpand raw
  | rename raw AS _raw
]
| extract  ``` use verbose mode ```
| rename Data_* AS Data.*
  ``` ^^^ create dummy events ^^^ ```
| rename "Data.Cause" as A_cause `comment("belong to A_Event")`
| rename "Data.Type" as B_type `comment("belong to B_Event")`
| rename "Data.Scenario" as B_scenario `comment("belong to B_Event")`
``` event B has two possible scenario types so make a new multivalue field then split and duplicate events by scenario type ```
| eval event_type=split(if(Name=="B_Event", "B_type=".B_type.":B_scenario=".B_scenario, "A_cause=".A_cause), ":")
| mvexpand event_type
| table Name event_type
| eval Scenario_name=case(
   Name="A_EVENT" AND event_type="A_cause=0", "A Cause: X reason",
   Name="A_EVENT" AND event_type="A_cause=1", "A Cause: Y reason",
   Name="B_Event" AND event_type="B_type=0", "B Type: a category",
   Name="B_Event" AND event_type="B_type=1", "B Type: b category",
   Name="B_Event" AND event_type="B_scenario=0", "B Scenario: description 1",
   Name="B_Event" AND event_type="B_scenario=1", "B Scenario: description 2",
   true(), null())
| stats count BY Scenario_name

 Hope that helps

[Jouman]

@yeahnah  Thank you very much. I tried and the code works well. This really helps me a lot.

[yeahnah]

Hi @Jouman 

It's always preferable to provide an example of the raw event(s).  Obfuscate or remove sensitive data, if any.
Please use the Insert/Edit code sample when adding example data

[Jouman]

@yeahnah  Thanks for the suggestion. I rewrite my question and hope that would be clear.

Thank you!