topic Re: From fields values make a table with calculated fields in Splunk Search

From fields values make a table with calculated fields

dfofie — Tue, 29 Sep 2020 21:04:23 GMT

Hi Everyone,
I'm new to Splunk: our Data looks like this:

>     id;name;Field1;Field2;Field3;Field4;field5;field6;field7
>     0;Module Name 0;true;false;true;true;false;true;true
>     1;Module Name 1;true;false;false;true;false;false;false

We would like to build a table that looks like this:

----------------------------------------------
FieldName|  Is_TRUE|   is_False|
field1         |    10        |    20        |
field2         |    10        |    20        |
field3         |    10        |    20        |
----------------------------------------------

The columns "is_true" and "is_False" are the sum of the times where field* is True and respectively False.
How do I get something like this ? Is there a Special Query for that ?

Re: From fields values make a table with calculated fields

JackNobrega — Tue, 29 Sep 2020 21:02:38 GMT

Try using the transpose function. Try running the following steps

run search
| REST /services/data/indexes | table title, splunk_server, currentDBSizeMB

It should produce a table something like this.

title splunk_server currentDBSizeMB

_audit vmphqm1dsc1ax06 1
_internal vmphqm1dsc1ax06 1
_introspection vmphqm1dsc1ax06 1
_telemetry vmphqm1dsc1ax06 1
.
.

Now run the same search with the added transpose function.
| REST /services/data/indexes | table title, splunk_server, currentDBSizeMB | transpose

It should cause the columns to be rows and vise versa. You should get a table like this.

column row 1 row 2 row 3 row 4 row 5

title _audit _internal _introspection _telemetry _thefishbucket
splunk_server vmphqm1dsc1ax06 vmphqm1dsc1ax06 vmphqm1dsc1ax06 vmphqm1dsc1ax06 vmphqm1dsc1ax06
currentDBSizeMB 1 1 1 1 1

Hope this helps. Here is a link to all the SPL commands.
http://docs.splunk.com/Documentation/Splunk/7.1.2/SearchReference/Abstract#collapseDesktop6

Re: From fields values make a table with calculated fields

renjith_nair — Tue, 28 Aug 2018 14:22:52 GMT

@dfofie,

Does this work for you ?

"your search"
|rename field* as Field*|fields Field*|transpose  column_name="Fields"
|rename "row *" as "row*"|eval Is_TRUE=0,Is_False=0
|foreach * [eval Is_TRUE=if(<<FIELD>>=="true",Is_TRUE+1,Is_TRUE+0),Is_False=if(<<FIELD>>=="false",Is_False+1,Is_False+0)]
|fields Fields,Is_TRUE,Is_False

Re: From fields values make a table with calculated fields

DalJeanis — Tue, 28 Aug 2018 15:29:30 GMT

try something like this...

(your search, or use run-anywhere base search below) 
| rename COMMENT as "Kill name field because we only need one generic id field for untable"
| fields - name

| rename COMMENT as "Turn each record into one record per field"
| untable id fieldname fieldvalue

| rename COMMENT as "Sum up the counts by fieldname and fieldvalue (true or false)"
| stats count as mycount by fieldname fieldvalue

| rename COMMENT as "Turn into a chart"
| chart sum(mycount) as count by fieldname fieldvalue

| rename COMMENT as "Optionally, put zeroes in blank cells"
| fillnull

You can use this as a base search for run-anywhere testing

| makeresults 
| eval mydata="0;Module Name 0;true;false;true;true;false;true;true!!!!1;Module Name 1;true;false;false;true;false;false;false"
| makemv delim="!!!!" mydata
| mvexpand mydata
| makemv delim=";" mydata
| eval  id = mvindex(mydata,0), name = mvindex(mydata,1), Field1 = mvindex(mydata,2), Field2 = mvindex(mydata,3), Field3 = mvindex(mydata,4), Field4 = mvindex(mydata,5), field5 = mvindex(mydata,6), field6 = mvindex(mydata,7), field7 = mvindex(mydata,8)
| fields - _time mydata

Re: From fields values make a table with calculated fields

mstjohn_splunk — Tue, 28 Aug 2018 19:45:49 GMT

Hi @dfofie, Did either of the answers below solve your question? If yes, please click “Accept” directly below the answer to resolve the post. If not, please comment with more information if you are still having issues. Thanks!!