Splunk Search

Exclude result like the first result

dineshp
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

sundareshr
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

sundareshr
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")
0 Karma

dineshp
Explorer

That's awesome, thank you sundareshr

0 Karma

somesoni2
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

dineshp
Explorer

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

0 Karma

alemarzu
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

inventsekar
Ultra 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

dineshp
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...