Splunk Table Query

[Awanish1212]

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

[VatsalJagani · Accepted solution]

@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]

Thank you very much!