How to find the percentage from a Splunk DB Connect table?

[crossap]

Hi All,

I am still working on my SANS 20 dashboard and have a search around working out the percentage of compliant machines.

We have a MYSQL DB that's populated with data from Qualys via API

One of the table names compliance is made up of a scan run to make sure machines meet certain aspects and as such become "compliant"

I am using DB Connect to talk to this DB and when running the following

| dbquery "DB_NAME" "SELECT * FROM TABLE_NAME"

See below columns in table

HOST_ID| CONTROLS| PASSED| FAILED| ERROR| PER_COUNT| PER_FAILED| PER_ERROR| PER_PASSED| OS

The amount of hosts will obviously change based upon any new machines added, but I was thinking of something along the lines of

CALCULATE TOTAL HOST_IDs (count)
CALCULATE THOSE WITH FAILED >0

Then workout the percentage that are compliant

100/HOST_COUNT * FAILED COUNT

eg

1015 hosts
50 failed
4.9% failing

this would = 85.1% compliant machines

Sorry I am trying to work out the best commands etc to use but not even getting close - any help would be appreciated

[Richfez · Accepted solution]

crossap,

I don't use dbquery to access the my dbx data - I have DB inputs set up to drop stuff into indexes. Given that difference, here's a potential way to accomplish what you want. I'm sure you'll need to heavily modify this for your data, but this should get you started.

Given my index has fields like Commandtype and ErrorNumber (zero being success), I can calculate the percentage of the successful ones vs. the unsuccessful ones.

index=sqlmaintenance Commandtype=BACKUP_DATABASE ErrorNumber=* | stats count(eval(ErrorNumber=0)) as success count as total | eval percent_success=success/total*100

Taking that in pieces...

This part index=sqlmaintenance Commandtype=BACKUP_DATABASE ErrorNumber=* gets me a list of the items in the index that match what I'm looking for - where the Commandtype is "BACKUP_DATABASE" and the field "ErrorNumber" exists. For reference, that's 3,044 events when run over last month. I do not off hand know how or if this can be adapted to be a direct dbquery.

The stats command is doing two things: | stats count(eval(ErrorNumber=0)) as success is counting where ErrorNumber is 0 (which is a success) into a new field "success". The second part count as total counts all events as a field called total. "Success" in the results is 3028, total is 3044.

So, then we do an eval on those to get your percentage - | eval percent_success=success/total*100. My output is now...

success     total   percent_success
3028       3044      99.474376 

I hope this helps!

[crossap]

Hi Rich,

thanks a lot it helped me to get the end result

stats count(eval(FAILED<1)) as success count as total | eval Compliant %=success/total*100

I used your method calculating anything that doesn't have a failed value is a pass

thanks again - Now I just need to work out how to make my traffic light pull this percentage (but I will try that and log if i get stuck)

Paul