Hey community I have my data in both MySQL and in Splunk. I'm trying to mimic the MySQL queries in Splunk so I can make a visual. My Data has five columns,:"Month", "Project", "Status", "Completion", "Points". The first query sums the column "Points" only if that row includes a Status and Completion value of "Done" and then grouping it by Month. The second query is summing the column points just by the Month. The problem I'm running into is having them together in one Splunk Query as I'm trying to have both tables in one graph. Any suggestions?    select Month,sum(Points) from TABLE where Status = "Done" and Completion = "Done" group by Month ; & select Month,sum(Points)from TABLE group by Month ;   ... View more

So my data structure has four columns: "Month", "Status", "Accepted", "Value". As the title suggest I'm trying to determine two things: 1) the total values per month, 2) the total values per month where "Status=Done and Accepted = Done". I have the query working for the first one but I'm having difficulty with the second. Does anyone have any ideas how to do this? Thanks!   index=index |table Month, Status, Accepted, Value | eval _time=strptime(scan_date,"%Y-%m-%d") | stats sum(Value) as total_value_per_month by Month   ... View more

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) ... View more

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'. I'm trying to show a time-chart that shows the average count per month of reports that have 'Closed' and'Open'. status , along with the difference of the two (everyday). I have found how to do the difference between'Open' and 'Closed'. But I cannot figure out how to find the monthly average for 'Open' and 'Closed', does anyone have any ideas how to query this?      index=blah... | timechart span=1mon count(report_date) by status | eval difference=abs(OPEN - CLOSED)       This is where I'm stuck, how do I get the monthly average of the particular status fields? ... View more

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'. I'm trying to show a time-chart that shows the count per day of reports that have 'Closed' and 'New' status , along with the difference of the two (everyday). So a file with report_date '2020-07-23' is ingested in Splunk and shows we had 5 'New' reports, 7 'Closed' Reports, so the difference should be 2 for that day. How do I go about doing this in my search query?   index=blah... | timechart count(report_date) by status| fields - OPEN   This is where I'm stuck, how do I get the difference of only NEW and CLOSED included into my graph. Thanks in advance ... View more