column data output is going to next row

[shashidharh]

Hi,

Here is my query:

| search SRCreateRequest Completed | stats count as CreateSR
| appendcols [search SRUpdateRequest Completed | stats count as UpdateSR]
| appendcols [search SRPublishRequest Completed | stats count as PublishSR]
| transpose header_field=a

| appendcols [search SRCreateRequest ERROR | stats count as Failure]
| append [search SRUpdateRequest ERROR | stats count as Failure]
| append [search RPublishRequest ERROR | stats count as Failure]
| appendcols [search SRCreateRequest response | stats count as Response]
| append [search SRUpdateRequest response | stats count as Response]
| append [search RPublishRequest response | stats count as Response]
| rename "column" as "API", "row 1" as "Success" | table API,Success,Failure,Response

Output is not coming in to proper table.. any suggestion

[to4kawa]

> Added these lines

my query will run just itself.

please do not add your query.

[shashidharh]

Hi,

yes, I tried.. but I am getting following screens.

If I have less time range, then it is showing only 2 rows and if increase time range,then extra columns showing like NULL,200 etc.

I wanted to have only 3 rows and 3 columns ..

[ITWhisperer]

Your first table suggests insufficient data to find all 3 values of each dimension. The second table suggests you already have a field called status. Based on @to4kawa suggestion, try

(SRCreateRequest OR SRUpdateRequest OR SRPublishRequest) (Completed OR ERROR OR response)
| rex "(?<API>SRCreateRequest|SRUpdateRequest|SRPublishRequest)"
| rex "(?<CERstatus>Completed|ERROR|response)"
| chart count by API CERstatus

[shashidharh]

Hi,

using @ITWhisperer , I am getting extra column "NULL" which was not expected.

Even I tried using same with dashboard query & it not working.

I tried to use server name in the query..

host=server1 (SRCreateRequest OR SRUpdateRequest OR SRPublishRequest) (Completed OR ERROR OR response)

something I am doing wrong ?

[ITWhisperer]

Rather than using chart, for now try stats so you can more easily look at the where the null is coming from

host=server1 (SRCreateRequest OR SRUpdateRequest OR SRPublishRequest) (Completed OR ERROR OR response)
| rex "(?<API>SRCreateRequest|SRUpdateRequest|SRPublishRequest)"
| rex "(?<CERstatus>Completed|ERROR|response)"
| stats count by API CERstatus

Is there something different about the events where the nulls are being reported? Do you have any multi-value fields in your events? 

[shashidharh]

yes,I do have multi value fields in the events..

BTW in this query,for 'completed' it is not coming ,only ERROR & Response is coming.

If less time range,where result is there then only it is showing , needs to display all 3 columns (if no data,'zero' has to display)

[ITWhisperer]

Splunk doesn't count what isn't there so you will have to append extra results with zero counts then sum them with your existing results.

[shashidharh]

able to remove another column "NULL" using  " where isnotnull(status)"

But, if I tried with in dashboard , getting an error ,although it was working fine with any other search

"Error parsing XML on line 48: Premature end of data in tag form line 1"

[to4kawa]

Your tag didn't close.

[to4kawa]

(SRCreateRequest OR SRUpdateRequest OR SRPublishRequest) (Completed OR ERROR OR response)
| rex "(?<API>SRCreateRequest|SRUpdateRequest|SRPublishRequest)"
| rex "(?<status>Completed|ERROR|response)"
| chart count by API status

It's OK, I guess.

[shashidharh]

Hi,

Added these lines and output is coming NULL instead of status.

something I am doing wrong ?