topic Re: Should I use Lookup or mvexpand in the following search? in Splunk Search

Should I use Lookup or mvexpand in the following search?

tb5821 — Mon, 03 Dec 2018 20:18:35 GMT

I have a search that returns a list of namespace values.

I want to take each one of those namespace values and run streamstats on it by doing a ...|search namespace=<namespace> | streamstats...

I tried doing a by namespace in my streamstats, but for some reason, it doesn't work and the only way it seems to work is with the pre-search by a single namespace ahead of time...

How do I accomplish this?

current search

source="/var/log/lag/stats.txt" d=* 
| eval namespace=trim(replace(namespace,"sample-text.",""))
| eval Processed_time=_time
| search namespace=HeartBeat
| streamstats current=false window=500 last(count) as prev_count earliest(Processed_time) as time_of_last_change by namespace 
| where prev_count != count
| eval actualchange=prev_count-count
| streamstats current=false window=2 range(Processed_time) AS diffoflastchange by namespace
| eval diffoflastchange=round(diffoflastchange)
| eval changeformatted=tostring(diffoflastchange,"duration")
| stats range(diffoflastchange) as totalrange by namespace
| eval totalrangeformat=tostring(totalrange,"duration")

Sure thing! events are really super basic....

d=12/14/18 02:15:01 PM UTC namespace=Sample,count=5400315
d=12/14/18 02:18:01 PM UTC namespace=HeartBeat,count=5400610
d=12/14/18 02:21:01 PM UTC namespace=Sample,count=5400927
d=12/14/18 02:24:01 PM UTC namespace=HeartBeat,count=5400815

So I'd expect my output to be

HeartBeat Avg Update Span = Sample Avg
Update Span =

Re: Should I use Lookup or mvexpand in the following search?

richgalloway — Mon, 03 Dec 2018 22:08:26 GMT

Please provide your current search.

Re: Should I use Lookup or mvexpand in the following search?

tb5821 — Fri, 07 Dec 2018 16:42:37 GMT

updated original question

Re: Should I use Lookup or mvexpand in the following search?

richgalloway — Thu, 13 Dec 2018 11:19:47 GMT

lookup and mvexpand are very different commands, ones not typically used interchangeably. To help with your search it's important to know what your end goal is. Don't tell us what commands you want to run, tell us in normal language what results you want. Then we'll try to help you get there.

Re: Should I use Lookup or mvexpand in the following search?

tb5821 — Thu, 13 Dec 2018 14:13:17 GMT

what I want is by the field NAMESPACE to get the average timebetween updates for the last x days

Re: Should I use Lookup or mvexpand in the following search?

macadminrohit — Fri, 14 Dec 2018 05:42:27 GMT

Can you paste some sample events here ? I do similar thing in my env, calculating the difference between two similar events. I can help you with this one.

Re: Should I use Lookup or mvexpand in the following search?

tb5821 — Fri, 14 Dec 2018 14:54:21 GMT

updated main question with samples etc

Re: Should I use Lookup or mvexpand in the following search?

macadminrohit — Fri, 14 Dec 2018 15:59:14 GMT

So you want something like this?

namespace avg(Count) last(Update _time) duration(between the same namespace events)

Re: Should I use Lookup or mvexpand in the following search?

macadminrohit — Fri, 14 Dec 2018 16:36:11 GMT

Try something like this , I am not sure what is update in your final results . I am assuming your fields correspond to :

Avg=Average of count of same namespace events.
Update= ?
Span= duration between same namespace events.

| makeresults 
| eval DATA="d=12/14/18 02:15:01 PM UTC namespace=Sample count=5400315,d=12/14/18 02:18:01 PM UTC namespace=HeartBeat count=5400610,d=12/14/18 02:21:01 PM UTC namespace=Sample count=5400927,d=12/14/18 02:24:01 PM UTC namespace=HeartBeat count=5400815" 
| makemv DATA delim="," 
| mvexpand DATA 
| rex field=DATA "namespace\=(?<namespace>\w+)\scount\=(?<count>\d+)" 
| table _time count namespace 
| streamstats count as nb 
| eval _time = _time + 120*nb 
| sort 0 namespace 
| table _time namespace count 
| streamstats count as RecordNumber by namespace reset_on_change=true 
| streamstats current=f last(_time) as LastTime last(RecordNumber) As previousRecord 
| eval change = if(RecordNumber-previousRecord!=1,"Yes","No")
| eval span=case(change="No",(_time-LastTime))
| fillnull span Value=0 | eventstats avg(count) as Avg_Count by namespace

