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

[Anthonylucian]

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.

[scelikok]

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

[scelikok · Accepted solution]

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

[Anthonylucian]

It worked!

Thank you very much for the help!

[richgalloway]

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.

[Anthonylucian]

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?

[ITWhisperer]

How have you combined them?

[Anthonylucian]

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

[ITWhisperer]

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