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

[ghannemann]

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

[somesoni2]

Why not simply this

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

[chanmi2]

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

[chanmi2]

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

[woodcock]

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