Exclude result like the first result

[dineshp]

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.

[sundareshr · Accepted solution]

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")

[dineshp]

That's awesome, thank you sundareshr

[somesoni2]

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

[dineshp]

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

[alemarzu]

Hi there inventsekar,

Perhaps dedup command can help you with this.

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

Hope it helps.

[inventsekar]

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?

[dineshp]

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