Splunk Search

Query assistance needed with unrelated data set

jgauthier
Contributor

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.

Tags (2)
0 Karma
1 Solution

kristian_kolb
Ultra Champion

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

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee
(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
Contributor

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

0 Karma

jgauthier
Contributor

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!

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

right. fixed that.

0 Karma

kristian_kolb
Ultra Champion

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

0 Karma

kristian_kolb
Ultra Champion

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
Contributor

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

0 Karma

kristian_kolb
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 This video is currently being processed. Please try again in a few minutes.(view in My Videos)Struggling with ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...