From fields values make a table with calculated fields

[dfofie]

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 ?

[mstjohn_splunk]

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

[DalJeanis]

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 

[renjith_nair]

@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

[JackNobrega]

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