Knowledge Management

Will summary index work with appendcols?

jimhobday
Engager

I have a query that joins the data from two types of log
1st search acting on log lines like this:

2020-06-02T10:54:05,899 [431972] INFO iseries.programcall.access.ProgramCallImpl Completed ConnectionPool=BasketEnquiry, P=FII6021 RT=10, Job0=064158/QUSER/QZRCSRVS, IB=1_, IR=e13ccfe3-cc40-40d3-a262-eb63fef8b0c3, IT=87d2938f-d166-4acc-8947-fca16c1b00df, IA=P0S000529, IM=acffed94-3679-46cb-8375-263e96873ea7, AdditionalLogInfo=<<EMPTY>>

2nd search acting on log lines like this:

2020-06-02T10:56:32,621 [235270] INFO programcall.access.connection.LoggingCommandConnectionPoolDecorator ConnectionPool=Enquiry, ConnectionAction='being requested', PoolMax=30, PoolActive=0, PoolAvailable=1, PoolFree=30, PoolFreeCapacity=100, Context: P=LDI6203, IR=0b86c9ea-cce0-4cef-a3d7-9581c6f67357, IT=4d583bea-a5c1-4c92-8097-a68b53e95b84, IA=P0S000531, IM=f0af22f2-efea-44d5-ae7a-1912a3dada5b

The query is

index="javaprod" INFO Completed ConnectionPool=* P=* RT=*
| eval KEY=ConnectionPool+":"+P 
| stats max(RT) as MaxResponse, count as Requests, min(ConnectionPool) as ConPool, min(P) as Pcml by KEY
| table KEY, ConPool, Pcml, MaxResponse, Requests
| appendcols [ search index="javaprod" INFO PoolFreeCapacity=* ConnectionPool=*
               | eval KEY=ConnectionPool+":"+P 
               | stats min(PoolFreeCapacity) as MinFreeCapacity by KEY ]

And sample output might be:

KEY                    ConPool        Pcml     MaxResponse  Requests  MinFreeCapacity
BasketEnquiry:FII6001  BasketEnquiry  FII6001  182          129       100
BasketEnquiry:FII6010  BasketEnquiry  FII6010  1908         129       100
BasketEnquiry:FII6021  BasketEnquiry  FII6021  372          130       100
BasketEnquiry:GEI6000  BasketEnquiry  GEI6000  673          10        100
BasketEnquiry:LDI6000  BasketEnquiry  LDI6000  155          410       98

So what I'd like to do is create a summary index of these values in one index (recording the stats every minute or hour) but I don't know if using the appendcols with 2 x sistats will work.

I can't find anything related to this in the existing questions or docs. Does anyone know if summary indexes are designed to work with such a query or do I need to have two separate sistats queries and join them afterwards in the standard query when I report on the aggregated results?

Supplementary question: If I get it wrong, can I purge the sistats and try again?

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @jimhobday,
in these case the only way is to check!
anyway, in summary indexes you sore (using the collect command) all the fields in your results, so if you have at the end of your search the following fields: KEY, ConPool, Pcml, MaxResponse, Requests, MinFreeCapacity, you can store them in a summary, eventually, to be more sure but you don't need it, you can put a table command at the end of the search before collect command:

index="javaprod" INFO Completed ConnectionPool=* P=* RT=*
| eval KEY=ConnectionPool+":"+P 
| stats max(RT) as MaxResponse, count as Requests, min(ConnectionPool) as ConPool, min(P) as Pcml by KEY
| appendcols [ search index="javaprod" INFO PoolFreeCapacity=* ConnectionPool=*
                | eval KEY=ConnectionPool+":"+P 
                | stats min(PoolFreeCapacity) as MinFreeCapacity by KEY ]
| table KEY ConPool Pcml MaxResponse Requests MinFreeCapacity
| collect index=your_summary_index

Only one hint, find a timestamp to store in the summary index, e.g. in the main search use earliest(_time) AS _time.

index="javaprod" INFO Completed ConnectionPool=* P=* RT=*
| eval KEY=ConnectionPool+":"+P 
| stats earliest(_time) AS _time max(RT) as MaxResponse, count as Requests, min(ConnectionPool) as ConPool, min(P) as Pcml by KEY
| appendcols [ search index="javaprod" INFO PoolFreeCapacity=* ConnectionPool=*
                | eval KEY=ConnectionPool+":"+P 
                | stats min(PoolFreeCapacity) as MinFreeCapacity by KEY ]
| table _time KEY ConPool Pcml MaxResponse Requests MinFreeCapacity
| collect index=your_summary_index

Ciao.
Giuseppe

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @jimhobday,
in these case the only way is to check!
anyway, in summary indexes you sore (using the collect command) all the fields in your results, so if you have at the end of your search the following fields: KEY, ConPool, Pcml, MaxResponse, Requests, MinFreeCapacity, you can store them in a summary, eventually, to be more sure but you don't need it, you can put a table command at the end of the search before collect command:

index="javaprod" INFO Completed ConnectionPool=* P=* RT=*
| eval KEY=ConnectionPool+":"+P 
| stats max(RT) as MaxResponse, count as Requests, min(ConnectionPool) as ConPool, min(P) as Pcml by KEY
| appendcols [ search index="javaprod" INFO PoolFreeCapacity=* ConnectionPool=*
                | eval KEY=ConnectionPool+":"+P 
                | stats min(PoolFreeCapacity) as MinFreeCapacity by KEY ]
| table KEY ConPool Pcml MaxResponse Requests MinFreeCapacity
| collect index=your_summary_index

Only one hint, find a timestamp to store in the summary index, e.g. in the main search use earliest(_time) AS _time.

index="javaprod" INFO Completed ConnectionPool=* P=* RT=*
| eval KEY=ConnectionPool+":"+P 
| stats earliest(_time) AS _time max(RT) as MaxResponse, count as Requests, min(ConnectionPool) as ConPool, min(P) as Pcml by KEY
| appendcols [ search index="javaprod" INFO PoolFreeCapacity=* ConnectionPool=*
                | eval KEY=ConnectionPool+":"+P 
                | stats min(PoolFreeCapacity) as MinFreeCapacity by KEY ]
| table _time KEY ConPool Pcml MaxResponse Requests MinFreeCapacity
| collect index=your_summary_index

Ciao.
Giuseppe

0 Karma

jimhobday
Engager

Grazie Giuseppe - I should have thought of the table at the end and thanks for the _time hint too.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...