Splunk Search

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

Path Finder

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``````

Labels (5)

• stats

1 Solution
Super Champion

Check this and upvote if it solves your problem.

————————————
If this helps, give a like below.
Splunk Employee
``````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``````
Super Champion

index=index Status=Done Accepted=Done

| stats sum(Value) by Month

————————————
If this helps, give a like below.
Path Finder

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

Super Champion

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.

————————————
If this helps, give a like below.
Path Finder

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

Super Champion

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.

————————————
If this helps, give a like below.
Path Finder

@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".

Super Champion

index=index

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

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

————————————
If this helps, give a like below.
Path Finder

@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

Super Champion

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.

————————————
If this helps, give a like below.
Super Champion

Check this and upvote if it solves your problem.

————————————
If this helps, give a like below.
Get Updates on the Splunk Community!

Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...