topic Re: Calculating Variance of a group leaving one ID at a time in Splunk Search

How to calculate variance of a group leaving one ID at a time?

Veerendra — Thu, 20 Apr 2023 16:38:58 GMT

I have the data as below:

LoginID	AccessDate	Organization	Section	logCount

1	10thApril	O1	S1	1.39
2	11thApril	O2	S2	1.76
3	12thApril	O1	S1	2.45
4	10thApril	O1	S1	5.67
5	11thApril	O2	S2	2.34
6	12thApril	O1	S1	1.23
7	13thApril	O2	S2	2.56

I want to calculate variance corresponding to each LoginID leaving that id for the time,

See Below (The result that is expected):

Id	Organization	Section	values(LoginID)	values(logCount)	Variance
1	O1	S1	2	1.76	2.011847
			3	2.45
			4	5.67
			5	2.34
			6	1.23
			7	2.56
2	O2	S2	1	1.39	2.142889
			3	2.45
			4	5.67
			5	2.34
			6	1.23
			7	2.56

Re: Calculating Variance of a group leaving one ID at a time

Veerendra — Wed, 19 Apr 2023 14:00:10 GMT

Please help me in writing the search

Re: Calculating Variance of a group leaving one ID at a time

somesoni2 — Wed, 19 Apr 2023 14:18:52 GMT

How is variance column value created? What does this report represent?

Re: Calculating Variance of a group leaving one ID at a time

Veerendra — Thu, 20 Apr 2023 12:23:39 GMT

Hi @somesoni2 ,
Its the variance that can be calculated with varp function in splunk.
The main issue here is i want LoginID and remaining LoginIDs of that Organization in the same row.
Please help me there , variance i can calculate.

Re: Calculating Variance of a group leaving one ID at a time

somesoni2 — Thu, 20 Apr 2023 15:39:00 GMT

Not sure if this is feasible with your data, but try this dirty workaround (anything before "| table LoginID..." is to generate sample data, replace it with your search).

| makeresults | eval raw="1 10thApril O1 S1 1.39# 2 11thApril O2 S2 1.76# 3 12thApril O1 S1 2.45# 4 10thApril O1 S1 5.67# 5 11thApril O2 S2 2.34# 6 12thApril O1 S1 1.23# 7 13thApril O2 S2 2.56" | makemv raw delim="#" | mvexpand raw | rename raw as _raw | rex "(?<LoginID>\S+)\s+(?<AccessDate>\S+)\s+(?<Organization>\S+)\s+(?<Section>\S+)\s+(?<logCount>\S+)" | table LoginID AccessDate Organization Section logCount | eval idCount=LoginID."#".logCount | eventstats values(idCount) as idCounts | streamstats count as sno | eval idCounts=if(sno>1,mvappend(mvindex(idCounts,0,sno-2),mvindex(idCounts,sno,-1)),mvindex(idCounts,1,-1)) | rex field=idCounts "(?<LoginIds>\S+)#(?<logCounts>\S+)" | table LoginID Organization Section LoginIds logCounts

Re: How to calculate variance of a group leaving one ID at a time?

woodcock — Fri, 21 Apr 2023 00:21:29 GMT

|makeresults
| eval _raw="
LoginID AccessDate Organization Section logCount
1 10thApril O1 S1 1.39
2 11thApril O2 S2 1.76
3 12thApril O1 S1 2.45
4 10thApril O1 S1 5.67
5 11thApril O2 S2 2.34
6 12thApril O1 S1 1.23
7 13thApril O2 S2 2.56"
| multikv forceheader=1
| stats list(LoginID) list(logCount) stdev(logCount) AS Variance BY Organization Section
| eval Variance = pow(Variance, 2)