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
SplunkTrust
SplunkTrust

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 and "Accept as Solution" is appreciated.

View solution in original post

scelikok
SplunkTrust
SplunkTrust

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

If this reply helps you an upvote and "Accept as Solution" is appreciated.
0 Karma

scelikok
SplunkTrust
SplunkTrust

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 and "Accept as Solution" is appreciated.

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, Karma 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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...