topic How to combine result of two query where there is common field after renaming . in Splunk Search

How to combine result of two query where there is common field after renaming .

shyambiswal — Thu, 10 Dec 2020 15:06:05 GMT

Hi All, I have two query as below.

index is same, where as sourcetype and source is different on both query.

There is field call "Vserver"( After rename ) and "host" whose value is same on both the query. This field can be taken as a reference for both the query.

I want to combine the result of the both the query so that, I can have query-2 "vol_count" output in query-1 table output.

can anyone please help me .

Thanks and Regards

Shyam

query-1 :-

index=infra_netapp sourcetype="ontap:vserver" source="vserver-get-iter" | rename vserver-name AS Vserver
| dedup Vserver
| regex Vserver="^([a-zA-Z]+)-([a-z]{0,2})([1-9]{1,2})pri(\d{1,4})"
| eval VserverCatagory=case(
match(Vserver, "^([a-zA-Z]+)-([a-z]{0,2})HD(\d{1,4})"),
"Home",
match(Vserver,"^([a-zA-Z]+)-([a-z]{0,2})GD(\d{1,4})"),
"GD",
match(Vserver,"^([a-zA-Z]+)-([a-z]{0,2})AD(\d{1,4})"),
"AD",
match(Vserver,"^([a-zA-Z]+)-([a-z]{0,2})UD(\d{1,4})"),
"UD",
1==1,"Unknown")
| table host, Vserver,vserver-type,state,VserverCatagory,operational-state

Query-2 :-

index=infra_netapp sourcetype="ontap:volume" source="volume-get-iter" | rename volume-id-attributes.name as Volume, volume-id-attributes.owning-vserver-name as Vserver
| regex Vserver="^([a-zA-Z]+)-([a-z]{0,2})([1-9]{1,2})pri(\d{1,4})"
| stats dc(Volume) AS vol_count BY host, Vserver

Re: How to combine result of two query where there is common field after renaming .

ITWhisperer — Thu, 10 Dec 2020 16:35:14 GMT

How about something like this:

index=infra_netapp (sourcetype="ontap:volume" AND source="volume-get-iter") OR (sourcetype="ontap:vserver" AND source="vserver-get-iter") | eval Vserver=coalesce(vserver-name, volume-id-attributes.owning-vserver-name) | regex Vserver="^([a-zA-Z]+)-([a-z]{0,2})([1-9]{1,2})pri(\d{1,4})" | stats values(host) as host values(vserver-type) as vserver-type values(state) as state values(operational-state) as operational-state dc(volume-id-attributes.name) as vol_count by Vserver | eval VserverCatagory=case( match(Vserver, "^([a-zA-Z]+)-([a-z]{0,2})HD(\d{1,4})"), "Home", match(Vserver,"^([a-zA-Z]+)-([a-z]{0,2})GD(\d{1,4})"), "GD", match(Vserver,"^([a-zA-Z]+)-([a-z]{0,2})AD(\d{1,4})"), "AD", match(Vserver,"^([a-zA-Z]+)-([a-z]{0,2})UD(\d{1,4})"), "UD", 1==1,"Unknown")

Re: How to combine result of two query where there is common field after renaming .

shyambiswal — Mon, 14 Dec 2020 07:19:32 GMT

Hi @ITWhisperer , thanks for replying with query.

when I use that, I got the volume count, but other value of below is not coming.

values(vserver-type) as vserver-type values(state) as state values(operational-state) as operational-state