Splunk Search

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

ghannemann
Engager

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

Tags (3)
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Why not simply this

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

chanmi2
Path Finder

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

or

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

0 Karma

chanmi2
Path Finder

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

0 Karma

woodcock
Esteemed Legend

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
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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