Splunk Search

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

Loves-to-Learn Lots

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
Labels (4)

• ### subsearch

Esteemed Legend

|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"
| stats list(LoginID) list(logCount) stdev(logCount) AS Variance BY Organization Section
| eval Variance = pow(Variance, 2)

Revered Legend

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
| table LoginID AccessDate Organization Section 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))

Revered Legend

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

Loves-to-Learn Lots

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.

Loves-to-Learn Lots