How to count for all columns in table based on condition in Splunk

[Gayathri95]

Hi,

We have nearly 50 columns and we want to extract the count for each column based on condition and represent in bar chart.

below is the query which we are using in Splunk. As its taking time and causing performance issues, Please suggest us the way to optimize it.

index="roche_aid3" "Appl ID"!=blank | eventstats count("Appl ID") | append [search index="roche_aid3" "Appl ID"=blank | rename "Appl ID" as "Appl Count" |eventstats count("Appl Count")] | append [search index="roche_aid3" "Appl Name"!=blank |eventstats count("Appl Name")] | append [search index="roche_aid3" "Appl Name"=blank | rename "Appl Name" as "Appl Name1" |eventstats count("Appl Name1")] | append [search index="roche_aid3" "Appl Description"!=blank |eventstats count("Appl Description")] | append [search index="roche_aid3" "Appl Description"=blank | rename "Appl Description" as "Appl Desctn" | eventstats count("Appl Desctn")] | append [search index="roche_aid3" "Appl Size"!=blank |eventstats count("Appl Size")] | append [search index="roche_aid3" "Appl Size"=blank| rename "Appl Size" as "Appl Size1" | eventstats count("Appl Size1")] |append [search index="roche_aid3" "Service Level"!=blank |eventstats count("Service Level")] | append [search index="roche_aid3" "Service Level"=blank | rename "Service Level" as "Service Level1" | eventstats count("Service Level1")] |append [search index="roche_aid3" "Lifecycle Stage"!=blank |eventstats count("Lifecycle stage")] |append [search index="roche_aid3" "Lifecycle Stage"=blank |rename "Lifecycle Stage" as "Lifecycle Stage1" |eventstats count("Lifecycle Stage1")] |append [search index="roche_aid3" "Geographic Scope"!=blank | eventstats count("Geographic Scope")] | append [search index="roche_aid3" "Geographic Scope"=blank |rename "Geographic Scope" as "Geographic Scope1"| eventstats count("Geographic Scope1")] |append [search index="roche_aid3" "Line Of Business"!=blank |rename "Line Of Business" as "LOB"| eventstats count("LOB")] |append [search index="roche_aid3" "Line Of Business"=blank |rename "Line Of Business" as "LOB1"| eventstats count("LOB1")] | append [search index="roche_aid3" "Business Function"!=blank | eventstats count("Business Function")] | append [search index="roche_aid3" "Business Function"=blank |rename "Business Function" as "Business Function1" | eventstats count("Business Function1")] | append [search index="roche_aid3" "Business Criticality"!=blank | eventstats count("Business Criticality")] | append [search index="roche_aid3" "Business Criticality"=blank | rename "Business Criticality" as "Business Criticality1"| eventstats count("Business Criticality1")] | append [search index="roche_aid3" "Parent Org"!=blank | eventstats count("Parent Org")] | append [search index="roche_aid3" "Parent Org"=blank | rename "Parent Org" as "Parent Org1" | eventstats count("Parent Org1")] | append [search index="roche_aid3" "Appl Category"!=blank | eventstats count("Appl Category")] |append [search index="roche_aid3" "Appl Category"=blank | rename "Appl Category" as "Appl Category1" | eventstats count("Appl Category1")] | append [search index="roche_aid3" "Appl Group"!=blank | eventstats count("Appl Group")] | append [search index="roche_aid3" "Appl Group"=blank | rename "Appl Group" as "Appl Group1" | eventstats count("Appl Group1")] | append [search index="roche_aid3" "Appl Subtype"!=blank |eventstats count("Appl Subtype")] | append [search index="roche_aid3" "Appl Subtype"=blank | rename "Appl Subtype" as "Appl Subtype1" |eventstats count("Appl Subtype1")] |append [search index="roche_aid3" "Service Model"!=blank | eventstats count("Service Model")] |append [search index="roche_aid3" "Service Model"=blank |rename "Service Model" as "Service Model1" | eventstats count("Service Model1")]

