Splunk Search

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

Username1
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)
0 Karma
1 Solution

thambisetty
Super Champion

Check this and upvote if it solves your problem.

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

View solution in original post

kmorris_splunk
Splunk Employee
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
0 Karma

thambisetty
Super Champion

index=index Status=Done Accepted=Done

| stats sum(Value) by Month

————————————
If this helps, give a like below.
0 Karma

Username1
Path Finder

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

 

0 Karma

thambisetty
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.
0 Karma

Username1
Path Finder

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

 

0 Karma

thambisetty
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.
0 Karma

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

0 Karma

thambisetty
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.
0 Karma

Username1
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

0 Karma

thambisetty
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.

 

your query is not clear.

————————————
If this helps, give a like below.
0 Karma

thambisetty
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 ...