Splunk Search

Change table columns based on field value

eholz1
Contributor

Hello All,

I have a search question. I have a csv file that returnds data.

the ID field if there is no data - I want to have a table which shows 4 columns: NAME,STATUS,DATE,ACTION. These come from the csv file header line.

If the ID >0 I want to show these columns: DATE-Changed,ID,NAME,DATE_DOWN,ACTION. I have not yet seen how I might do this. What I need, in a sense, it two searches, one when ID=0, and one when ID>0. Any suggestions?

 

Thanks,

EWHOLZ

Labels (1)
0 Karma

eholz1
Contributor

dtbur;rows3

Wow, fast reply. Thanks. The ID gets set when the csv file is written. I have a python program that queries a MySQL database, and writes a "0" as ID if no results are returned from the query. If there is data returned, the ID is taken from query results (i.e ID=34, etc). The csv file is on a remote server. I use the Splunk Universal Forwarder to send the file to splunk. Is there a way to get this file set as an "input lookup" or does the "input lookupo" required the file to be local to the Splunk server?

Thanks for quick help.

EWHolz

0 Karma

dtburrows3
Builder

Oh okay I just assumed it was a Splunk lookup. So if you are indexing the data from a CSV then you can probably do something like this (assuming field extractions are in place)

index=<index> sourcetype=<sourcetype>
    | table
        [ 
            | makeresults
                | fields - _time
                | eval
                    ID=[ | search index=<index> sourcetype=<sourcetype> | stats latest(ID) as ID | return $ID ],
                    field_list_id_zero="NAME,STATUS,DATE,ACTION",
                    field_list_id_positive="DATE-Changed,ID,NAME,DATE_DOWN,ACTION",
                    final_field_list=if(
                        'ID'==0,
                            'field_list_id_zero',
                            'field_list_id_positive'
                            )
                    | fields + final_field_list
                    | return $final_field_list
                ]

 
where <index> and <sourcetype> is where your CSV is being indexed.

0 Karma

dtburrows3
Builder

Not sure exactly how your ID value is being derived in this situation but you may be able to utilize a subsearch holding you list of fields for each scenario and then set up an eval if() function to determine which to use based on the value in the ID field. Then with a return command you can return that conditional field list back into the parent search after a fields command.

Something like this.

 

| inputlookup <lookup>
    | fields  
        [ 
            | makeresults
                | fields - _time
                | eval
                    ``` Not sure how the ID is being derived but there should be a variety of ways to get it here ```
                    ``` From lookup method ```
                    ```
                    ID=[ | inputlookup <lookup> | stats max(ID) as ID | return $ID ]
                    ```
                    ``` From token method ```
                    ```
                    ID=$ID_token$
                    ```
                    ``` This is hardcoded for a POC ```
                    ID=1,
                    field_list_id_zero="NAME,STATUS,DATE,ACTION",
                    field_list_id_positive="DATE-Changed,ID,NAME,DATE_DOWN,ACTION",
                    final_field_list=if(
                        'ID'==0,
                            'field_list_id_zero',
                            'field_list_id_positive'
                            )
                    | fields + final_field_list
                    | return $final_field_list
                ]

 

 
Sample output when ID=0

dtburrows3_0-1703115959337.png

Sample output when ID>0

dtburrows3_1-1703116011254.png


 

Get Updates on the Splunk Community!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...