topic Re: Query assistance needed with unrelated data set in Splunk Search

Query assistance needed with unrelated data set

jgauthier — Thu, 12 Apr 2012 19:08:45 GMT

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.

Re: Query assistance needed with unrelated data set

kristian_kolb — Thu, 12 Apr 2012 20:01:35 GMT

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

Re: Query assistance needed with unrelated data set

jgauthier — Thu, 12 Apr 2012 20:20:03 GMT

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

Re: Query assistance needed with unrelated data set

kristian_kolb — Thu, 12 Apr 2012 22:26:17 GMT

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

Re: Query assistance needed with unrelated data set

gkanapathy — Thu, 12 Apr 2012 22:40:01 GMT

(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

Re: Query assistance needed with unrelated data set

kristian_kolb — Thu, 12 Apr 2012 22:58:59 GMT

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

Re: Query assistance needed with unrelated data set

gkanapathy — Thu, 12 Apr 2012 23:00:49 GMT

right. fixed that.

Re: Query assistance needed with unrelated data set

jgauthier — Fri, 13 Apr 2012 13:28:53 GMT

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!

Re: Query assistance needed with unrelated data set

jgauthier — Mon, 28 Sep 2020 11:40:35 GMT

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