Stats Values and Count

[IRHM73]

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

[IRHM73 · Accepted solution]

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)

[cmerriman]

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?

[IRHM73]

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

[richgalloway]

Try coalesce.

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

[IRHM73]

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