topic Re: How do you table multiple interesting fields values side by side? in Splunk Search

How do you table multiple interesting fields values side by side?

talaveralino — Tue, 28 May 2019 16:09:40 GMT

I would like to create a table that shows a list of all computers that have specific apps installed and those that don't, but I'm not sure how to do it. I added the interesting fields and their values below.

(interesting fields)
sites
computer names
applications

(field values)
site - TX - CA - NY
computer names - Dell001 - Dell002 - Dell003
applications - firefox - internet explorer - chrome

EXAMPLE:

site   computer   application  application   application
TX    DELL001   chrome     firefox     internet explorer
CA    DELL002               firefox
NY    DELL003   chrome                  internet explorer

Re: How do you table multiple interesting fields values side by side?

wyfwa4 — Tue, 28 May 2019 18:14:57 GMT

I would need more details on the structure of the raw event - specifically whether each raw event contained all of the data for each computer, or whether these field values come from lots of different events, but are not generally within the same event.

If all the events contain all fields, then you could use the table command

   <search for events >| table site computer application

The likely problem with this approach, is that you will get multiple events per computer as each event will have a row displayed. So the better approach is to use the "stats" command

   <search for events >| stats values(application) by site computer

This approach will only show one line for each combination of site and computer. However There are two possible issues with this approach . Firstly the list of applications will be in a single column for each site/computer - nice to look at, but not good for CSV export. Secondly, it assumes that every event will contain both the site and computer fields.

How you proceed would depend on your raw data. Assuming you have a fixed list of applications, the simplest approach would be to create new fields for each application.

   <search for events >| mvexpand application |eval chrome=if(application="chrome", "yes", "no" ) |eval chrome=if(application="firefox", "yes", "no" ) |eval chrome=if(application="internet explorer", "yes", "no" )|stats values(chrome) values(firefox) values("internet explorer") by site computer

Firstly we use "mvexpand" to create new events for each value of application (only required if a single event has multiple values for the application field), then we create new fields for each application and assign a value of yes/no to each field. The stats command then creates a table which indicates if that application is installed or not. You could assign the application name to the field if that is the value you want to appear in the table.

If you do not know what applications you are looking for , then you would have to do a bit more advanced processing and use automatic field naming (based on the values) and wildcard for the field names in the stats command.

Re: How do you table multiple interesting fields values side by side?

talaveralino — Tue, 28 May 2019 20:42:08 GMT

I don't have a fixed list of applications unfortunately. I think I was making things too complicated, so I changed my searches. I have one search showing a total number of computers at site and another showing me a total number of computers with a specific app. The problem I have now is that the total number of computers is 316 and total number of computers with the app is 313. I'm not sure how to find the 3 computers missing the app.

Re: How do you table multiple interesting fields values side by side?

wyfwa4 — Tue, 28 May 2019 21:53:49 GMT

It is difficult to know exactly how to address this without an example event. Assuming that the "application" field is the one that filled with one of the possible application names, then you could use the fillnull command to assign a specific value and then filter on that - for example

| fillnull value="no app" application | search application="no app" |stats values (application) by site computer

This will assign the application field with the value "no app" when this field does not exist in a raw event. The search command then filters out any event which does not have an application assigned. Assuming there are computers with no applications assigned or you initial search is just for a single application, is missing that one application.