topic Re: Stats DC With Table in Splunk Search

Stats DC With Table

IRHM73 — Tue, 20 Oct 2015 05:53:04 GMT

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

I'm using the the search below to return values in a table.

auditSource="cato-filing" auditType="FilingStarted" | dedup "detail.Filing ID" | table "detail.*"

What I'd like to do is make this more efficient by changing the dedup to stats dc. Now I have used stats dc before, so I tried auditSource="cato-filing" auditType="FilingStarted" | stats dc(detail.Filing ID) By "detail.*" , but for the life of me, I can't work out how to return the table of results, because my effort returns no results.

I just wondered whether someone may be able to look at this please and let me know where I've gone wrong.

Many thanks and kind regards

Chris

Re: Stats DC With Table

MuS — Tue, 20 Oct 2015 08:39:36 GMT

Hi IRHM73,

two things that I can think of:

auditSource="cato-filing" auditType="FilingStarted" | stats dc(detail.Filing ID) By "detail.*" will not work because the dc(detail.Filing ID) contains a space and should be dc("detail.Filing ID")
auditSource="cato-filing" auditType="FilingStarted" | stats dc(detail.Filing ID) By "detail.*" will not work because by "detail.*" contains a wild card; take a field which is unique like detail.foo or detail.baz

You could try auditSource="cato-filing" auditType="FilingStarted" | stats dc("detail.Filing ID") BY "detail.Filing ID" | table "detail.Filing ID" or just use dedup .

Hope this helps ... and this is un-tested 😉

cheers, MuS

Re: Stats DC With Table

IRHM73 — Tue, 20 Oct 2015 08:56:41 GMT

Hi @MuS thank you for coming back to me with this. I'm trying to get away from dedup because of it's over use of resources.

I have tried the query you kindly provided and I appreciate that this was untested, but unfortunately this doesn't work.

However I've found that rather than using "detail.*" I can use the fields:

detail.CompanyName
detail.Department
detail.Location

I'm not sure whether that helps.

Many thanks and kind regards

Chris

Re: Stats DC With Table

immortalraghava — Tue, 20 Oct 2015 11:56:37 GMT

As per my understanding from your question, you could rename before stats something like this.

| rename "detail.Filing ID" as FilingID | stats dc(FilingID)

Hope this helps !

Re: Stats DC With Table

IRHM73 — Tue, 20 Oct 2015 12:00:00 GMT

Hi @immortalraghaven,

Thank you for taking the time to reply to my post.

Unfortunately the rename won't work because I'm trying to use a 'stats dc' for multiple fields as per my last post to @MuS.

Many thanks and kind regards

Chris

Re: Stats DC With Table

woodcock — Tue, 20 Oct 2015 15:10:58 GMT

What makes you think that dedup is inefficient? What makes you think that dedup is any different from stats latest(_raw) AS _raw (which will be pretty much the same work as stats dc)?

Re: Stats DC With Table

woodcock — Tue, 20 Oct 2015 15:11:26 GMT

Like this:

 auditSource="cato-filing" auditType="FilingStarted" | stats values(detail.Filing ID)

Re: Stats DC With Table

somesoni2 — Tue, 20 Oct 2015 16:20:22 GMT

Try something like this

auditSource="cato-filing" auditType="FilingStarted"  | table "detail.*" | stats latest(*) as * by "detail.Filing ID"

This will do same function as dedup

Re: Stats DC With Table

woodcock — Tue, 20 Oct 2015 17:01:22 GMT

To make it more flexible to cover more fields, you can do this:

auditSource="cato-filing" auditType="FilingStarted" | stats values(detail.*)

Re: Stats DC With Table

alacercogitatus — Tue, 20 Oct 2015 19:32:20 GMT

dedup compared to a stats dc by is indeed less efficient. When using dedup most (if not all) of the _raw events must be returned to the search head. When using stats, only the dc and the by clause field are returned to the search head - which gives a better performance when re-assembling on the search head to do the final stats.

