topic Re: Need a table format data in Splunk Enterprise

How to table format data?

Vani_26 — Tue, 17 Jan 2023 20:18:00 GMT

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

767890

Succ

6789

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.

Re: Need a table format data

yeahnah — Mon, 16 Jan 2023 21:58:59 GMT

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

Re: Need a table format data

Vani_26 — Tue, 17 Jan 2023 00:03:23 GMT

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.

Re: Need a table format data

yeahnah — Tue, 17 Jan 2023 02:07:56 GMT

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

Re: Need a table format data

Vani_26 — Tue, 17 Jan 2023 21:38:21 GMT

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