Data modelling Issue

[Pulkit_Thukral]

Hi All,

I have a requirement that from all the occurences of a Server Patching status i need to consider that server as compliant ,if 80 % (or more) of the occurences are have patching status field as success.

Ex:

Server name       Date                   Patch              Patching status

xyz                 may-2016          Antivirus Patch         Success

xyz                 apr-2016           Windows Patch         Success

xyz                 mar-2016             123 Patch              Success

xyz                 feb-2016              345 Patch                Failure

abc                 apr-2016            Windows Patch         Success

abc                 mar-2016           Windows Patch         Success

abc                 mar-2016           Windows Patch         Success

abc                 may-2016           Windows Patch         Success

abc                 may-2016           Windows Patch         Failure

In the above sample data, server "xyz" has in total 4 occurences based on different patches out of which 3 are "Success" .

Hence, success rate is 3/4 =75% means server compliant critiria (80% is not met).

Similarly , for server "abc",total occurences are 5 out of which 4 are  success .Hence, this server can be considered as compliant(80 % success rate)

So, to count total servers which are compliant (80% or more success), we should will get below details

Server name    Patching status

xyz                  Failure

abc                  Success

How can i achieve this in modelling(Scripting part) ?

[settu_periasamy · Accepted solution]

May be like this..

T1:
Load *,if([Patching status]='Success',1,0) as tmp_field;
LOAD * INLINE [
Server name, Date, Patch, Patching status
xyz, May-16, Antivirus Patch, Success
xyz, Apr-16, Windows Patch, Success
xyz, Mar-16, 123 Patch, Success
xyz, Feb-16, 345 Patch, Failure
abc, Apr-16, Windows Patch, Success
abc, Mar-16, Windows Patch, Success
abc, Mar-16, Windows Patch, Success
abc, May-16, Windows Patch, Success
abc, May-16, Windows Patch, Failure
];

NoConcatenate
T2:
LOAD [Server name],if(Sum(tmp_field)/Count([Patching status])>=0.8,'Success','Failure')  as Patching_Status
Resident T1 Group by [Server name];

DROP Table T1;

[Pulkit_Thukral]

Thanks for your reply.

I tried using the same and it was working but incorrect values were seen .

Hence, doing it now at UI level only rather than modelling level.