Splunk Search

From fields values make a table with calculated fields

dfofie
New Member

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 ?

0 Karma

mstjohn_splunk
Splunk Employee
Splunk Employee

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

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

renjith_nair
SplunkTrust
SplunkTrust

@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
Happy Splunking!
0 Karma

JackNobrega
Explorer

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

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