Should I use Lookup or mvexpand in the following search?

[tb5821]

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 =

[woodcock]

This MUST work:

index=YouShoulAlwaysSpecifyIndex source="/var/log/lag/stats.txt" d=*
| eval namespace=coalesce(trim(replace(namespace,"sample-text.","")), "NO VALUE")
| 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")

[tb5821]

just to eliminate some things I took out | eval namespace=coalesce(trim(replace(namespace,"sample-text.","")), "NO VALUE") still getting just the namespaces...

and if I add namespace=sample-text.test to index=YouShoulAlwaysSpecifyIndex source="/var/log/lag/stats.txt" d=*

then I get that one namespace with the totalrangeformat and totalrange values I'd expect...

I think the problem is somewhere with the totalrange* calculations as going back to the 'must work' search that only displays all the namespaces with the totalrange column not totalrangeformat like it does when I search for just a single namespace

[tb5821]

pretty sure the problem is with the second streamstats command for some reason...

[tb5821]

You know I was really hopeful 😞 - but nope still just all the namespaces no totalrange.

[woodcock]

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")

[tb5821]

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?

[cpetterborg]

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?

[tb5821]

Sorry, finally getting back to this!

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

Thoughts??

[tb5821]

following back up here @woodcock @cpetterborg

[woodcock]

OK, let's start completely over. Run this search:

|makeresults | eval raw="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:::d=12/14/18 03:21:01 PM UTC namespace=Sample,count=5410927:::d=12/14/18 03:24:01 PM UTC namespace=HeartBeat,count=5420815"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| kv
| eval _time = strptime(_raw, "d=%m/%d/%y %I:%M:%S %p %Z")
| sort 0 - _time

Now: given these fake events, what would you like the output to be?

[woodcock]

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.

[tb5821]

maybe adding a fillnull if thats the case? hmmm

[woodcock]

No, that will not work.

[tb5821]

uhhh nope - that didn't work 😞

[tb5821]

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 ?

[woodcock]

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.

[macadminrohit]

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.

[tb5821]

updated main question with samples etc

[macadminrohit]

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 

[macadminrohit]

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