Splunk Search

Is it possible to convert a |stats values(<field>) as <field> to create a delimited csv outputlookup?

Glasses
Builder

Hi 

I am trying to speed up a query.

When I run >>>

 

index=foo | stats values(host) as F_host

 

It take less than a minute to return the results.

I want to take those results and create an outlookup and match host values against another lookup.  However I need to deliminate the stats results to individual values.   Something like, 

 

 ... | makemv delim=" " F_host | outputlookup ...

 

or maybe, 

 

... | eval D_host = split(F_host, " ") etc

 

If I run the original query >>> 

 

index=foo | lookup bar-host.csv barHost AS host OUTPUTNEW barHost as match-host | stats values(match-host) by host

 

it takes forever.   In this case, bar-host.csv is the lookup filename and barHost is the fieldname.

Maybe this is just plain old wrong, any advice appreciated...

Thank you

Labels (2)
0 Karma
1 Solution

PickleRick
Ultra Champion

Firstly, of you're creating lokup contents with outputlookup I think you'd rather want to do

index=foo
| stats values(host) as whatever
| mvexpand whatever
| outputlookup mylookup.csv

 Then - if you're doing 1-to-1 mapping and stats, why not do stats first, then use lookup?

View solution in original post

0 Karma

PickleRick
Ultra Champion

Firstly, of you're creating lokup contents with outputlookup I think you'd rather want to do

index=foo
| stats values(host) as whatever
| mvexpand whatever
| outputlookup mylookup.csv

 Then - if you're doing 1-to-1 mapping and stats, why not do stats first, then use lookup?

0 Karma

Glasses
Builder

Thank you PickleRick.

I needed the 

 

... | mvexpand 

 

hint.  I was trying other methods without luck...

And yes I want to do the stats first then the Lookups matching... I must not have explained clearly.

I will see how this works out...

TY!

0 Karma

Glasses
Builder

@PickleRick  -Yes it works very well, thank you for helping me overcome my Homer Simpson "density" "d'oh!"

 

Finally working query is >>>

 

index=foo | stats values(host) as S_Host
| mvexpand S_Host
|outputlookup Stats_Hosts.csv
|lookup WildCard-Hosts WC-Host AS S_Host OUTPUTNEW WC-Host AS Matched

 

In this case "Stats_Hosts.csv" is the result of the "stats" function, S_Host is the column header (fieldname) of Stats_Hosts.csv.   WildCard-Hosts is a static lookup list of <hostnames*> with a column header (fieldname) of WC-Host.  Matched is the resulting matched WC-Host to S_Host.

I hope that is clear, and if anyone has a better way please share.

TY!

 

0 Karma