So suppose that everyday Splunk takes in a report that houses 9 different fields, one of which is called 'status'. Status has the option of being 'New', 'Closed', or 'Open'. Because the report is sent to Splunk everyday, a report with ID =1 will indicate "Open" everyday that it is "Open", so (for example) from 1/1/20, 1/2/2020,...,1/5/20 it is 'Open' and on 1/6/20 it is "Closed". So what I'm trying to do is sum up all unique reportId's for a given month, so from my example it should only return a value of 1 for 'OPEN' and 1 for "Closed' when I sum it up for the month of January. My current query is below but this counts the number of days a reportId was 'Open' that month. So my query is returning 5 from my example above for 'OPEN'. The last part of the query I wanted to find the 'Rate" of 'Open'/'Closed' but as you guessed it isn't doing it for all of them and only individually.
index=base
| bin _time span=1month
| stats count(eval(status="Open")) as OPEN, count(eval(status="Closed")) as CLOSED by reportName, _time, reportId
| eval Rate=abs(OPEN/CLOSED)
Does anyone have any suggestions with how to solve my problem, any suggestions would be very much appreciated. Thanks in advance. (End goal is a timechart of OPEN, CLOSED, Rate with the x axis as time in months and the y be total number of unique reportIds for that month)
To whomever may need this out there the correct function to use was dc. I didn't need it for closed because I was only counting those once to begin with.
index=stuff
| bin _time span=1month
| stats dc(eval(status="OPEN")) as OPEN, count(eval(status="CLOSED")) as CLOSED by reportId, reportName, _time
| stats sum(CLOSED) as CLOSED, sum(OPEN) as OPEN by _time
| eval Rate=round(CLOSED/OPEN,3)*100
To whomever may need this out there the correct function to use was dc. I didn't need it for closed because I was only counting those once to begin with.
index=stuff
| bin _time span=1month
| stats dc(eval(status="OPEN")) as OPEN, count(eval(status="CLOSED")) as CLOSED by reportId, reportName, _time
| stats sum(CLOSED) as CLOSED, sum(OPEN) as OPEN by _time
| eval Rate=round(CLOSED/OPEN,3)*100