Splunk Search

Divide the sum of all fields that end with X whose value == true with the sum of all fields that end with X whose value equals == true | false

ruhtraeel
Path Finder

Hello,
My data looks like this:
urlupdateid=4, urlid=1, payer=Aetna, EffectiveDate_datetype_correct=T, EffectiveDate_date_correct=F, total_datetypes_correct=1, total_dates_correct=0, total_datetypes=1
host = Arthurs-MacBook-Pro.localsource = PCDAccuracy2.txtsourcetype = PCDAccuracy

urlupdateid=3, urlid=1, payer=Aetna, EffectiveDate_datetype_correct=F, EffectiveDate_date_correct=F, total_datetypes_correct=0, total_dates_correct=0, total_datetypes=1
host = Arthurs-MacBook-Pro.localsource = PCDAccuracy2.txtsourcetype = PCDAccuracy

urlupdateid=2, urlid=1, payer=Aetna, ReviewDate_datetype_correct=T, ReviewDate_date_correct=F, total_datetypes_correct=1, total_dates_correct=0, total_datetypes=1
host = Arthurs-MacBook-Pro.localsource = PCDAccuracy2.txtsourcetype = PCDAccuracy

urlupdateid=1, urlid=1, payer=Aetna, PublicationDate_datetype_correct=T, PublicationDate_date_correct=T, ReviewDate_datetype_correct=T, ReviewDate_date_correct=T, total_datetypes_correct=2, total_dates_correct=2, total_datetypes=2
host = Arthurs-MacBook-Pro.localsource = PCDAccuracy2.txtsourcetype = PCDAccuracy

I need to basically calculate
For each datetype, Sum of <>_datetype_correct == "T" / Sum of all <>_datetype_correct

So in the end, I would have
EffectiveDate_datetype_correct_ratio = 0.5
ReviewDate_datetype_correct_ratio = 1
PublicationDate_datetype_correct_ratio = 1

where there could be a variable number of datetypes, ie. EffectiveDate, ReviewDate, PublicationDate, RetiredDate, ...

How would I do this?

Thanks!

Tags (1)
0 Karma

ruhtraeel
Path Finder

I actually ended up doing this:

source="PCDAccuracy2.txt" host="Arthurs-MacBook-Pro.local" sourcetype="PCDAccuracy" 
| foreach *_datetype_correct [eval <<FIELD>>_datetype_correct_subtotal = 0]
| fillnull value=0
| foreach *_datetype_correct [eval <<FIELD>>_datetype_correct_subtotal = if('<<FIELD>>' == "true", <<FIELD>>_datetype_correct_subtotal + 1, <<FIELD>>_datetype_correct_subtotal)] 
| eventstats sum(*_datetype_correct_subtotal) as *_NUM_CORRECT, sum(*_datetype_correct) as *_datetype_correct_subtotal

| foreach *_datetype_correct [eval <<FIELD>>_datetype_subtotal = 0]
| fillnull value=0
| foreach *_datetype_correct [eval <<FIELD>>_datetype_subtotal = if('<<FIELD>>' == "true" OR '<<FIELD>>' == "false", <<FIELD>>_datetype_subtotal + 1, <<FIELD>>_datetype_subtotal)] 
| eventstats sum(*_datetype_subtotal) as *_TOTAL_NUM, sum(*_datetype_correct) as *_datetype_subtotal

| foreach *_datetype_correct [eval <<FIELD>>_CORRECT_RATIO = <<FIELD>>_NUM_CORRECT / <<FIELD>>_TOTAL_NUM]
0 Karma

arjunpkishore5
Motivator

Try This
Because of the formatting issues, I have added few extra spaces between the arrows. Also, there is no dot after FIELD.

|stats sum(eval(if(*_correct=="T", 1,0))) as T_*_correct, count(8_correct) as total_*_correct
|foreach T_*_correct [ eval < < MATCHSEG1 > >ratio = < < FIELD. > >/total< < MATCHSEG1 > >_correct]

Here's documentation for foreach - https://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Foreach

0 Karma

arjunpkishore5
Motivator

For ReviewDate_datetype_correct, you would do this

|stats sum(eval(if(ReviewDate_datetype_correct=="T", 1,0))) as ReviewDate_datetype_correct_T, count(ReviewDate_datetype_correct) as total_ReviewDate_datetype_correct
|eval ReviewDate_datetype_correct_ratio = round((ReviewDate_datetype_correct_T/total_ReviewDate_datetype_correct),2)

Similarly for the other 2 dates.

0 Karma

ruhtraeel
Path Finder

Hi. the datetypes like "ReviewDate" and "PublicationDate" can't be hardcoded because the list can change.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...