Query assistance needed with unrelated data set

[jgauthier]

Ugh! I hate having to ask for query help, but I'm close.. but not close enough. Basically, I have two sets of data. I want to compare information in a field from set 'A', to the cumulative set of records in set 'B'. So, my set 'A' data will look something like this:

[EDITED to include real data]

In my environment, I run a visualization cluster. One area I want to get visibility into is overallocation of CPUs. Using splunk, I am recording a lot of machine information from the Hypervisors.

For instance a data set for a hypervisor looks like this:

Timestamp=1334260801
Host=S-HYPERV2
Source=ComputerSystem
Name=S-HYPERV2
NumbOfProcs=16

In this data set, I am interested in NumbOfProcs.
In the second data set, I am going to count how my virtual processors are allocated, and compare that to the total above. That data set looks like this:
(record 1)

Timestamp=1334260801
Host=S-HYPERV2
Source=HypervGuestCPU
HCPU="v-sqldb:Hv VP 0"
PercGuestTime=17
PercHostTime=1
PercTotalTime=18

(record 2)
Timestamp=1334260801
Host=S-HYPERV2
Source=HypervGuestCPU
HCPU="v-sqldb:Hv VP 1"
PercGuestTime=2
PercHostTime=0
PercTotalTime=2

For each record, it's a unique vcpu. So, I have done this to get pretty close:

Host="*HYPERV*" AND sourcetype="HypervGuestCPU" AND NOT HCPU="_Total" |dedup HCPU | chart count(HCPU) by host | appendcols [search Host="*HYPERV*" AND sourcetype="ComputerSystem"  | dedup Host | eval NumbofCPUs=(NumbOfProcs) | chart sum(NumbofCPUs) by Host ]

Issues I have are, that I don't like the output. I also am just dumping out out,I would prefer to be able to compare the numbers (total and the count).
Additionally, if there are no vcpus being used, then I have a weird looking table.

Ultimately, I attempting to get the output of :
Server VCPUs Physical
S-HYPERV2 2 16

And it would be fantastic if I could actually compare them for alerting. I'm afraid this is a little over my query foo.

This doesn't have the Total number in it, but this another method I was using to build the data set:

Host="*HYPERV*" AND sourcetype="ComputerSystem" OR sourcetype="HypervGuestCPU" AND NOT HCPU="_Total" | dedup HCPU |eventstats count(HCPU) as VCPU by Host | dedup Host| table Host, VCPU 

But this is just Host by VCPU, in a table.

[gkanapathy]

(sourcetype=ComputerSystem Host="*HYPERV*") OR (sourcetype=HypervGuestCPU NOT HCPU="_Total")
| eval HCPU=coalesce(HCPU,"Not Applicable")
| dedup Host,sourcetype,HCPU
| stats sum(NumbOfProcs) as Physical
        distinct_count(if(HCPU=="Not Applicable",null(),HCPU)) as VCPU
  by Host

[jgauthier]

Here is the working query from both of your suggestions:

sourcetype=HypervGuestCPU OR sourcetype=ComputerSystem NOT HCPU="_Total" | dedup HCPU keepempty=t | stats c(HCPU) AS Virtual_CPUs first(NumbOfProcs) AS PhysicalCPUs by host

And

(sourcetype=ComputerSystem Host="HYPERV") OR (sourcetype=HypervGuestCPU NOT HCPU="_Total")

| stats first(NumbOfProcs) as Physical distinct_count(HCPU) as VCPU by Host

[jgauthier]

Thanks guys. Both were ridiculously close, considering that the data was not present for you. I am going to have to look at these and figure out some stuff so I can apply this. In short, I was able to get kristians to work quicker, with a small modification (removed Name from Dedup). I think the first(NumbOfProcs) was the key. Thanks both so much!

[gkanapathy]

right. fixed that.

[kristian_kolb]

Wouldn't the coalescing throw the dc(HCPU) off by one? /k

[kristian_kolb · Accepted solution]

Hm, I don't have your data to play with, but would something like this be functioning? If both sourcetypes have the same host value (as Splunk sees it) this ought to produce someting like what you're after.

sourcetype=HypervGuestCPU OR sourcetype=ComputerSystem NOT HCPU="_Total" | dedup Name HCPU keepempty=t | stats c(HCPU) AS Virtual_CPUs first(NumbOfProcs) AS PhysicalCPUs by host

Let us know if it works,

/kristian

[jgauthier]

Thank you. I've made heavy edits of the original to outline the data, and details much more clearly.

[kristian_kolb]

Are you sure you got the 1's and 2's correct in your post? Also, I'm not quite sure what you want to accomplish.

I think it would be easier to help you if you posted real events, but mask out/change ip-addresses etc

/k