Dashboards & Visualizations

How to chart results from an input file by similar values

ronaldsc
New Member

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.

Tags (2)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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)]

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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)]
0 Karma

ronaldsc
New Member

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?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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?

0 Karma

ronaldsc
New Member

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

ronaldsc
New Member

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.

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