Splunk Search

How to display the ratio of the sum of two fields?

ruhtraeel
Path Finder

Hello,
My data looks like this:

urlupdateid=4, urlid=1, payer=Aetna, EffectiveDate_datetype_correct=T, EffectiveDate_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_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_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_correct=T, ReviewDate_datetype_correct=T, ReviewDate_correct=T, total_datetypes_correct=2, total_dates_correct=2, total_datetypes=2
host = Arthurs-MacBook-Pro.localsource = PCDAccuracy2.txtsourcetype = PCDAccuracy

I want to show the result of the sum of total_datetypes_correct for each row / total_datetypes for each row in a dashboard.

I tried this search query, but it says "Error in 'stats' command: The argument '(TOTAL_CORRECT)' is invalid.":

source="PCDAccuracy2.txt" host="Arthurs-MacBook-Pro.local" sourcetype="PCDAccuracy"| stats sum(total_datetypes_correct) as TOTAL_CORRECT values(total_datetypes) as total_datetypes | stats sum(total_datetypes) as TOTAL values (TOTAL_CORRECT) | eval total = TOTAL_CORRECT / TOTAL | table total_accuracy total

What am I doing wrong?

Thanks

Tags (1)
0 Karma
1 Solution

ruhtraeel
Path Finder

Figured it out. I did this:

source="PCDAccuracy2.txt" host="Arthurs-MacBook-Pro.local" sourcetype="PCDAccuracy"| eventstats sum(total_dates_correct) as TOTAL_CORRECT | eventstats sum(total_datetypes) as TOTAL | eval total = TOTAL_CORRECT / TOTAL

View solution in original post

0 Karma

adonio
Ultra Champion

is this what you are trying to achieve?

| makeresults count=1
| eval data = "urlupdateid=4, urlid=1, payer=Aetna, EffectiveDate_datetype_correct=T, EffectiveDate_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_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_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_correct=T, ReviewDate_datetype_correct=T, ReviewDate_correct=T, total_datetypes_correct=2, total_dates_correct=2, total_datetypes=2 host = Arthurs-MacBook-Pro.localsource = PCDAccuracy2.txtsourcetype = PCDAccuracy"
 | makemv delim=";;;" data
 | mvexpand data
 | eval _raw = data
 | table _raw
 | extract kvdelim="=" pairdelim=", "
 | rename COMMENT as "the above generates data below is the solution" 
 | table total_datetypes_correct total_datetypes
 | eval ratio = round(total_datetypes_correct / total_datetypes, 2)
0 Karma

ruhtraeel
Path Finder

Not quite; I needed to add all the total_datetypes_correct values together for every event, as well as all the total_datetypes values together for every event, then calculate the ratio from that, rather than calculate the ratio for each event individually.

0 Karma

ruhtraeel
Path Finder

Figured it out. I did this:

source="PCDAccuracy2.txt" host="Arthurs-MacBook-Pro.local" sourcetype="PCDAccuracy"| eventstats sum(total_dates_correct) as TOTAL_CORRECT | eventstats sum(total_datetypes) as TOTAL | eval total = TOTAL_CORRECT / TOTAL

0 Karma

ruhtraeel
Path Finder

I would assume it's because the total_datetypes field no longer exists after the first "stats" command. How would I keep the previous fields?

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...