Splunk Search

Stats Values and Count

Motivator

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

0 Karma
1 Solution

Motivator

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)

View solution in original post

0 Karma

Motivator

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)

View solution in original post

0 Karma

Super Champion

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?

0 Karma

Motivator

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

0 Karma

SplunkTrust
SplunkTrust

Try coalesce.

...
| search given="true"
| eval ITREF=coalesce(ITREF1,ITREF2)
| stats count values(auditSource) as auditSource values(given) as given by ITREF
...
---
If this reply helps you, an upvote would be appreciated.

Motivator

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

0 Karma