Solved: Help on fields command which doesnt works - Page 2

jip31 · ‎02-27-2020

hi

I use the search below in order to count the number of degradation by model
This search is a scheduled search and I call it from my dashboard with a loadjob command
In my dashboard, I have a dropdwon list which allows to filter the data by SITE (test.csv field)
For doing that, I need to jeep the field SITE in my search

| lookup test.csv HOSTNAME as host output SITE MODEL 
| stats values (MODEL) as Model, count(DegradationTime) as DegradationTime by host 
| stats count(host) as "Number of degradation" by Model 
| sort -"Number of degradation"

So I add "by SITE" at the end of my stats command

    | lookup test.csv HOSTNAME as host output SITE MODEL 
    | stats values (MODEL) as Model, count(DegradationTime) as DegradationTime by host SITE
    | stats count(host) as "Number of degradation" by Model SITE
    | sort -"Number of degradation"

But when I am doing this, I have double models because a same model can exist for different SITE
So how to keep the field SITE available for using it in my dropdwon list without displaying double models?
Please note that I dont need this field visible in my table panel

anmolpatel · ‎03-02-2020

This might be what you need:

| stats count(DegradationTime) as DegradationTime dc(host) as "Number of degradation" by MODEL SITE
| stats values(DegradationTime) as DegradationTime sum("Number of degradation") as "Number of degradation" values(SITE) as SITE by MODEL
| sort - "Number of degradation" 
| fields - SITE

Corrected an error: from values("Number of degradation") to sum("Number of degradation")

I tested on this:
Degradation Model SITE host
4 Mazda3 AAA 1
5 Mazda3 AAB 2
1 Mercedes MMM 3

and I got this:
Model DegradationTime Number of degradation
Mazda3 1 2
Mercedes 1 1

As per your description, it meets the output criteria.

View solution in original post

Help on fields command which doesnt works

.conf24 | Registration Open!

ICYMI - Check out the latest releases of Splunk Edge Processor

Introducing the 2024 SplunkTrust!