Splunk Search
Highlighted

Help on fields command which doesnt works

Contributor

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

0 Karma
Highlighted

Re: Help on fields command which doesnt works

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

Re: Help on fields command which doesnt works

Contributor

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

0 Karma
Highlighted

Re: Help on fields command which doesnt works

Contributor

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

0 Karma
Highlighted

Re: Help on fields command which doesnt works

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.

0 Karma
Highlighted

Re: Help on fields command which doesnt works

Contributor

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

0 Karma
Highlighted

Re: Help on fields command which doesnt works

Builder

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

0 Karma
Highlighted

Re: Help on fields command which doesnt works

Contributor

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

0 Karma
Highlighted

Re: Help on fields command which doesnt works

Builder

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

0 Karma
Highlighted

Re: Help on fields command which doesnt works

Contributor

unfortunately no...

0 Karma