Splunk Search

## How can I calculate a relative value?

Communicator

Hi there.

I'm trying to do something like this:

Relative Conversion = Event Conversion / Total Conversion

Where:

Event Conversion is the conversion within the event (actually calculated by an eval)
Total Conversion is the sum of all conversion fields in all the events (one by event)

I can't get it through and I think that I forget something for sure. I tried append, appendcol, foreach, accum... but I can't get it.

By now, my search is something like this:

``````index=someindex sourcetype=somesourcetype
| stats count(eval(if_OK=="Success")) as TC, sum(field1) as field1, count as TM by Hour,CITY
| join Hour,CITY [ search index=someindex sourcetype="anothersourcetype"
| stats count as TV by Hour,CITY ]
| eval EventConversion=round((TV*100)/TC,2)
| eval EventQuality=round((TC*100)/TM,2)
``````

Some clue to this confused folk, please?

Regards.

Tags (5)
1 Solution
Legend

If the following query is working fine use eventstats command to calculate the total and append to each event.

index=someindex sourcetype=somesourcetype
| stats count(eval(if_OK=="Success")) as TC, sum(field1) as field1, count as TM by Hour,CITY
| join Hour,CITY [ search index=someindex sourcetype="anothersourcetype"
| stats count as TV by Hour,CITY ]
| eval EventConversion=round((TV*100)/TC,2)
| eventstats sum(EventConversion) as TotalConversion
| eval EventQuality=round((TC*100)/TM,2)
| eval RelativeConversion=round(EventConversion/TotalConversion,2)

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Legend

If the following query is working fine use eventstats command to calculate the total and append to each event.

index=someindex sourcetype=somesourcetype
| stats count(eval(if_OK=="Success")) as TC, sum(field1) as field1, count as TM by Hour,CITY
| join Hour,CITY [ search index=someindex sourcetype="anothersourcetype"
| stats count as TV by Hour,CITY ]
| eval EventConversion=round((TV*100)/TC,2)
| eventstats sum(EventConversion) as TotalConversion
| eval EventQuality=round((TC*100)/TM,2)
| eval RelativeConversion=round(EventConversion/TotalConversion,2)

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Communicator

Thanks @niketnilay!

I found the trick 2 minutes ago. With this, I'm can get what I looking for.

Thanks a lot 🙂

Esteemed Legend

Hi slr,
to debug your search you should express single values if_OK, field1, TV, TC and TM, so you could understand if the problem is in field calculation or in eval commands.
Probably problem is in joun command.
Bye.
Giuseppe

Communicator

Hi @cusello

At the moment, the query is ok. I get the expected values. My problem is the next step: with that query, how can I calculate the Total and then use it to calculate the relative?

I can calculate the totals with addcoltotals, or even a appendpipe like this:

`````` appendpipe [stats sum(EventConversion) as TotalConversion]
``````

but then, how can I put that value in the equation

Relative Conversion = Event Conversion / Total Conversion

for all the cities?

Get Updates on the Splunk Community!

#### Devesh Logendran, Splunk, and the Singapore Cyber Conquest

At this year’s Splunk University, I had the privilege of chatting with Devesh Logendran, one of the winners in ...

#### There's No Place Like Chrome and the Splunk Platform

WATCH NOW!Malware. Risky Extensions. Data Exfiltration. End-users are increasingly reliant on browsers to ...