topic Re: Exclude result like the first result in Splunk Search

Exclude result like the first result

dineshp — Mon, 12 Sep 2016 03:11:29 GMT

Hi,
I want to identify the available and occupied resources in a pool. The active resource will have "Available" on the log and the occupied resource will have "taken" in the pool.
(only 1 set of resource is always available and one set should always be taken i.e Laptop1 and Laptop2 cannot be available at the same time)

Now when i do,
source=sdesktop "Available" | stats count by resource, _time
resource _time
Laptop1 2016-09-12 12:04:14
Laptop2 2016-09-12 10:00:45
Projector2 2016-09-11 03:13:04
Projector1 2016-09-06 11:17:50

I want following result,

resource _time
Laptop1 2016-09-12 12:04:14
Projector2 2016-09-11 03:13:04

I tried source=sdesktop "Available" | stats count by resource, _time | sort - _time | head 2
resource _time
Laptop1 2016-09-12 12:04:14
Laptop2 2016-09-12 10:00:45

Please help.

Thank you.

Re: Exclude result like the first result

inventsekar — Mon, 12 Sep 2016 10:22:44 GMT

Hi Dinesh, can you please update us what is this "_time" clearly.. is it the device available "from time" ?
Laptop1 2016-09-12 12:04:14
Laptop2 2016-09-12 10:00:45
Projector2 2016-09-11 03:13:04
Projector1 2016-09-06 11:17:50

from the 4 available devices, why you want these two devices(laptop1, projector2)?

are there any other field values which will differentiate the laptops and projectors?
are there any group names for laptops together?
can you please give us the real devices names please?

Re: Exclude result like the first result

alemarzu — Mon, 12 Sep 2016 12:43:27 GMT

Hi there inventsekar,

Perhaps dedup command can help you with this.

source=sdesktop "Available" | dedup resource | table resource _time

Hope it helps.

Re: Exclude result like the first result

dineshp — Tue, 29 Sep 2020 11:00:16 GMT

Hi inventsekar,
Thank you for your reply. "_time" is the time when a resource gets free. I sorted the result with "_time" so i can grab the top two results.
doing head on the result gives me Laptop1 & Laptop2. But what i want is to grab one laptop and one projector after sorting them by time.
Is there a way we can say "exclude the result similar to the first result" so anything that looks like "Laptop*" gets discarded and we only get projector.
I found a work around using append command and running the search twice.
*source=sdesktop "Available" Laptop| stats count by resource, _time | sort - _time | head 1 | append [search source=sdesktop "Available" Projector*| stats count by resource, _time | sort - _time | head 1 ]
Result:
resource _time
Laptop1 2016-09-12 12:04:14
Projector2 2016-09-11 03:13:04

Re: Exclude result like the first result

somesoni2 — Mon, 12 Sep 2016 22:01:35 GMT

Give this a try

source=sdesktop "Available" resource=Laptop* OR resource=Projector* | eval Type=if(match(resource,"Laptop"),"Laptop","Projector") | dedup Type | table resource _time

Re: Exclude result like the first result

dineshp — Mon, 12 Sep 2016 23:57:07 GMT

hi Somesoni2,
I tried your query, its only giving back "Laptop1"

Re: Exclude result like the first result

sundareshr — Tue, 13 Sep 2016 00:26:27 GMT

Try this

 source=sdesktop "Available" resource=Laptop* OR resource=Projector* | eval Type=if(match(resource, "Laptop"), "Laptop", "Projector") | stats latest(_time) as Time  last(resource) as Resource by Type | table Resource Time | eval Time=strftime(Time, "%x %X")

Re: Exclude result like the first result

dineshp — Tue, 13 Sep 2016 00:48:16 GMT

That's awesome, thank you sundareshr