Re: Stats DC With Table

MuS — Tue, 20 Oct 2015 20:15:20 GMT

Just for the fun of it, I ran all searches as run everywhere examples with fixed time range and leave you to choose the fastest/best for your use case:

index=_internal earliest=-2d@d latest=-1d@d | dedup sourcetype | table sourcetype
This search has completed and has returned 8 results by scanning 2,907,591 events in 249.892 seconds.
index=_internal earliest=-2d@d latest=-1d@d | stats dc(sourcetype) by sourcetype
This search has completed and has returned 8 results by scanning 2,907,591 events in 229.72 seconds.
index=_internal earliest=-2d@d latest=-1d@d | stats latest(sourcetype) by sourcetype
This search has completed and has returned 8 results by scanning 2,907,591 events in 228.705 seconds.
index=_internal earliest=-2d@d latest=-1d@d | table sourcetype | stats latest(*) as * by sourcetype
This search has completed and has returned 8 results by scanning 2,907,591 events in 382.519 seconds.
index=_internal earliest=-2d@d latest=-1d@d | table sourcetype | stats values(sourcetype)
This search has completed and has returned 1 result by scanning 2,907,591 events in 388.672 seconds.

cheers, MuS

Re: Stats DC With Table

IRHM73 — Wed, 21 Oct 2015 05:20:09 GMT

Hi @somesoni2, thank you for taking the time to my post.

I've tried the query you kindly provided but it doesn't work. Rather than displaying the "detail.*" fields it just displays the "detail.Filing ID".

Many thanks and kind regards

Chris

Re: Stats DC With Table

IRHM73 — Wed, 21 Oct 2015 05:43:26 GMT

Hi @Mus, thank you very much for this.

It provide some interesting reading.

Kind Regards

Chris

Re: Stats DC With Table

IRHM73 — Wed, 21 Oct 2015 05:48:52 GMT

Hi @woodcock, thank you for taking the time to reply to my post.

I've tried the query you kindly provided, and although it does extract all the fields which is great, rather than each record being on it's own row, they are all contained within one.

Many thanks and kind regards

Chris

Re: Stats DC With Table

woodcock — Wed, 21 Oct 2015 14:06:53 GMT

OK, then just add this:

... | foreach values* [ mvexpand <<FIELD>> ]

Re: Stats DC With Table

MuS — Wed, 21 Oct 2015 20:28:22 GMT

just to be complete, here is the latest command ran on the same server as yesterday:

index=_internal earliest=-3d@d latest=-2d@d | stats values(sourcetype) | foreach values* [ mvexpand <<FIELD>> ]
This search has completed and has returned 8 results by scanning 2,907,591 events in 248.982 seconds.

Re: Stats DC With Table

alacercogitatus — Wed, 21 Oct 2015 21:27:55 GMT

So just to throw in my two cents:

auditSource="cato-filing" auditType="FilingStarted"  | rename detail.* as * | stats dc("Filing ID") as "Filing ID" by CompanyName Department Location

Re: Stats DC With Table

IRHM73 — Thu, 22 Oct 2015 05:13:24 GMT

HI @alacercogitatus, thank you for taking the time to reply to my post.

I tried the query you kindly provided, but unfortunately this create a table.

Many thanks and kind regards

Chris

Re: Stats DC With Table

IRHM73 — Thu, 22 Oct 2015 05:16:44 GMT

Hi @woodcock, thank you for coming back to me with this, but unfortunately this doesn't change the layout of the results.

I think I'll have to say with the 'dedup' because I think what I want to achieve isn't possible.

Many thanks and kind regards

Chris

Re: Stats DC With Table

IRHM73 — Thu, 22 Oct 2015 05:18:46 GMT

Hi, thank you very much for this.

I think I'll have to stay with the dedup because what I want to achieve doesn't seem possible.

Kind Regards

Chris