How to chart results from an input file by similar values

[ronaldsc]

Hi everyone -

Hoping one of the many experts here can point me in the right direction. I have an input file with server names and contents within each server. I'd like to be able to display the data from the input file by the unique contents across each of my servers. Example below to better illustrate what I am trying to accomplish

input file contents sample - I have a csv file with the following data:

servera    serverb    serverc
box        spoon      box
spoon      straw      straw
straw                 napkin
bowl                  knife

Here is the desired output - I'd like to display the the contents across all servers and show a null or blank line for unique "contents" across all the servers.

    servera    serverb    serverc
    box                   box
    spoon      spoon
    straw      straw      straw
    bowl   
                          napkin
                          knife

Thanks for the help in advance, please let me know if I need to further explain what I'm trying to accomplish.

[somesoni2 · Accepted solution]

Give this a try

|inputlookup filename.csv
| eval temp=1 | untable temp server content | chart max(temp) over content by server | foreach * [eval <<FIELD>>=case("<<FIELD>>"="content",content,'<<FIELD>>'=1,content)]

Tested with this run anywhere sample.

| gentimes start=-1 | eval temp="box        spoon      box# spoon      straw      straw# straw      -         napkin#bowl      -          knife" | table temp | makemv temp delim="#" | mvexpand temp | rex field=temp "(?<servera>\S+)\s+(?<serverb>\S+)\s+(?<serverc>\S+)" | table ser* | foreach * [eval <<FIELD>>=if('<<FIELD>>'="-",null(),'<<FIELD>>')] 
| eval temp=1 | untable temp server content | chart max(temp) over content by server | foreach * [eval <<FIELD>>=case("<<FIELD>>"="content",content,'<<FIELD>>'=1,content)]

[ronaldsc]

This works, but I have no clue what you did. Untable just pumps the data with the temp server and content columns, correct? What is the foreach statement at the end doing exactly?

[somesoni2]

The best way to understand the query is to run step by step. See the result change from "|inputlookup filename.csv" to "|inputlookup filename.csv | eval temp=1 | untable temp server content" . The columns have become the row labels and values of the columns are coming in a separate field called content. After that I'm charting max(temp) by content, just a placeholder to find which server has that content available. It'll 1 if a server has that content and null if it doesn't. The foreach loop is to loop through all server columns and replace 1 with the value of "content" per your desired output.

[somesoni2]

Is the data in Splunk as indexed data OR lookup csv file? What are the field names available in Splunk for your data and are they static/dynamic?

[ronaldsc]

It's a csv file - I use |inputlookup filename to show the contents. I don't have fields names the file is exactly as shown in my example. Should I change that around, pretty new to using lookup files.

[somesoni2]

When you use |inputlookup filename.csv, there has to be some header/column name appears in Splunk. What are those? Splunk will add the first row as the field name/header automatically.

[ronaldsc]

That would be the server names, so from the example I provided it shows the following:

servera serverb serverc

Below those column names it shows the contents of each server.