How to table format data?

[Vani_26]

Hi,
My query:

|tstats count where index=app-clietapp host=ahnbghjk OR host=ncsjnjsnjn sourcetype=app-clientapp source=/opt/splunk/var/clientapp/application.log by PREFIX(status:)
|rename status: as App_Status
|where isnotnull(App_Status)
|eval Sucess=if(App_Status="0" OR App_Status="", "Succ", null())
|eval Error=if(App_Status!="0", "Error", null())

output:

App_Status	count	Error	Sucess
0	767890		Succ
6789	65	Error

But i want the output as shown below:

App_Status	Error	Sucess
6789	65	767890

please let me know how to modify the query so that i can get the required output.

[yeahnah]

Hi @Vani_26 

You could try this

| tstats count where index=app-clietapp host=ahnbghjk OR host=ncsjnjsnjn sourcetype=app-clientapp source=/opt/splunk/var/clientapp/application.log by PREFIX(status:)
| rename status: as App_Status
| where isnotnull(App_Status)
| eval Success=if(App_Status=0, count, Success), Error=if(App_Status!=0, count, Error)
| filldown
| where App_Status > 0
| table App_Status Error Success

Hope this helps

[Vani_26]

Hi @yeahnah 
let me tell you the original one:

my orginal query:
index=app-clietapp host=ahnbghjk OR host=ncsjnjsnjn sourcetype=app-clientapp source=/opt/splunk/var/clientapp/application.log by PREFIX(status:)
|rename status: as App_Status
|where isnotnull(App_Status)
|eval Sucess=if(App_Status="0" OR App_Status="", "Succ", null())
|eval Error=if(App_Status!="0", "Error", null())
|stats count(Sucess) as Sucess_Count count(Error) as Error_Count  count(App_Status) as Total_Count
|eval SuccessPer=( Sucess_Count/Total_Count) * 100
|eval SuccessPercentage=round(SuccessPer,2)
|table SuccessPercentage

OUTPUT:
SuccessPercentage

100.00

tstats query:

|tstats count where index=app-clietapp host=ahnbghjk OR host=ncsjnjsnjn sourcetype=app-clientapp source=/opt/splunk/var/clientapp/application.log by PREFIX(status:)
|rename status: as App_Status
|where isnotnull(App_Status)
|eval Sucess=if(App_Status="0" OR App_Status="", "Succ", null())
|eval Error=if(App_Status!="0", "Error", null())
|stats count(Sucess) as Sucess_Count count(Error) as Error_Count  count(App_Status) as Total_Count
|eval SuccessPer=( Sucess_Count/Total_Count) * 100
|eval SuccessPercentage=round(SuccessPer,2)
|table SuccessPercentage

OUTPUT:
SuccessPercentage

50.00

When i use orginal query i am gettting output as 100%, but when i use tstats  query i am getting 50% as output.
can you please help on this.

[yeahnah]

Hi @Vani_26

It's a bit hard to say without knowing what the event data looks like.  But I imagine it's due to the way you've used the "group by" clause in the index=... query.  The group by clause and PREFIX command would only work for the tstats query which also summaries the results into a table output.

The index=... query equivalent to the tstats query would look something like this...  

index=app-clietapp AND (host=ahnbghjk OR host=ncsjnjsnjn) sourcetype=app-clientapp source=/opt/splunk/var/clientapp/application.log "status:"=*
| stats count BY status: 
| rename status: as App_Status
| ... and so on ...

Note - there is no "by" as a word filter in the base search and I'm assuming the fieldname "status:" is auto-extracted and contains a trailing colon.  This may need some modification when you look at your event data, i.e. maybe the fieldname is just "status" without the colon.   The most important part is that it only works if the status field is extracted some how before the stats command, if that makes sense.

Basically ensure the summarised results (output before the rename command) looks the same.  If they don't then the final result will always be different.

Hope this helps

[Vani_26]

Hi@yeahnah
Actually data was coming correctly using tstats byt the issue was with prefix .the data is displaying by prefix.
now i corrected it by  using your suggested query.
thank you for your help.

|tstats count where index=app-clietapp host=ahnbghjk OR host=ncsjnjsnjn sourcetype=app-clientapp source=/opt/splunk/var/clientapp/application.log by PREFIX(status:)
|rename status: as App_Status
|where isnotnull(App_Status)
| eval Success=if(App_Status=0, count, Success), Error=if(App_Status!=0, count, Error)
|filldown
|stats count by Success, Error
|eval Total_Count= Success+Error
|fields Success Error Total_Count 
|eval SuccessPer=( Success/Total_Count) * 100
|eval SuccessPercentage=round(SuccessPer,2)
|table SuccessPercentage