Splunk Search

How do you rename count of field values conditionally?

dhirendra761
Contributor

Hi,

I have below data in below format using stats count command
Date - FR GE SP UK NULL
16/11/18 - 0 1 1 1 1
17/11/18 - 3 0 0 0 0
18/11/18 - 0 0 0 1 1

Search Command

| convert ctime(_time) AS Date timeformat="%d/%m/%y" 
| eval Country=case(File_Copied="NatCo 1","UK",File_Copied="NatCo 2","GE",File_Copied="NatCo 4","SP",File_Copied="natco 3","FR") 
| chart count(File_Created) over Date by Country
    | convert ctime(_time) AS Date timeformat="%d/%m/%y" 
    | eval Country=case(File_Copied="NatCo 1","UK",File_Copied="NatCo 2","GE",File_Copied="NatCo 4","SP",File_Copied="natco 3","FR") 
    | chart count(File_Created) over Date by Country

But I want to display data as below:

Date - FR GE SP UK NULL
16/11/18 - KO OK OK OK OK
17/11/18 - OK KO KO KO KO
18/11/18 - KO KO KO OK OK

If count is >0, then it will be print as "OK" and If count is equal to 0, then "KO"

Please suggest if this is possible. I am trying a lot, but not succeeding. Any help will be appreciated.

0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@dhirendra761,
Try

| convert ctime(_time) AS Date timeformat="%d/%m/%y" 
| eval Country=case(File_Copied="NatCo 1","UK",File_Copied="NatCo 2","GE",File_Copied="NatCo 4","SP",File_Copied="natco 3","FR") 
| chart count(File_Created) over Date by Country
| untable Date,Country,count 
|eval count=if(count>0,"OK","NOK")|maketable Date,Country,count

OR

| convert ctime(_time) AS Date timeformat="%d/%m/%y" 
| eval Country=case(File_Copied="NatCo 1","UK",File_Copied="NatCo 2","GE",File_Copied="NatCo 4","SP",File_Copied="natco 3","FR") 
| chart count(File_Created) over Date by Country
| foreach * [eval <<FIELD>>=if(<<FIELD>> >0,"OK","NOK")]
Happy Splunking!

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try like this

your base search
| convert ctime(_time) AS Date timeformat="%d/%m/%y" 
 | eval Country=case(File_Copied="NatCo 1","UK",File_Copied="NatCo 2","GE",File_Copied="NatCo 4","SP",File_Copied="natco 3","FR") 
 | chart count(File_Created) over Date by Country
| foreach * [eval "<<FIELD>>"=case("<<FIELD>>"!="Date" AND '<<FIELD>>'=0,"KO", "<<FIELD>>"!="Date" AND '<<FIELD>>'>0,"OK", true(),'<<FIELD>>') ]
0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@dhirendra761,
Try

| convert ctime(_time) AS Date timeformat="%d/%m/%y" 
| eval Country=case(File_Copied="NatCo 1","UK",File_Copied="NatCo 2","GE",File_Copied="NatCo 4","SP",File_Copied="natco 3","FR") 
| chart count(File_Created) over Date by Country
| untable Date,Country,count 
|eval count=if(count>0,"OK","NOK")|maketable Date,Country,count

OR

| convert ctime(_time) AS Date timeformat="%d/%m/%y" 
| eval Country=case(File_Copied="NatCo 1","UK",File_Copied="NatCo 2","GE",File_Copied="NatCo 4","SP",File_Copied="natco 3","FR") 
| chart count(File_Created) over Date by Country
| foreach * [eval <<FIELD>>=if(<<FIELD>> >0,"OK","NOK")]
Happy Splunking!

darrenfuller
Contributor

(only with KO instead of NOK to answer his requirement)

0 Karma

dhirendra761
Contributor

Hi @renjith.nair Thank you for your inputs. Its working for me....
What if I have data
File_Created FR GE SP UK
FR KO OK OK OK
GE OK KO KO KO
SP OK KO KO KO
UK OK KO KO KO

and wanted as :

File_Created    FR  GE  SP  UK
FR   NULL OK    OK  OK
GE  OK  NULL KO KO
SP  OK  KO  NULL KO
UK  OK  KO  KO  NULL 

Means corresponding self element will be has value as NULL.

my Query string is :

host="ITEM-S56303" sourcetype="iisys" 
| eval File_Copied=case(File_Copied="NatCo 1","UK",File_Copied="NatCo 2","GE",File_Copied="NatCo 4","SP",File_Copied="natco 3","FR") 
| eval File_Created=case(File_Created="natco 1","UK",File_Created="natco 2","GE",File_Created="natco 4","SP",File_Created="NatCo 3","FR") 
| chart count(File_Created) over File_Created by File_Copied
 | untable File_Created,File_Copied,count 
 |eval count=if(count>0 ,"OK","KO")| maketable File_Created,File_Copied,count

CC:@somesoni2 @martin_mueller @woodcock

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