Splunk Search

How can I calculate a relative value?

slr
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.

0 Karma
1 Solution

niketn
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!!!"

View solution in original post

niketn
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!!!"

slr
Communicator

Thanks @niketnilay!

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

Thanks a lot 🙂

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

slr
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?

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 ...