Splunk Search

Exclude result like the first result

Explorer

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.

0 Karma
1 Solution

Legend

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

View solution in original post

0 Karma

Legend

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

View solution in original post

0 Karma

Explorer

That's awesome, thank you sundareshr

0 Karma

SplunkTrust
SplunkTrust

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
0 Karma

Explorer

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

0 Karma

Motivator

Hi there inventsekar,

Perhaps dedup command can help you with this.

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

Hope it helps.

0 Karma

Champion

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?

0 Karma

Explorer

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

0 Karma