Transaction search and appendcols - missing cols

[RocIngersol]

Hey folks,

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

1 -
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

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!

[martin_mueller · Accepted solution]

appendcols is no join.

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

[martin_mueller]

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

[martin_mueller]

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.

[RocIngersol]

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

[martin_mueller]

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.

[RocIngersol]

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..

[RocIngersol]

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

[martin_mueller]

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

[RocIngersol]

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..

[RocIngersol]

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.

[somesoni2]

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

[RocIngersol]

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