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!

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