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
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?
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?
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!
@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!