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

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

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

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

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

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

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

Check this and upvote if it solves your problem.

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

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...