Splunk Search

How to use fields from 2 lookups for pie chart?

gemrose
Explorer

Hello Team,

 

I have 2 look up data and I want to join them through a common field MonthYear. I need to calculate transmission per dept = Total transmission *(size of dept/total size of dept)

In lookup1 I need to calculate the propotion of size based on dept eg;

Transmission for Eng dept = 119 *((100+23)/ 170)

Lookup1:

MonthYear

 size

 org

 dept

July 2022

100

research

Eng

July 2022

23

research

Eng

July 2022

2

data

IU

July 2022

45

research

Lab

Total size

170

 

Lookup2:

MonthYear

Transmission

ID

July 2022

60

global

July 2022

34

global

July 2022

23

Pbg

July 2022

2

pcf

Total transmission

119

 

I made a merge of 2 lookup with join using MonthYear but I am able to pass only one token value at a time. I need to get pie chart based on calculated formula for org and sort for top values in dept

Code:

|inputlookup lookup2.csv

|search MonthYear="July 2022"

|join MonthYear

  [|inputlookup lookup1.csv]

 |stats sum(Transmission) as TotalTransmission, sum(size) as Totalsize by MonthYear

 |join MonthYear

 [|inputlookup lookup1.csv

  |search dept="Eng"

  |stats values(MonthYear) as MonthYear,sum(size) as DeptMem by dept]

|eval "Transmission per dept" = round(("Transmission per dept") * (DeptMem/Totalsize),2)

|fields "Transmission per dept"

Labels (5)
Tags (2)
0 Karma

gemrose
Explorer

Thank you this worked

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

I have 2 look up data and I want to join them through a common field MonthYear. I need to calculate transmission per dept = Total transmission *(size of dept/total size of dept)

In lookup1 I need to calculate the propotion of size based on dept eg;

Transmission for Eng dept = 119 *((100+23)/ 170)


| inputlookup lookup1.csv
| stats sum(size) as DeptMem by dept
| eventstats sum(DeptMem) as TotalSize
| append
    [inputlookup lookup2.csv
    | stats sum(Transmission) as TotalTransmission]
| eventstats values(TotalTransmission) as TotalTransmission
| eval "transmission per dept" = round(TotalTransmission * DeptMem / Totalsize, 2)
| stats values('transmission per dept') as "transmission per dept" by dept

Note:

  • Based on your description, I believe that breakdown by dept is the goal.
  • You cannot get total as you illustrated with "by MonthYear".
  • You cannot get a pie chart with "by MonthYear" if you want to break down by dept
  • Because you need a breakdown by dept, the only useful data in lookup2.csv is a total of Transmission.
  • A single append is a lot more efficient than doing joins.
Tags (1)
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...