Splunk Search

column data output is going to next row

shashidharh
Explorer

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,Responsewrong output.PNG

 



 

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

 

Labels (1)
0 Karma

to4kawa
Ultra Champion

Added these lines

my query will run just itself.

please do not add your query.

0 Karma

shashidharh
Explorer

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 ..

2 items.PNGother columns.PNG

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

0 Karma

shashidharh
Explorer

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 ?

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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? 

0 Karma

shashidharh
Explorer

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)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

shashidharh
Explorer

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"

 

 

0 Karma

to4kawa
Ultra Champion

Your tag didn't close.

0 Karma

to4kawa
Ultra Champion

(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.

0 Karma

shashidharh
Explorer

Hi,

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

something I am doing wrong ?

0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>