How would I display a percentage of devices in an HA pair where neither has reported in a specific period?

[john_dagostino]

We have a dashboard set up where we display a percentage of appliances which are not sending logs to Splunk. We take the list of hosts from our CMDB and compare it with the hosts listed in the metadata of the corresponding index, then if that host hasn't appeared within the past 24 hours it's listed as 'Missing'.

index=cmdb asset_type=Firewall  | join host type=left [metadata type=hosts index=fw| eval since=now()-recentTime| search since<86400 | eval myField="true"] | fillnull value=-1 myField | eval isMissing=if(myField == -1, "true", "false") | stats count by isMissing| eventstats sum(count) AS total| where isMissing="true"| eval percent = ((1-(count/total))*100)  | fields percent

The search above works, however the problem I'm having is that several of these devices are configured in an HA pair, where only one or the other will ever send logs to Splunk. For example, I have 100 firewalls listed in the CMDB, 50 of them named 'fw01a' through 'fw50a', and the other half being 'fw01b' through 'fw50b'. The 'a' and 'b' are always matched up, so I created a regex to to strip off the last character and report the status of the pair, however I'm not able to write a query which will give me a percentage of the 'Missing' firewalls.

In a nutshell, what I need is a percentage of the total devices where isMissing=true for both devices in a pair. Any ideas?

[woodcock · Accepted solution]

Something like this should work (cannot test so it may have a few glitches but it should get you most of the way there):

index=cmdb asset_type=Firewall | join host type=left [metadata type=hosts index=fw| eval since=now()-recentTime| search since<86400] | eval hostpair=substr(host, 1, length(host) - 1) | stats count dc(sourcetype) AS sourcetypes by hostpair | stats count count(eval(count==2 AND sourcetypes==1)) AS downedPairs | eval percent = ((1-(downedPairs/count))*100) | fields percent

[john_dagostino]

I'm accepting your answer because although I didn't finish modifying it to fit my environment I'm fairly certain it would have worked. I ended up going with a co-worker's recommendation to eval the missing devices as 0 and available as 1, then do a sum of the hostpair. This seems to be working perfectly, but I appreciate your input.

index=cmdb asset_type=Firewall | join host type=left [metadata type=hosts index=fw| eval since=now()-recentTime| search since<86400 | eval inMeta=1] |fillnull value=0 inMeta| rex field=name "(?P<hostpair>\\w+\\d+\\w+\\d+)" | stats sum(inMeta) as PAIRSTATUS by hostpair| stats count(eval(PAIRSTATUS=0)) as MISSING, dc(hostpair) as TOTAL| eval percent = ((1-(MISSING/TOTAL))*100)| fields percent