Let's focus on the case where $C = Set$, since this will give the intuition for other cases.
An object $p: E \to X$ in the category $Set/X$ can be thought of as an $X$-indexed set, where over every $x \in X$ there is a fiber $p^{-1}(x)$. Similarly, a morphism in $Set/X$ from $p: E \to X$ to $q: F \to X$ is a global function $h: E \to F$ which takes fibers to fibers, i.e., is an $X$-indexed family of functions $h_x: p^{-1}(x) \to q^{-1}(x)$.
Now, for simplicity, take $Y = 1$ to be a 1-element set, where $Set/1 \simeq Set$. The pullback functor $X^\ast: Set \to Set/X$ takes a set $A$ to the $X$-indexed set where $A_x = A$ for all $x$. A morphism from $X^\ast A \to (p: E \to X)$ is thus a family of functions $h_x: A \to p^{-1}(x)$. Such families are in natural bijection with functions
$$A \to \prod_{x \in X} p^{-1}(x)$$
where on the right we take the product of all fibers together. That basically gives you the right adjoint, and suggests the usual notation for this functor $\prod_X$. More formally, the set $\prod_x p^{-1}(x)$ is constructed as the set of sections $s: X \to E$ of $p: E \to X$; categorically it is the equalizer of a pair of functions
$$Sect(p) \to E^X \stackrel{\to}{\to} X^X$$
which you can work out yourself; basically it's the solution set to the equation $p \circ s = 1_X$. The effect on morphisms is similarly described: $\prod_X h = \prod_{x \in X} h_x$; formally, it can be constructed by taking advantage of the universal property of equalizers.
The situation for the right adjoint to a pullback $f^\ast: Set/Y \to Set/X$ is only slightly more complicated. Intuitively, the right adjoint $\prod_f$ sends an $X$-indexed set $p: E \to X$ to a $Y$-indexed set where for each $y \in Y$, we have
$$(\prod_f p)_y := \prod_{x \in f^{-1}(y)} p^{-1}(x)$$
i.e., don't take the product of all fibers $p^{-1}(x)$, but only over those where $x$ sits over $y$ via the map $f$. Again, this can be constructed more formally by considering $Y$-indexed sets of sections, where we take families of equalizers which implement section equations; here we consider a $Y$-indexed family of diagrams of the form
$$(f \circ p)^{-1}(y)^{f^{-1}(y)} \stackrel{\to}{\to} f^{-1}(y)^{f^{-1}(y)}$$
More compactly, compute the object of sections of $p$ seen as a morphism from $f \circ p$ to $f$ in the category $Set/Y$.
Once the formal categorical details of that have been squared away, it works the same way for any locally cartesian closed category.
