How do you rename count of field values conditionally?

[dhirendra761]

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.

[somesoni2]

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>>') ]

[renjith_nair · Accepted solution]

@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")]

[darrenfuller]

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

[dhirendra761]

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