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!
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]
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
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.
Hi. the datetypes like "ReviewDate" and "PublicationDate" can't be hardcoded because the list can change.