I have the following data in Splunk
"2015/05/15 12:11" Service11=1 host=SystemA
"2015/05/15 12:11" Service12=1 host=SystemA
"2015/05/15 12:11" Service13=1 host=SystemA
"2015/05/15 12:11" Service14=0 host=SystemA
"2015/05/15 12:11" Service15=1 host=SystemA
"2015/05/15 12:11" ServiceX=1 host=SystemA
"2015/05/15 12:11" ServiceY=1 host=SystemA
"2015/05/15 12:11" ServiceZ=1 host=SystemA
"2015/05/15 12:11" Service21=0 host=SystemB
"2015/05/15 12:11" Service22=1 host=SystemB
"2015/05/15 12:11" Service23=1 host=SystemB
"2015/05/15 12:11" Service24=1 host=SystemB
"2015/05/15 12:11" Service25=0 host=SystemB
"2015/05/15 12:11" ServiceX=1 host=SystemB
"2015/05/15 12:11" ServiceY=1 host=SystemB
"2015/05/15 12:11" ServiceZ=1 host=SystemB
"2015/05/15 12:11" Service31=1 host=SystemC
"2015/05/15 12:11" Service32=1 host=SystemC
"2015/05/15 12:11" Service33=1 host=SystemC
"2015/05/15 12:11" Service34=1 host=SystemC
"2015/05/15 12:11" Service35=1 host=SystemC
"2015/05/15 12:11" ServiceX=1 host=SystemC
"2015/05/15 12:11" ServiceY=0 host=SystemC
"2015/05/15 12:11" ServiceZ=0 host=SystemC
Service Values 1 means "running" and 0 means "stopped"
I am trying to create a matrix as follows:
Service Name SystemA SystemB SystemC
============ ======= ======= =======
Service1 1 0 1
Service2 1 1 1
Service3 1 1 1
Service4 0 1 1
Service5 1 0 1
ServiceX 1 1 1
ServiceY 1 1 0
ServiceZ 1 1 0
Note:
Service1 refers to Service11 on SystemA, Service21 on SystemB and Service31 on SystemC
Service2 refers to Service12 on SystemA, Service22 on SystemB and Service32 on SystemC
Service3 refers to Service13 on SystemA, Service23 on SystemB and Service33 on SystemC
Service4 refers to Service14 on SystemA, Service24 on SystemB and Service34 on SystemC
Service5 refers to Service15 on SystemA, Service25 on SystemB and Service35 on SystemC
I can generate matrix using XY Series, but I am unable to use alias of something for the fields as in the Note. I guess I need some help here. I am also trying to find a solution. If I find it I will post it here. If someone has already done something similar before, please post it as answer.
Doesnt look exactly like what you're looking for but if taken to a pivot this could be done:
index="summary" source="a.txt"
|rex field=_raw "(?<service_number>(?<=Service).*(?==[1|0] ))"
|rex field=_raw "(?<running>(?<=Service..=)([0|1]))"
|eval running=if(running==1,"Running","Stopped)
|eval serviceName=if(match(service_number,"\d1"),"Service 1",if(match(service_number,"\d2"),"Service 2",if(match(service_number,"\d3"),"Service 3",if(match(service_number,"\d4"),"Service 4",if(match(service_number,"\d5"),"Service 5","n/a")))))
|stats first(running) as Status by serviceName, host
You could do this, but it's pretty clunky:
yoursearchhere
| eval Service1=coalesce(Service1,Service11,Service21,Service31)
| eval Service2=coalesce(Service2,Service12,Service22,Service32)
| eval Service3=coalesce(Service3,Service13,Service23,Service33)
| eval Service4=coalesce(Service4,Service14,Service24,Service34)
| eval Service5=coalesce(Service5,Service15,Service25,Service35)
| table host Service1Service2 Service3 Service4 Service5 ServiceX ServiceY ServiceZ
| transpose
But I like this better:
yoursearchhere
| rex "Service(?<serviceId>\S+?)\=(?<serviceStatus>\d)"
| eval serviceId = "Service" + if(isint(serviceId) AND serviceId > 9,serviceId % 10,serviceId)
| chart sum(serviceStatus) by serviceId host