Splunk Search

Need help with having earliest command and two other stats commands in same query

Anthonylucian
Path Finder

I currently have two searches that work separately but when I combine them into one search I cant seem to get it to run. 

The first part is to find the earliest/Minimum value in a field called First_Seen for each Datacenter. First seen I have to convert into a readable format but I have this search working on its own.

| stats min(firstSeen) AS min by Datacenter
| eval min = strftime(min, "%F %T.%3N")

The second part is getting stats on a field called state, Adding up a state of "Open" and "Reopened" per each Datacenter and then counting the number of state="Fixed" by each datacenter

| stats count(eval(state="open" OR state="reopened")) as Open count(eval(state="fixed")) as fixed by Datacenter

when I have all of these together within one query I get nothing to load, but separately they both work.

Labels (2)
Tags (4)
0 Karma
1 Solution

scelikok
Champion

Hi @Anthonylucian,

Please try below;

| eventstats min(firstSeen) AS min by Datacenter
| stats latest(*) AS * by ip, pluginID
| dedup macAddress, Datacenter
| stats count(eval(state="open" OR state="reopened")) as Open count(eval(state="fixed")) as fixed min(min) as min by Datacenter
| eval min = strftime(min, "%F %T.%3N")
If this reply helps you an upvote is appreciated.

View solution in original post

scelikok
Champion

You're welcome, please mark solution as accepted for other community members to benefit as well.

If this reply helps you an upvote is appreciated.
0 Karma

scelikok
Champion

Hi @Anthonylucian,

Please try below;

| eventstats min(firstSeen) AS min by Datacenter
| stats latest(*) AS * by ip, pluginID
| dedup macAddress, Datacenter
| stats count(eval(state="open" OR state="reopened")) as Open count(eval(state="fixed")) as fixed min(min) as min by Datacenter
| eval min = strftime(min, "%F %T.%3N")
If this reply helps you an upvote is appreciated.

View solution in original post

Anthonylucian
Path Finder

It worked!

Thank you very much for the help!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The problem with combining these two searches one after the other is the first stats command strips out all fields except 'min' and "Datacenter' so there is no 'state' for the second stats command to use.

Try replacing the first stats command with eventstats.

---
If this reply helps you, an upvote would be appreciated.

Anthonylucian
Path Finder

Events stats didnt work for me, if you look below in the thread you can see the full search being used. Would I need multiple event stats?

0 Karma

ITWhisperer
Ultra Champion

How have you combined them?

Anthonylucian
Path Finder

Just listing them sequentially, I have a few commands between to help me get rid of duplicates and retrieve the latest stats.  would a subsearch be needed?

| stats min(firstSeen) AS min by Datacenter
| eval min = strftime(min, "%F %T.%3N")
| stats latest(*) AS * by ip, pluginID
| dedup macAddress, Datacenter
| stats count(eval(state="open" OR state="reopened")) as Open count(eval(state="fixed")) as fixed by Datacenter

0 Karma

ITWhisperer
Ultra Champion

Each stats is only passing the fields it output down the pipeline, so the second won't have ip or pluginID to group by

.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!