AnsweredAssumed Answered

Can it be done in ExecuteSQL() alone?

Question asked by aknudsen on Aug 16, 2018
Latest reply on Aug 17, 2018 by fmpdude

I have a table where I need to extract two fields and one of them have several values. The first field is an IP address and the second field is an application.

 

IP Address          Application

10.0.0.1               dns

10.0.0.1               ntp

10.0.1.2               dns

 

My output should look like this

 

IP address          Application

10.0.0.1               dns,ntp,

10.0.1.2               dns,,

 

So I solved this with an ExecuteSQL() for all the IP addresses associated with the applications and then removed all duplicates.

 

UniqueValues (

 

 

ExecuteSQL ( "SELECT DISTINCT src FROM log_raw

WHERE app IN ('dns','ntp','smtp')

AND srcloc IS NULL

AND dstloc IS NOT NULL

ORDER BY src";

"";"";

"";""

))

From there I loop through all the IP addresses to build the final list.

 

Let ( [

 

 

~ip = GetValue ( $RawList ; $Counter );

 

 

~sql = ExecuteSQL ( "SELECT DISTINCT app FROM log_raw

                    WHERE app IN ('dns','ntp','smtp')

                    AND src = ?

                    ORDER BY app";

                    "";"";

                    ~ip

                    );

 

 

~linje1 = Case ( GetValue ( ~sql ; 1) = "dns" ; "dns";

                 GetValue ( ~sql ; 2) = "dns" ; "dns";

                 GetValue ( ~sql ; 3) = "dns" ; "dns"

                 );

~linje2 = Case ( GetValue ( ~sql ; 1) = "ntp" ; "ntp";

                 GetValue ( ~sql ; 2) = "ntp" ; "ntp";

                 GetValue ( ~sql ; 3) = "ntp" ; "ntp"

                 );

~linje3 = Case ( GetValue ( ~sql ; 1) = "smtp" ; "smtp";

                 GetValue ( ~sql ; 2) = "smtp" ; "smtp";

                 GetValue ( ~sql ; 3) = "smtp" ; "smtp"

                 );

 

 

~complete =  "\"" & ~ip & "," & ~linje1 & "," & ~linje2 & "," & ~linje3 &  "\""

 

];

 

 

List ( $$Liste; ~complete)

 

 

)

So this builds the list I'm looking for.

 

Screen Shot 2018-08-16 at 23.11.08.png

 

This is exactly what I need, but can it be done with ExecuteSQL() only? I've looked at different JOINS, ALIASes, sub queries etc, but so far I've not had much success.

Outcomes