topic Re: Using fields from 2 lookups for pie chart in Splunk Search

How to use fields from 2 lookups for pie chart?

gemrose — Fri, 22 Sep 2023 18:12:24 GMT

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"

Re: Using fields from 2 lookups for pie chart

yuanliu — Wed, 20 Sep 2023 19:18:16 GMT

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.

Re: Using fields from 2 lookups for pie chart

gemrose — Thu, 21 Sep 2023 10:51:50 GMT

Thank you this worked