|append [search index="roche_aid3" "Degree of Customization"!=blank | rename "Degree of Customization" as "Customization" | eventstats count("Customization")] |append [search index="roche_aid3" "Degree of Customization"=blank |rename "Degree of Customization" as "Customization1" |eventstats count("Customization1")] |append [search index="roche_aid3" "Security Category"!=blank | eventstats count("Security Category")] |append [search index="roche_aid3" "Security Category"=blank | rename "Security Category" as "Security Category1" |eventstats count("Security Category1")] |append [search index="roche_aid3" "Technology Stack"!=blank | eventstats count("Technology Stack")] |append [search index="roche_aid3" "Technology Stack"=blank |rename "Technology Stack" as "Technology Stack1"|eventstats count("Technology Stack1")] |append [search index="roche_aid3" "Install type"!=blank | eventstats count("Install type")] | append [search index="roche_aid3" "Install type"=blank |rename "Install type" as "IT" | eventstats count("IT")] |append [search index="roche" "H_W Platform Version"!=blank |rename "H_W Platform Version" as "H_W"|eventstats count("H_W")] | append [search index="roche_aid3" "H_W Platform"=blank |rename "H_W Platform" as "H_W Platform1"| eventstats count("H_W Platform1")] | append [search index="roche_aid3" "OS Platform Version"!=blank | rename "OS Platform Version" as "OS" |eventstats count("OS")] | append [search index="roche_aid3" "OS Platform Version"=blank |rename "OS Platform Version" as "OS1"| eventstats count("OS1")] | append [search index="roche_aid3" "Active Users"!=blank |eventstats count("Active Users")] | append [search index="roche_aid3" "Active Users"=blank |rename "Active Users" as "Active Users1" | eventstats count("Active Users1")] |append [search index="roche_aid3" "Ticket Count_Last 12 Months!=blank" | rename "Ticket Count_Last 12 Months" as "Ticket" |eventstats count("Ticket")] | append [search index="roche_aid3" "Ticket Count_Last 12 Months=blank" | rename "Ticket Count_Last 12 Months" as "Ticket1" |eventstats count("Ticket1")] | append [search index="roche_aid3" "CR_Last 12 Months"!=blank | rename "CR_Last 12 Months" as "CR" | eventstats count("CR")] | append [search index="roche_aid3" "CR_Last 12 Months"=blank | rename "CR_Last 12 Months" as "CR1"|eventstats count("CR1")] | append [search index="roche_aid3" "Help Facilities _ Documentation"!=blank | rename "Help Facilities _ Documentation" as "Documentation" |eventstats count("Documentation")] |append [search index="roche_aid3" "Help Facilities _ Documentation"=blank |rename "Help Facilities _ Documentation" as "Documentation1"| eventstats count("Documentation1")]

Regards,
GJ

[Gayathri95]

Hi,

We tried the same query which you pinged, but its not working. It returned results as 0. Please help us in this.

[to4kawa]

what's your query?
please use 101010

[Gayathri95]

Hi,

We tried the below query and it returned null results.

index="roche_aid3" |stats count("Appl ID"!=blank) as "Appl ID",count(eval("Appl Id"=blank)) as "Appl ID_blank",count(eval("Appl Name"!=blank)) as "Appl Name",count(eval("Appl Name"!=blank)) as "Appl Name_blank",count(eval("Appl Description"!=blank)) as "Appl Description",count(eval("Appl Description"=blank)) as "Appl Description_blank",count(eval("Appl Size"!=blank)) as "Appl Size",count(eval("Appl Size"=blank)) as "Appl Size_blank",count(eval("Service Level"!=blank)) as "Service Level",count(eval("Service Level"=blank)) as "Service Level_blank",count(eval("Lifecycle Stage"!=blank)) as "Lifecycle Stage",count(eval("Lifecycle Stage"=blank)) as "Lifecycle Stage_blank",count(eval("Geographic Scope"!=blank)) as "Geographic Scope",count(eval("Geographic Scope"=blank)) as "Geographic Scope_blank",count(eval("Line Of Business"!=blank)) as "Busniess Function",count(eval("Line Of Business"=blank)) as "Busniess Function_blank",count(eval("Business Criticality"!=blank)) as "Business Criticality",count(eval("Business Criticality"=blank)) as "Business Criticality_blank",count(eval("Parent Org"!=blank)) as "Parent Org",count(eval("Parent Org"=blank)) as "Parent Org_blank",count(eval("Appl Category"!=blank)) as "Appl Category",count(eval("Appl Category"=blank)) as "Appl Category_blank"

