topic Re: Can you help me with the where command? in Splunk Search

Can you help me with the where command?

jip31 — Fri, 01 Mar 2019 15:15:22 GMT

Hello,

I use the seatrch below

index="*" sourcetype="*"
| eval Boot_Duration=coalesce('Durée du démarrage      ','Boot Duration        ','Startdauer       ','Duración del arranque       ') 
| dedup host 
| stats count by host

Boot_Duration is a number value.

I want to check only the number values >100000

So I do

| where Boot_Duration>100000

But it doesn't work.

Could you help me please??

Re: Can you help me with the where command?

tiagofbmm — Fri, 01 Mar 2019 15:21:11 GMT

You need to keep the boot duration on your stats command

| stats count, values(Boot_Duration) as Boot_Duration by host

Then you can your | where Boot_Duration>100000

Re: Can you help me with the where command?

nickhills — Fri, 01 Mar 2019 15:23:06 GMT

Try:

index="" sourcetype=""
| eval Boot_Duration=coalesce('Durée du démarrage ','Boot Duration ','Startdauer ','Duración del arranque ') 
| rex field=Boot_Duration (?P<Boot_Duration>^\d+)
| search Boot_Duration>=100000
| dedup host 
| stats count by host

Your stats command 'looses' the Boot Duration field, so you either need to filter it before the stats, or bring the field through after your stats command has produced results

Re: Can you help me with the where command?

jip31 — Mon, 04 Mar 2019 07:29:56 GMT

tiago
it doesnt works
I think its because the boot duration has a value no?
for example : 100000ms

Re: Can you help me with the where command?

kamlesh_vaghela — Mon, 04 Mar 2019 07:33:23 GMT

@jip31

Can you please share sample events?

Re: Can you help me with the where command?

woodcock — Mon, 04 Mar 2019 10:30:10 GMT

Try this (stop using dedup and surely your fields do not really have all of those spaces in them, do they?):

index="*" sourcetype="*"
| eval Boot_Duration=coalesce('Durée du démarrage',' Boot Duration', 'Startdauer', 'Duración del arranque')
| where Boot_Duration>100000
| stats count by host

Re: Can you help me with the where command?

nickhills — Mon, 04 Mar 2019 10:48:07 GMT

responding to a comment above:

"wait - Boot_Duration includes 'ms' too?
This means its a string, not a number, and, you can't perform arithmetic logic on a string!"

Re: Can you help me with the where command?

nickhills — Mon, 04 Mar 2019 10:54:55 GMT

I have edited the answer above to work if your duration field includes a unit of time (like 'ms')
This works by taking just the digits and stripping any letters from the field, so you can perform math comparisons on the field.

Re: Can you help me with the where command?

jip31 — Mon, 04 Mar 2019 14:08:20 GMT

same problem...
and unfortunately there is the space in the fields...

Re: Can you help me with the where command?

jip31 — Mon, 04 Mar 2019 14:11:42 GMT

You mean that i have to do something like this :
| where Boot_Duration>100000. "ms" ???

Re: Can you help me with the where command?

tiagofbmm — Mon, 04 Mar 2019 14:12:54 GMT

@jip31 can you paste here a couple of your events examples please ?

Re: Can you help me with the where command?

tiagofbmm — Mon, 04 Mar 2019 14:18:36 GMT

Use rex to extract only the number part of the field and then use Boot_Duration>1000

Re: Can you help me with the where command?

nickhills — Tue, 29 Sep 2020 23:33:10 GMT

No - try my example above.
In this case I am performing a regex on your Boot_Duration field - I read the entire field contents which I (now) assume contains something like "100929ms"
After the rex command, Boot_Duration will now only contain numbers ie "100929". Because this field is now numeric, you can perform <> operations on the values.

Re: Can you help me with the where command?

jip31 — Tue, 05 Mar 2019 10:23:24 GMT

hi I have added the events in attachment

Re: Can you help me with the where command?

woodcock — Wed, 06 Mar 2019 08:04:50 GMT

You need to pass Boot_Duration through the stats so try using this instead:

| stats count, max(Boot_Duration) as Boot_Duration BY host

Or perhaps you would prefer avg() or min() over max().

Re: Can you help me with the where command?

jip31 — Thu, 07 Mar 2019 07:40:59 GMT

thanks woodcok but i prefer to use the code of nickhillscpl because I need a regex in order to extract "ms" after the boot duration....

Re: Can you help me with the where command?

jip31 — Thu, 07 Mar 2019 07:42:41 GMT

Thanks a lot