topic Re: How to create a table and align rows for related fields using stats? in Splunk Search

How to create a table and align rows for related fields using stats?

ghannemann — Tue, 29 Sep 2020 07:05:11 GMT

Sorry for the lengthy question......

Here is what I am trying to achieve:
For a event, containing the following data:

"host1" "high" "EC2000" "Not enough RAM" more data...
"host2" "high" "EC1000" "Not enough Disk space or Disk Fault" more data...
"host3" "high" "EC1000" "Not enough Disk space or Disk Fault" more data..
"host3" "high" "EC1001" "Not enough Disk space or Disk Fault" more data..
"host3" "high" "EC1000" "Not enough RAM" more data...

The event has the following fields:
Host, Level, Err_Code, Data

So for the host1 as an example:

Host="host1"
Level="high"
Err_Code="EC2000"
Data="Not enough RAM"

What I am trying to achieve is a report/table that looks like:

<table>
  <tr>
    <th>Host</th>
    <th>Data</th>
    <th>Err_Code</th>
    <th>Count</th>
  </tr>
  <tr>
    <td>host1</td>
    <td>Not enough RAM</td>
    <td>EC2000</td>
    <td>1</td>
  </tr>
  <tr>
    <td>host2</td>
    <td>Not enough Disk space or Disk Fault</td>
    <td>EC1000</td>
    <td>1</td>
  </tr>
  <tr>
    <td>host3</td>
    <td>Not enough Disk space or Disk Fault</td>
    <td>EC1000</td>
    <td>3</td>
  </tr>
  <tr>
    <td></td>
    <td></td>
    <td>EC1001</td>
    <td></td>
  </tr>
  <tr>
    <td></td>
    <td>Not enough RAM</td>
    <td>EC2000</td>
    <td></td>
  </tr>
</table>

Note EC2000 and EC2001 are related problems

I am using the following search:

index="something" sourcetype="something else" Host="" Level="high" Err_Code="" | dedup Err_Code, Host | stats count(ERR_Code) AS Count, values(Err_Code) AS Err_Code, values(Data) AS Data by Host | table Host, Err_Code, Data, Count

Which gives me something like:
Host Data Err_Code Count
host1 Not Enough RAM EC1000 1
host 2 Not enough Disk space or Disk Fault EC2000 1
host 3 Not Enough Disk space or Disk Fault EC1000 3
Not Enough RAM EC1001
EC2000

Which is not quite correct, the Data needs to row align with the code. I understand why this is so,
therefore, I tried the following:

index="something" sourcetype="something else" Host="" Level="high" Err_Code="" | dedup Err_Code, Host 
| eval err_detail=Err_Code . "," , Data
| stats count(err_detail) AS Count, values(err_detail) AS err_detail, values(Err_Code) AS Err_Code by Host 
| eval temp=split(err_detail,",") | eval Code=mvindex(temp,0) | eval Details=mvindex(temp,1)
| table Host, Err_Code, err_detail, Code, Details, Count

This produces something like:
Host Err_Code err_detail Code Details Count
host1 EC1000 EC2000,Not Enough RAM EC1000 Not Enough RAM 1
host 2 EC2000 EC1000,Not enough Disk space or Disk Fault EC2000 Not enough Disk space or Disk Fault 1
host 3 EC1000 EC1000,Not enough Disk space or Disk Fault 3
EC1001 EC1001,Not enough Disk space or Disk Fault
EC2000 EC2000,Not Enough RAM

It seems that if I have more that one error row for a given host, the splitting mechanism does not work as I expected.

What am I not understanding here?

Sorry again for the lengthy question.

Regards..

Re: How to create a table and align rows for related fields using stats?

woodcock — Fri, 28 Aug 2015 04:57:54 GMT

The trick to get "row alignment" as you call it is to use list, which does not dedup and resort, instead of values, which does. Try this:

index="something" sourcetype="something else" Level="high" | dedup Err_Code, Host | fillnull value="-" Err_Code Data | stats count AS Count list(Err_Code) AS Err_Code list(Data) AS Data BY Host

Re: How to create a table and align rows for related fields using stats?

chanmi2 — Fri, 28 Aug 2015 07:51:24 GMT

Is the last line of your sample data wrong? I think you mean this:
"host3" "high" "EC2000" "Not enough RAM" more data

Re: How to create a table and align rows for related fields using stats?

chanmi2 — Fri, 28 Aug 2015 07:57:01 GMT

I think you can split by host and Data if I did not misunderstand your question.

index="something" sourcetype="something else" Level="high" | dedup Err_Code, Host | stats count as Count, values(Err_Code) as Err_Code by Host, Data | eval Err_Code_List=mvjoin(Err_Code, ",") | stats sum(Count) as Count, list(Data) as Data, list(Err_Code_List) as Err_Code by Host

index="something" sourcetype="something else" Level="high" | dedup Err_Code, Host | stats count as Count by Host, Data, Err_Code | streamstats count as MAKE_EMPTY_COUNT by Host, Data | eval Data=if(MAKE_EMPTY_COUNT==1, Data, "_") | stats sum(Count) as Count, list(Data) as Data, list(Err_Code) as Err_Code by Host

Hope this helps

Re: How to create a table and align rows for related fields using stats?

somesoni2 — Fri, 28 Aug 2015 14:19:39 GMT

Why not simply this

 index="something" sourcetype="something else" Host="" Level="high" Err_Code="" | stats count as Count by Host, Data, Err_Code