[to4kawa]

blank is " "? or "blank"?

[Gayathri95]

Hi,

The field value itself is "blank"

Regards,
GJ

[to4kawa]

I see.
blank needs ""
check my last answer and wrap string with ""

[Gayathri95]

Hi,

We wrapped string with "" and we tried the answer which you pasted but still its throwing error as "arg 3' I mean line 3 is invalid.I mean its throwing error in stats count("App ID")

Regards,
GJ

[to4kawa]

add eval?

[Gayathri95]

Hi,

We tried with eval and without eval, but still same results as 0.

Regards,
GJ

[to4kawa]

with eval and without eval,
@Gayathri95

=blank or !"=blank must need eval
Now, what's your query?

[shruthiangadi]

Hi,

we tried all possible ways which u suggested but still the result is 0.

[to4kawa]

u suggested
sorry, I don't know. what's it?
please debug line by line.

example:

index="roche_aid3" 
| stats count(eval("Appl ID"!="blank")) as Appl_ID_count

result? OK ➡️ next line add and try
NG ➡️ "Appl ID" is nothing, OR "blank" is wrong.

Do you try like this?

[shruthiangadi]

Hi,

We tried still the result is 0, so please can you help us in minimizing the query size

[to4kawa]

above example

[somesoni2]

Any particular reason for using event stats instead of regular stats??

[shruthiangadi]

Hi,

No we were just doing trail and error method.

[to4kawa]

index="roche_aid3" 
| stats count(eval("Appl ID")) as Appl_ID_count
, count(eval("Appl ID"="")) as Appl_ID_blunk_count
, count(eval("Appl Name")) as Apple_Name_count
, count(eval("Appl Name"="")) as Apple_Name_count
....

like above, try stats and eval
It is unnecessary append

[to4kawa]

index="roche_aid3" 
|stats count("Appl ID"!=blank) as "Appl ID"
,count(eval("Appl Id"=blank)) as "Appl ID_blank"
,count(eval("Appl Name"!=blank)) as "Appl Name"
,count(eval("Appl Name"!=blank)) as "Appl Name_blank"
,count(eval("Appl Description"!=blank)) as "Appl Description"
,count(eval("Appl Description"=blank)) as "Appl Description_blank"
,count(eval("Appl Size"!=blank)) as "Appl Size"
,count(eval("Appl Size"=blank)) as "Appl Size_blank"
,count(eval("Service Level"!=blank)) as "Service Level"
,count(eval("Service Level"=blank)) as "Service Level_blank"
,count(eval("Lifecycle Stage"!=blank)) as "Lifecycle Stage"
,count(eval("Lifecycle Stage"=blank)) as "Lifecycle Stage_blank"
,count(eval("Geographic Scope"!=blank)) as "Geographic Scope"
,count(eval("Geographic Scope"=blank)) as "Geographic Scope_blank"
,count(eval("Line Of Business"!=blank)) as "Busniess Function"
,count(eval("Line Of Business"=blank)) as "Busniess Function_blank"
,count(eval("Business Criticality"!=blank)) as "Business Criticality"
,count(eval("Business Criticality"=blank)) as "Business Criticality_blank"
,count(eval("Parent Org"!=blank)) as "Parent Org"
,count(eval("Parent Org"=blank)) as "Parent Org_blank"
,count(eval("Appl Category"!=blank)) as "Appl Category"
,count(eval("Appl Category"=blank)) as "Appl Category_blank"

this is your query.

line 2: eval is nothing.
please debug line by line.

[asabatini23]

Hi,

50 append kill your splunk instance, try to reduce the number of append or try to use multisearch.

N.B you can use a number of multisearch like the number of your cores prensent in your cpu.

Alessandro