Query for a list of Ids from a Root Id and then query on each of the list of Ids to get their occurence count

[mohapatraa]

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.

[renjith_nair]

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.

[mohapatraa]

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 .

[renjith_nair]

@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

[renjith_nair]

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>