Help on fields command which doesnt works

[jip31]

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 · Accepted solution]

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.

[jip31]

it seems better now
I have just replaced values by last
| stats count(DegradationTime) as DegradationTime dc(host) as "Number of degradation" by MODEL SITE
| stats last(DegradationTime) as DegradationTime sum("Number of degradation") as "Number of degradation" last(SITE) as SITE by MODEL
| sort - "Number of degradation"
| search SITE=toto
| fields - SITE DegradationTime

[anmolpatel]

glad you got that sorted

[jip31]

unfortunately no...

[gaurav_maniar]

Hi,

As per your information, there may be same models in multiple in sites.

So the output of the second stats and fields - SITE is correct.

| stats count(host) as "Number of degradation" by Model SITE

Now Splunk has prepared the results and will give you the count based on combination of "Model" and "SITE" fields.
The next command fields - SITE simply work as removing the column from the display, it will not change the results from the previous stats command.

Either remove SITE from the second stats or get the clear requirement regarding the report.

[jip31]

HI
like i said to somesoni, if I remove SITE from the seconds stats I obviously lost this field
And I need to keep it until that my loadjob command may be executed....

[isoutamo]

If you need the SITE, but don’t want stats by it then just move it before by e.g. values(SITE) as SITE.

Ismo

[jip31]

No because if I am doing that, un unable to use my dropdown list which is used to filter the events by site
Like I said :
1) I need to to filter the events by Model and by SITE in my search in order to be able to use the field SITE later (in the dropdown list i use in the dashboard)
2) But i dont want that this fields is displayed in my panel because if I display it I have double model ( because its possible to have a same Model for two different sites)
so my question is to know how to keep this field available without having double model values in my table??

[somesoni2]

Do those two stats run one after another? The first stats doesn't return field SITE, so wonder if your query returns result at all? Could add some sample data and expected output here? some mock values will do.

[jip31]

yes they run one after another
concerning the first stats it's a copy paste issue sorry
| stats values(DESCRIPTION_MODEL) as Model, count(DegradationTime) as DegradationTime by host SITE
| stats count(host) as "Number of degradation" by Model SITE
| fields - SITE

I have attached sample data and as you can see i have a lot of double model even if i use | fields - SITE
For your understanding, i use a scheduled search so in this one i need to add "by SITE" in order to keep this field available
and after the loadjob command I add "| fields - SITE" because I dont need this field which is just used because I have a dropdown list in my dashboard which allows to filter the data by SITE

[jip31]

Hi
do you have an idea of the root cause please?