Splunk Search

Help on fields command which doesnt works

jip31
Motivator

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
1 Solution

anmolpatel
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

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

0 Karma

jip31
Motivator

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

0 Karma

anmolpatel
Builder

glad you got that sorted

0 Karma

jip31
Motivator

unfortunately no...

0 Karma

gaurav_maniar
Builder

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

jip31
Motivator

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

isoutamo
SplunkTrust
SplunkTrust

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

jip31
Motivator

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

somesoni2
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

jip31
Motivator

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

jip31
Motivator

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

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...