I am facing some difficulty to query on the Splunk Log data ,while I was able to make some dashboards and reports , this requirement has been bugging me for last couple of days and I was hoping someone can help me in this forum .
The logs/data are as follows :
2019-01-05 08:03:13.532 INFO 1 --- [-8080-exec-1097] c.c.m.s.process.ProcessEngineDriver : [(i)20190105.08:03:13.532 p127662850.5212899 m.abc@xyz.com-0105140313479] Process started - com.hc.ite/SendAsynchronousMessage
2019-01-05 08:03:13.540 INFO 1 --- [-8080-exec-1097] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.540 p127662850.5212899 a1.2851150] Activity started - Receive Request
2019-01-05 08:03:13.548 INFO 1 --- [-8080-exec-1097] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.548 p127662850.5212899 a1.2851150] Activity completed - completion code null
2019-01-05 08:03:13.568 INFO 1 --- [-8080-exec-1097] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.568 p127662850.5212899 t1.2492350] Transition initiated from 1 to 6
2019-01-05 08:03:13.573 INFO 1 --- [-8080-exec-1097] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.573 p127662850.5212899 a6.2851151] Activity started - Validation
2019-01-05 08:03:13.580 INFO 1 --- [-8080-exec-1097] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.580 p127662850.5212899 a6.2851151] Activity completed - completion code 'true'
2019-01-05 08:03:13.585 INFO 1 --- [-8080-exec-1097] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.585 p127662850.5212899 t11.2492351] Transition initiated from 6 to 8
2019-01-05 08:03:13.898 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.898 p30498461.5212901 m.abc@xyz.com-0105140313479] Process started - com.hc.ite/HcSendMessage/0.2
2019-01-05 08:03:13.902 DEBUG 1 --- [-8080-exec-1097] c.c.mdw.services.process.BaseActivity : [(d)20190105.08:03:13.902 p127662850.5212899 a7.2851154] Dynamic Java ClassLoader: class com.CloudClassLoader:com.hc.ite
2019-01-05 08:03:13.913 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.913 p30498461.5212901 a1.2851155] Activity started - Start
2019-01-05 08:03:13.918 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.918 p30498461.5212901 a1.2851155] Activity completed - completion code null
2019-01-05 08:03:13.922 INFO 1 --- [-8080-exec-1097] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:13.922 p127662850.5212899 a7.2851154] Activity completed - completion code null
2019-01-05 08:03:14.129 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.129 p23330230.5212908 m.abc@xyz.com-0105140313479] Process started - com.hc.ite/MasterSendMessage/1.4
2019-01-05 08:03:14.129 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.129 p30498461.5212901 a4.2851163] Activity completed - completion code null
2019-01-05 08:03:14.140 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.140 p23330230.5212908 a1.2851164] Activity started - Start
2019-01-05 08:03:14.144 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.144 p23330230.5212908 a1.2851164] Activity completed - completion code null
2019-01-05 08:03:14.149 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.149 p30498461.5212901 t13.2492361] Transition initiated from 4 to 2
2019-01-05 08:03:14.155 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.155 p23330230.5212908 t48.2492362] Transition initiated from 1 to 7
2019-01-05 08:03:14.161 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.161 p30498461.5212901 a2.2851165] Activity started - Stop
2019-01-05 08:03:14.166 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.166 p30498461.5212901 a2.2851165] Activity completed - completion code null
2019-01-05 08:03:14.170 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.170 p30498461.5212901 m.abc@xyz.com-0105140313479] Process completed - com.hc.ite/HcSendMessage completion code is null
2019-01-05 08:03:14.177 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.177 p23330230.5212908 a7.2851166] Activity started - Channel
2019-01-05 08:03:14.188 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.188 p23330230.5212908 a7.2851166] Activity completed - completion code 'EMAIL'
2019-01-05 08:03:14.204 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.204 p23330230.5212908 t32.2492363] Transition initiated from 7 to 17
2019-01-05 08:03:14.213 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.213 p23330230.5212908 a17.2851167] Activity started - Send Email
2019-01-05 08:03:14.218 DEBUG 1 --- [ommonThread9354] c.c.mdw.services.process.BaseActivity : [(d)20190105.08:03:14.218 p23330230.5212908 a17.2851167] Invoking subprocess: SendEmail v0.5
2019-01-05 08:03:14.225 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.225 p245576454.5212909 m.abc@xyz.com-0105140313479] Process started - com.hc.ite/SendEmail/0.5
2019-01-05 08:03:14.228 INFO 1 --- [ommonThread9354] c.c.m.s.process.ProcessExecutorImpl : [(i)20190105.08:03:14.228 p23330230.5212908 a17.2851167] Activity suspended
I need to create a report as follows :
RequestID InstanceId Count
abc@xyz.com-0105140313479 5212899(9) 28 --> on clicking should show 12 events
5212901 (8) --> on clicking should show 8 events
5212908 (10) --> on clicking should show 10 events
5212909 (1) --> on clicking should show 1 events
Please note RequestIds will be list as well ( is not constrained to be same all the time).
Any help with the query will be great .
This is the best Ive come up with after extracting "RequestID" & "InstanceId" using Regex
index=k8_hc* container_name="*hc*" [search index=k8_hc* container_name="*hc*" RequestID="*" | stats count by InstanceId | table InstanceId ] | stats values(RequestID) as RequestId, count by InstanceId
Thanks in advance.
Hi @mohapatraa ,
If you have only one RequestId per Instance Id , try this
index=k8_hc* container_name="*hc*" |"your rex for RequestId and InstanceId"
|eventstats values(RequestId) as temp by InstanceId|eval RequestId=coalesce(RequestId,temp)
|stats count by RequestId,InstanceId
Result :
RequestId InstanceId count
abc@xyz.com-0105140313479 5212899 9
abc@xyz.com-0105140313479 5212901 8
abc@xyz.com-0105140313479 5212908 10
abc@xyz.com-0105140313479 5212909 1
Please test and lets know if you need any modifications.
The query I had :
index=k8_hc* container_name="*hc*" [search index=k8_hc* container_name="*hc*" RequestID="*" | stats count by InstanceId | table InstanceId ] | stats values(RequestID) as RequestId, count by InstanceId
and your query :
index=k8_hc* container_name="hc" |"your rex for RequestId and InstanceId"
|eventstats values(RequestId) as temp by InstanceId|eval RequestId=coalesce(RequestId,temp)
|stats count by RequestId,InstanceId
There is not much difference in the results of these 2 queries above .
RequestId InstanceId count
abc@xyz.com-0105140313479 5212899 9
abc@xyz.com-0105140313479 5212901 8
abc@xyz.com-0105140313479 5212908 10
abc@xyz.com-0105140313479 5212909 1
My requirement ( as Ive mentioned earlier)
RequestId InstanceId Count TotalCount
abc@xyz.com-0105140313479 5212899 9 28
5212901 8
5212908 10
5212909 1
The numbers under Count and Total Count should be clickable to return the results for those categories.
If possible I would also like to add a "Start Time" , "End Time" and "Duration" column as well to understand when "abc@xyz.com-0105140313479 " started and ended and what was the duration.
Thanks for the helping me out on this by the way 🙂 Appreciate it .
@mohapatraa , sorry i thought that was your expected output. i haven't used a subsearch and that might give you a better performance if you have large number of events.
Nevertheless, coming back to you original requirement, do you have the total count also as part of your result ? And now in a dashboard, you want to click on the count and should display the events for that particular InstanceId in a table. Is that right? Have you already tried with the drilldown option ?
Thanks
Sample dashboard
<dashboard>
<label>Query for a list of Ids from a Root Id</label>
<row>
<panel>
<table>
<search>
<query> index=k8_hc* container_name="*hc*" |"your rex for RequestId and InstanceId" |eventstats values(RequestId) as temp by InstanceId|eval RequestId=coalesce(RequestId,temp) |stats count by RequestId,InstanceId |eventstats sum(count) as Total by RequestId</query>
<earliest>0</earliest>
<sampleRatio>1</sampleRatio>
</search>
<option name="count">20</option>
<option name="dataOverlayMode">none</option>
<option name="percentagesRow">false</option>
<option name="rowNumbers">false</option>
<option name="totalsRow">false</option>
<option name="wrap">true</option>
<drilldown>
<set token="instance_token">$row.InstanceId$</set>
</drilldown>
</table>
</panel>
</row>
<row>
<panel depends="$instance_token$">
<table>
<search>
<query> index=k8_hc* container_name="*hc*" |"your rex for RequestId and InstanceId"|where InstanceId=$instance_token$</query>
<earliest>0</earliest>
<latest></latest>
</search>
<drilldown>
<unset token="instance_token"></unset>
</drilldown>
</table>
</panel>
</row>
</dashboard>