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?
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
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
Grazie Giuseppe - I should have thought of the table at the end and thanks for the _time hint too.