Re: Should I use Lookup or mvexpand in the following search?

macadminrohit — Fri, 14 Dec 2018 16:37:49 GMT

I have intentionally added a gap of 2 minutes between the events to test this.

Re: Should I use Lookup or mvexpand in the following search?

woodcock — Fri, 14 Dec 2018 18:13:37 GMT

There is no reason to filter at all if you can use streamstats, just make sure that you use the BY clause appropriately. You are definitely doing some things in your search that don't fit (i.e. | eval changeformatted=tostring(diffoflastchange,"duration"), which creates a field that is not used by and is discarded by the following | stats). Also, your window=500 seems misplaced. Does this do pretty much what you'd expect?

index=_* count=*
| rename sourcetype AS namespace 
| replace splunkd WITH "HeartBeat" IN namespace 
| eval Processed_time=_time 
| streamstats current=false last(count) as prev_count earliest(Processed_time) as time_of_last_change by namespace 
| where prev_count != count 
| eval actualchange=prev_count-count 
| streamstats current=false window=2 range(Processed_time) AS diffoflastchange by namespace 
| eval diffoflastchange=round(diffoflastchange) 
| stats range(diffoflastchange) as totalrange by namespace 
| eval totalrangeformat=tostring(totalrange,"duration")

Re: Should I use Lookup or mvexpand in the following search?

tb5821 — Fri, 14 Dec 2018 20:21:43 GMT

hmmm haven't tried this YET but can you explain what this is doing and how its helping?

| rename sourcetype AS namespace 
 | replace splunkd WITH "HeartBeat" IN namespace

I'm not looking at souretype as a field nor splunkd ?

Re: Should I use Lookup or mvexpand in the following search?

woodcock — Fri, 14 Dec 2018 20:33:48 GMT

I don't have your data so I munged some data that everybody has and forced it to look like your data so that I could see what your search is doing. Obviously, you don't need those lines.

Re: Should I use Lookup or mvexpand in the following search?

tb5821 — Fri, 14 Dec 2018 20:52:51 GMT

SO running this query - aside from the rename/replace commands on my statistics tab only gives me a list of namespaces with a blank column for totalrange which is the exact problem I was having earlier - thus adding a secondary search right before the streamstats command that only looks at ONE namespace will work for that one but why can't I get this to work for all of them?

Re: Should I use Lookup or mvexpand in the following search?

woodcock — Fri, 14 Dec 2018 21:47:11 GMT

But my search does not have blanks. That is the point. It must be that your data for some/all of the other namespace values do not have the fields necessary to generate the values that you are needed.

Re: Should I use Lookup or mvexpand in the following search?

cpetterborg — Fri, 21 Dec 2018 22:14:17 GMT

What do you get from just:

source="/var/log/lag/stats.txt" d=* 
 | eval namespace=trim(replace(namespace,"sample-text.",""))
 | eval Processed_time=_time
 | stats count by namespace

Are you getting all the namespace values you expect?

Re: Should I use Lookup or mvexpand in the following search?

tb5821 — Sat, 05 Jan 2019 18:15:56 GMT

Sorry, finally getting back to this!

that query above produces all the namespaces each one has the same count.

Thoughts??

Re: Should I use Lookup or mvexpand in the following search?

tb5821 — Sat, 05 Jan 2019 18:16:39 GMT

maybe adding a fillnull if thats the case? hmmm

Re: Should I use Lookup or mvexpand in the following search?

tb5821 — Sat, 05 Jan 2019 18:23:03 GMT

uhhh nope - that didn't work 😞

Re: Should I use Lookup or mvexpand in the following search?

tb5821 — Fri, 11 Jan 2019 14:23:43 GMT

following back up here @woodcock @cpetterborg