Splunk Search

AddcolsTotals & Where Statement

IRHM73
Motivator

Hi, I wonder whether someone may be able to help me please.

I'm using the query below which calcluates the difference between two for a group of users:

`tcs_wmf(misFullReceived)` OR (`submissions_wmf(Submission)` detail.changeType=EndAJob)
| fields detail.rawData detail.id detail.endDate
| transaction detail.id endswith="EndAJob" maxspan=2h 
| spath input=detail.rawData output=jobs path=applicant1.PaidJobs
| rename detail.endDate as empend detail.id as ID
| stats count by jobs ID _time empend
| where jobs=1 
| eval eventtime=_time, endofjob= strptime('empend', "%Y-%m-%d"), processedtime=_time, duration=(processedtime-endofjob)/86400, totaldays=round(duration,0)
| convert ctime(_time) as timestamp timeformat="%d/%m/%y"
| fields - _time
| fields empend timestamp totaldays nino
| addcoltotals totaldays labelfield=timestamp label="Total Count 28 Days or Less"

The difficulty I'm having is in the final line of code.

I'd like to use the 'Column Total' to only count the rows where the "Total Days" row value is 28 or less but I'm at a bit of a loss about how to do this.

I just wondered whether someone may be able to offer some guidance on how I may go about this.

Many thanks and kind regards

Chris

0 Karma

kmaron
Motivator

instead of addcoltotals try appendpipe

 | appendpipe [stats sum(eval(if((totaldays<28), totaldays,0))) AS "Total Count 28 Days or Less"]
0 Karma

IRHM73
Motivator

Hi @kmaron. Thank you for taking the time to come back to me with this.

I've had a look at this, but unfortunately I think I'm right in saying that the 'appendpipe' results are restricted to 50,000 rows?

Many thanks and kind regards

Chris

0 Karma

elliotproebstel
Champion

If you want to add a row at the bottom of your results that counts the number of events with totaldays<=28 and put a label of "Total Count 28 Days or Less" into the timestamp field, this should do it:

your current search
| appendpipe 
 [| stats count(eval(totaldays<=28)) AS totaldays 
  | eval timestamp="Total Count 28 Days or Less" ]
0 Karma

IRHM73
Motivator

Hi @elliotproebstel, thank you for taking the time to come back to me with this. But am I right in thinking the results are restricted to 50,000 rows?

Many thanks and kind regards

Chris

0 Karma

elliotproebstel
Champion

Hmm, I would expect that might be a default number of rows to be returned and wouldn't have thought it would limit the number of rows analyzed to produce the result, but you could certainly be right.

0 Karma

mayurr98
Super Champion

You can try something like this

Try this run anywhere search

| makeresults 
| eval int="1 2 3 4 5 6 7 8 9 10 30 33 232 646 85" 
| makemv int 
| mvexpand int 
| eventstats sum(eval(if((int<28), int,0))) AS "Total Count 28 Days or Less"

Try this in your current search

`tcs_wmf(misFullReceived)` OR (`submissions_wmf(Submission)` detail.changeType=EndAJob) 
| fields detail.rawData detail.id detail.endDate 
| transaction detail.id endswith="EndAJob" maxspan=2h 
| spath input=detail.rawData output=jobs path=applicant1.PaidJobs 
| rename detail.endDate as empend detail.id as ID 
| stats count by jobs ID _time empend 
| where jobs=1 
| eval eventtime=_time, endofjob= strptime('empend', "%Y-%m-%d"), processedtime=_time, duration=(processedtime-endofjob)/86400, totaldays=round(duration,0) 
| convert ctime(_time) as timestamp timeformat="%d/%m/%y" 
| fields - _time 
| fields empend timestamp totaldays nino 
| eventstats sum(eval(if((totaldays<28), totaldays,0))) AS "Total Count 28 Days or Less"

let me know if this helps!

0 Karma

IRHM73
Motivator

Hi @mayurr98, thank you for coming back to me with this.

Unfortunately it doesn't work as I'd hoped.

There is a an extra column for the eventstats with the total in wach row, rather than one total at the end of the column.

In addition, it was also summing the totaldays values, rather than counting. So I did try to changing sum to count, but this doesn't work either.

Many thanks and kind regards

Chris

0 Karma

mayurr98
Super Champion

yeah unfortunately eventstats works in that way, it will show you results at each row and not only at the end of the table
if you want just count then you should try this

| eventstats count(eval(case((int<28),int))) AS "Total Count 28 Days or Less"
0 Karma

IRHM73
Motivator

Hi, thank you for coming back to me with this.

Unfortunately though it's not counting any totals now.

Many thanks and regards

Chris

0 Karma

valiquet
Contributor

Do you want to remove row were > 28?
If yes
| search totaldays < 29

0 Karma

IRHM73
Motivator

Hi @valiquet, I'm sorry for perhaps not explainig sufficiently.

But I'd like to shwo all the values in the table of results, but only count those where the value is 28 or less.

I hope this helps.

Many thanks and kind regards

Chris

0 Karma

tiagofbmm
Influencer

Hi

Before the last line (| addcoltotals), why don't you filter only the rows that fall within the totaldays<28.

 | fields - _time
 | fields empend timestamp totaldays nino
| where totaldays<29
| addcoltotals totaldays labelfield=timestamp label="Total Count 28 Days or Less"

Maybe I'm not fully understanding your issue here. Let me know please

0 Karma

IRHM73
Motivator

Hi @tiagofbmm, I'm sorry perhaps my explanation was insufficient.

I still want to show all the results but for the count to only count those out of the list which have a value of 28 or less.

I hope this helps.

Many thanks and kind regards

Chris

0 Karma

tiagofbmm
Influencer

Ok so did you try what I suggested?

I think it does count you only the rows that have value 28 or less

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

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

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...