topic Re: AddcolsTotals & Where Statement in Splunk Search

AddcolsTotals & Where Statement

IRHM73 — Fri, 09 Mar 2018 08:17:50 GMT

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

Re: AddcolsTotals & Where Statement

tiagofbmm — Fri, 09 Mar 2018 08:25:20 GMT

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

Re: AddcolsTotals & Where Statement

valiquet — Fri, 09 Mar 2018 08:25:50 GMT

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

Re: AddcolsTotals & Where Statement

IRHM73 — Fri, 09 Mar 2018 08:32:54 GMT

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

Re: AddcolsTotals & Where Statement

IRHM73 — Fri, 09 Mar 2018 08:34:49 GMT

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

Re: AddcolsTotals & Where Statement

tiagofbmm — Fri, 09 Mar 2018 08:36:36 GMT

Ok so did you try what I suggested?

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

Re: AddcolsTotals & Where Statement

mayurr98 — Fri, 09 Mar 2018 09:24:06 GMT

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!

Re: AddcolsTotals & Where Statement

IRHM73 — Fri, 09 Mar 2018 09:46:10 GMT

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

Re: AddcolsTotals & Where Statement

mayurr98 — Fri, 09 Mar 2018 10:12:21 GMT

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"

Re: AddcolsTotals & Where Statement

IRHM73 — Fri, 09 Mar 2018 10:46:20 GMT

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

Unfortunately though it's not counting any totals now.

Many thanks and regards

Chris

Re: AddcolsTotals & Where Statement

elliotproebstel — Fri, 09 Mar 2018 14:20:36 GMT

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" ]

Re: AddcolsTotals & Where Statement

kmaron — Fri, 09 Mar 2018 16:20:58 GMT

instead of addcoltotals try appendpipe

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

Re: AddcolsTotals & Where Statement

IRHM73 — Mon, 12 Mar 2018 07:49:16 GMT

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

Re: AddcolsTotals & Where Statement

IRHM73 — Mon, 12 Mar 2018 07:51:23 GMT

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

Re: AddcolsTotals & Where Statement

elliotproebstel — Tue, 13 Mar 2018 00:56:07 GMT

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.