topic Re: Transaction search and appendcols - missing cols in Splunk Search

Transaction search and appendcols - missing cols

RocIngersol — Tue, 29 Sep 2020 13:00:22 GMT

Hey folks,

I have two separate searches that work fine and return the expected results. I.e.

2-

index=blah field1!=this field2!=that field3!=stuff TICKNUM>=1 |stats count by FIELD1 FIELD2

When I add the second search as an appendcols I notice that some of the counts are blank / missing. I suspect my appendcols isn't joining properly.. I also tried to create a dummy common field (eval = FIELD1+FIELD2) in both searches in the hope that they would be used as the join but no success..

thx!

Re: Transaction search and appendcols - missing cols

somesoni2 — Tue, 21 Feb 2017 21:50:07 GMT

Try like this (it can be done via join as well but they are expensive so, try this append-stats alternative of join)

index=blah field1!=this field2!=that field3!=stuff TICKNUM>=1 | bucket span=1d _time |transaction FIELD1 FIELD2 | stats avg(duration) as Avg_Duration by FIELD1 FIELD2 | some more time stuff | table FIELD1 FIELD2 Avg_Duration
| append [search index=blah field1!=this field2!=that field3!=stuff TICKNUM>=1 |stats count by FIELD1 FIELD2 ]
| stats values(*) as * by FIELD1 FIELD2

Re: Transaction search and appendcols - missing cols

martin_mueller — Tue, 21 Feb 2017 21:51:05 GMT

appendcols is no join.

Add sum(eventcount) as count to the first stats and skip the second search entirely.

Re: Transaction search and appendcols - missing cols

RocIngersol — Tue, 21 Feb 2017 22:26:24 GMT

appendcols is no join -< that explains a lot.

Re adding that to the first stats? That doesn't work as I need to use by fields later in the search. Using stats there breaks the rest of my search.

Re: Transaction search and appendcols - missing cols

martin_mueller — Tue, 21 Feb 2017 22:28:51 GMT

Like this:

index=blah field1!=this field2!=that field3!=stuff TICKNUM>=1 
| bucket span=1d _time 
|transaction FIELD1 FIELD2 
| stats avg(duration) as Avg_Duration sum(eventcount) as count by FIELD1 FIELD2 
| some more time stuff 
| table FIELD1 FIELD2 Avg_Duration

Re: Transaction search and appendcols - missing cols

RocIngersol — Wed, 22 Feb 2017 12:57:45 GMT

Thx. What I'm trying to do is have stats count by FIELD1 FIELD2 appended as a column, seperate to the transaction stuff. Doing the sum(eventcount) is just totalling the bucketed 1d _time buckets, not all the actual occurrences from what I can see..

Re: Transaction search and appendcols - missing cols

martin_mueller — Wed, 22 Feb 2017 15:25:47 GMT

The eventcount field should have the count of events per transaction, and the sum of that should be your overall count of events. The bucket shouldn't have any effect because you're not grouping by time in the stats.

Re: Transaction search and appendcols - missing cols

RocIngersol — Wed, 22 Feb 2017 16:05:18 GMT

Trying that --

stats avg(duration) as Avg_Duration sum(eventcount) as count by FIELD1 FIELD2

sum(eventcount) is never the sum - it's always the value of event count....

tried a table at the end too. I'll keep poking..

Re: Transaction search and appendcols - missing cols

RocIngersol — Wed, 22 Feb 2017 16:56:05 GMT

Sorry - I meant to say (!!) I do a dedupe on _time after my bucket 1d. That explains why sum(event count) doesn't work....

Re: Transaction search and appendcols - missing cols

martin_mueller — Wed, 22 Feb 2017 19:18:34 GMT

I see... no way to know what you left out of your question 😛

Alternatively, you can do this:

... | eventstats count by FIELD1 FIELD2 | ... blah blah dedup whatever ... | stats avg(duration) first(count) by FIELD1 FIELD2 | ...

It won't be that efficient, but in the context of transaction that won't matter much.

Re: Transaction search and appendcols - missing cols

RocIngersol — Wed, 22 Feb 2017 22:40:05 GMT

I know - my bad - leaving out a killer _time dedup !! Anyway, eventstats - that worked a charm. Thanks a lot. I should probably drop transaction in favour or stats and eventstats too - but that is for another day 🙂

Thx again! R

Re: Transaction search and appendcols - missing cols

RocIngersol — Wed, 22 Feb 2017 22:41:38 GMT

Thanks a lot for your comments - your append worked but I opted for the eventstats method instead. Thx!

Re: Transaction search and appendcols - missing cols

martin_mueller — Wed, 22 Feb 2017 23:23:48 GMT

When you do head that way, check out the March 2016 session from http://wiki.splunk.com/Virtual_.conf for more.