topic Stats Values and Count in Splunk Search

Stats Values and Count

IRHM73 — Thu, 13 Jul 2017 08:15:53 GMT

Hi, I wonder if someone could help me please.

I'm trying to 'join' two queries using the 'stats values' for efficiency purposes.

This is the query I've put together so far:

| multisearch
[ search `it_wmf(OutboundCall)`]
[ search `it_wmf(RequestReceived)` detail.responseMessage!=""]
| spath output=ITREF1 input=detail.responseMessage path=itRef
| spath output=given input=detail.responseMessage path=allGifts.isGivenAway
| rex field=request.detail.path "cases\/(?<ITREF2>[^\W]+)\/"
| search given="true"
| stats count values(auditSource) as auditSource values(given) as given by ITREF1, ITREF2
| where (auditSource="it" AND auditType=OutBoundCall AND auditType=RequestReceived)

I'm trying to join the queries on the ITREF's but because I've had to give these two different fieldnames i.e ITREF1 and ITREF2 I'm not sure how to join them.

In addition I'd then like to create a distinct count of the ITREF.

I just wondered whether someone could look at this please and offer some guidance on how I may achieve this?

Many thanks and kind regards

Chris

Re: Stats Values and Count

richgalloway — Thu, 13 Jul 2017 11:38:30 GMT

Try coalesce.

...
| search given="true"
| eval ITREF=coalesce(ITREF1,ITREF2)
| stats count values(auditSource) as auditSource values(given) as given by ITREF
...

Re: Stats Values and Count

cmerriman — Thu, 13 Jul 2017 11:39:06 GMT

if before your stats command you create a field |eval ITREF=coalesce(ITREF1,ITREF2) and use the new ITREF field as the by arguement | stats count values(auditSource) as auditSource values(given) as given by ITREF, would that work?

Re: Stats Values and Count

IRHM73 — Thu, 13 Jul 2017 12:15:50 GMT

Hi, please see my solution below. I found out that because I was extracting the ITREF field with separate methods I could allocate the same field name to them.

Many thanks for taking the time to reply.

Kind Regards

Chris

Re: Stats Values and Count

IRHM73 — Thu, 13 Jul 2017 12:17:04 GMT

Hi, thank you for taking the time to reply. I actually found out that because I am extracting the "ITREF" field using different methods, I can use the stats values command and it pulls the two together.

I have included by solution on a separate post.

Kind Regards

Chris

Re: Stats Values and Count

IRHM73 — Thu, 13 Jul 2017 12:18:03 GMT

This was the working solution I cam up with:

| multisearch
[ search `it_wmf(OutboundCall)`]
[ search `it_wmf(RequestReceived)` detail.responseMessage!=""]
| spath output=ITREF input=detail.responseMessage path=itRef
| spath output=given input=detail.responseMessage path=allGifts.isGivenAway
| rex field=request.detail.path "cases\/(?<ITREF>[^\W]+)\/"
| stats count values(auditSource) as auditSource values(auditType) as auditType values(given) as given by ITREF
| search given="true"
| where (auditType="OutboundCall" AND auditType="RequestReceived")
| stats dc(ITREF)