topic Re: Count and sum fourth column if second and third column are certain value and group by first column in Splunk Search

How to count and sum fourth column if second and third column are certain value and group by first column?

Username1 — Thu, 06 Aug 2020 23:35:51 GMT

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

Re: Count and sum fourth column if second and third column are certain value and group by first column

thambisetty — Thu, 06 Aug 2020 15:49:11 GMT

index=index Status=Done Accepted=Done

| stats sum(Value) by Month

Re: Count and sum fourth column if second and third column are certain value and group by first column

Username1 — Thu, 06 Aug 2020 15:52:51 GMT

Hi @thambisetty Thanks for responding! If i do that how will I count where Status = 'Not Done" by Month?

Re: Count and sum fourth column if second and third column are certain value and group by first column

thambisetty — Thu, 06 Aug 2020 15:57:06 GMT

I believe you are looking for different query?

If yes,

index=index Status=“not done”

| stats sum(Value) by Month.

up vote if it solves your problem.

Re: Count and sum fourth column if second and third column are certain value and group by first column

Username1 — Thu, 06 Aug 2020 15:58:20 GMT

@thambisetty Is there no way I can have one query? The end goal is to have a visual

Re: Count and sum fourth column if second and third column are certain value and group by first column

thambisetty — Thu, 06 Aug 2020 16:02:45 GMT

Write down all your cases to combine. We need to use Case statement.

for example:

if Status=Done Accepted=Done -> Done

if status =“Not Done” -> “Not Done”

and expected output in table format.

Re: Count and sum fourth column if second and third column are certain value and group by first column

Username1 — Thu, 06 Aug 2020 16:07:24 GMT

@thambisetty So my data structure has four columns: "Month", "Status", "Accepted", "Value". The two things I'm trying to graph are: 1) the total values per month no matter what Status/Accepted, 2) the total values per month where "Status=Done and Accepted = Done".

Re: Count and sum fourth column if second and third column are certain value and group by first column

thambisetty — Thu, 06 Aug 2020 16:13:31 GMT

index=index

| eval done=if(Status=“Done” AND Accepted=“Done”,”1”,”0”)

| stats sum(Value) as all , sum(done) as done by Month

Re: Count and sum fourth column if second and third column are certain value and group by first column

Username1 — Thu, 06 Aug 2020 16:48:18 GMT

@thambisetty I tried running that and it returned "Error in 'eval' command: The expression is malformed. An unexpected character is reached at '”1”,”0”)'."

If I do that how would the `done` eval be able to sum all of the Values, as Values range from .25 - 10

Re: Count and sum fourth column if second and third column are certain value and group by first column

thambisetty — Thu, 06 Aug 2020 16:56:31 GMT

index=index

| eval done=if(Status=“Done” AND Accepted=“Done”,”1”,”0”)

| stats sum(Value) as all , sum(done) as done by Month

replace double quotes with double quotes from your key board.

your query is not clear.

Re: Count and sum fourth column if second and third column are certain value and group by first column

thambisetty — Thu, 06 Aug 2020 19:50:31 GMT

Check this and upvote if it solves your problem.

Re: Count and sum fourth column if second and third column are certain value and group by first column

kmorris_splunk — Thu, 06 Aug 2020 20:11:06 GMT

index=YOURINDEX sourcetype=YOURSOURCETYPE | stats sum(Value) as TotalValue by Month | append [ searchindex=YOURINDEX sourcetype=YOURSOURCETYPE Status="Done" Accepted="Done" | stats sum(Value) as TotalVal_Done by Month] | stats values(TotalValue) as TotalValue values(TotalVal_Done) as TotalVal_Done by Month