These are the sample parameters for index, host, source
index="production"
host="abc.com-i-1234"
source="Log-*-3333-abc4j.log"
Suppose there are three Splunk queries as shown below:
----------------------------------------
Query 1:
index="production" host="abc.com-*" source="Log-*" | eval ID=substr(host,9,7) | dedup ID| table ID
Suppose it gives output as :
ID |
i-1234 |
i-5678 |
i-9123 |
i-4567 |
------------------------------
Query 2:
index="production" host="abc.com-$field2$" source="Log-*-*-abc4j.log" | eval Sub_ID = mvindex(split(source,"-"),2) | dedup Sub_ID | table Sub_ID
Suppose it gives output as :
Sub_ID |
111 |
222 |
3333 |
4444 |
555 |
666 |
7777 |
8888 |
where, $field2$ denotes the "ID" generated from Query 1 and each "ID" from Query 1 is mapped to two values of "Sub_ID" generated from Query 2.
E.g
if the query was-
index="production" host="abc.com-i-1234" source="Log-*-*-abc4j.log" | eval Sub_ID = mvindex(split(source,"-"),2) | dedup Sub_ID | table Sub_ID
it will give output as:
Sub_ID |
111 |
222 |
-------------------------------------------
Query 3:
index="production" host="abc.com-$field2$" source="Log-*-$field3$-log4j.log" | dedup RP_Remote_User | table RP_Remote_User | stats count as events
Suppose it gives output as :
events:
52
where, $field2$ denotes the "ID" generated from query 1 and $field3$ denotes the "Sub_ID" generated from Query 2
E.g
if the query was-
index="production" host="abc.com-i-1234" source="Log-*-3333-log4j.log" | dedup RP_Remote_User | table RP_Remote_User | stats count as events
it will give output as: (on the basis of "ID" : i-1234 and "Sub_ID":3333)
events:
52
---------------------------------------
Could you please help me with the Splunk query to generate the output in tabular format as below (count of events corresponding to each ID and its Sub_ID) with the help of above mentioned three queries:
ID | Sub_ID | Events |
i-1234 | 111 | 38 |
222 | 48 | |
i-5678 | 3333 | 52 |
4444 | 45 | |
i-9123 | 555 | 23 |
666 | 34 | |
i-4567 | 7777 | 12 |
8888 | 29 |
@Awanish1212 - Try below
index="production" host="abc.com-*" source="Log-*"
| eval ID=substr(host,9,7)
| eval Sub_ID = mvindex(split(source,"-"),2)
| stats dc(RP_Remote_User) as events by ID, Sub_ID
| stats list(Sub_ID) as Sub_ID, list(events) as events by ID
I hope this helps!!! Kindly upvote if it does!!!
@Awanish1212 - Try below
index="production" host="abc.com-*" source="Log-*"
| eval ID=substr(host,9,7)
| eval Sub_ID = mvindex(split(source,"-"),2)
| stats dc(RP_Remote_User) as events by ID, Sub_ID
| stats list(Sub_ID) as Sub_ID, list(events) as events by ID
I hope this helps!!! Kindly upvote if it does!!!
Thank you very much!