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.
@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")]
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>>') ]
@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")]
(only with KO instead of NOK to answer his requirement)
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