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

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

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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...