davidw

Unfortunately not. The URL always contains a UID like device.htm?Id=18357 and the call requires authentication.

Hello everyone,

I wrote a small PHP script that read the connection data for a user from the MSSQL database and displays it as ICAL. This calendar can then be wonderfully inserted into Outlook. Maybe someone can use that. Ideally, an SQL user should be created who only has read access.

The script can then be called up as follows: yourscript.php?User=Username1&apikey=CHANGE_ME

<?php

$users = array(
 "Username1" => "CHANGE_ME",
 "Username2" => "CHANGE_ME",
 "Username3" => "CHANGE_ME",
 "Username4" => "CHANGE_ME",
 "Username5" => "CHANGE_ME"
);

header("content-type:text/calendar");

if($users[$_GET['user']] !== NULL && $users[$_GET['user']] == $_GET['apikey'])

	{

		$eol = "\r\n";
		echo "BEGIN:VCALENDAR
		VERSION:2.0
		PRODID:-//RDMiCal//DE
		CALSCALE:GREGORIAN
		NAME:RDMiCal (".$_GET['user'].")
		X-WR-CALNAME:RDPMiCal (".$_GET['user'].")
		";

		$user = $_GET['user'];

		$apikey = $_GET['apikey'];

		$serverName = "SERVERNAME\MSSQL";

		$connectionInfo = array( "Database"=>"DVLS", "UID"=>"MSSQL_USER_WITH_READ_PERMISSION", "PWD"=>"CHANGE_ME");

		$conn = sqlsrv_connect( $serverName, $connectionInfo);

		if( $conn ) {
			     //echo "Connection established.<br />";
			} else {
			     echo "Connection could not be established.<br />";
			     die( print_r( sqlsrv_errors(), true));
		}

		function removeInvalidChars($input) {

		    return preg_replace("/[^0-9a-zA-Z]/", "", $input);

		}

 		$sql = "SELECT TOP 5000 
 			[ID],
	 		[ConnectionUsername],  
	 		[ConnectionName],     
	 		[StartDateTime],
	 		[EndDateTime],
	 		[ActiveTime],
	 		[Comment],
	 		[ActivityDuration],
	 		[HostName],
	 		[LoggedUserName],
	 		[GroupName],
	 		[MessageType],
	 		[ClosePrompt],
	 		[TicketNumber]

 		FROM 
 			[DVLS].[dbo].[ConnectionLog] 
 		WHERE 
 			[LoggedUserName] 
 		LIKE 
 			'".$user."' 
 		AND
 			[MessageType]
 		LIKE
 			'4'
 		ORDER BY 
 			[StartDateTime] 
 		DESC";
	    $params = array();

	    $stmt = sqlsrv_query( $conn, $sql , $params );

	    $row_count = sqlsrv_num_rows( $stmt );
	        while( $row = sqlsrv_fetch_array( $stmt) ) {
				if(!empty($row["EndDateTime"])) {    
						echo "BEGIN:VEVENT".$eol; 
						echo "DTSTART:".$row["StartDateTime"]->format('Ymd\THis').$eol;
						echo "DTEND:".$row["EndDateTime"]->format('Ymd\THis').$eol;
						echo "DTSTAMP:".$row["StartDateTime"]->format('Ymd\THis').$eol;
						echo "SUMMARY:". $row["ConnectionName"]. " // ".$row["ClosePrompt"]. " #".$row["TicketNumber"].$eol;  
						$stamm = $row["GroupName"];
						$kunde = substr($stamm, 0, strpos($stamm, "\\"));
						echo "LOCATION:".$kunde.$eol;
						echo "DESCRIPTION:None".$eol;
						//echo " CLASS:PUBLIC".$eol;
						echo "UID:".removeInvalidChars($row["ID"]).$eol; 
						echo "END:VEVENT".$eol; 
	 			}

		}

	sqlsrv_close($conn);

	echo "END:VCALENDAR"; 

} else {

	header('HTTP/1.1 403 Forbidden');
}


?>

Hi David,

any updates on this? This where very useful for us, too!

Thanks,

DavidW

That´s great. Thank you vey much!

Hi, is there or will there be a chance that the onpremise version of Confluence can also be inserted? That would be an important feature for us