Some of both, but not quite as you might think. I don't have any knowledge of the Brocade Guard, so I can't make comparisons to them. One note is that the guard itself was an elite unit, and there were many Varangians who were not guardsmen. This distinction is given as "within the city" for the elite life guards and "outside the city" for the sailors/marines who hadn't bought their way into the guard. For the rest of this post I'll focus on the Varangians "within the city".

There are plenty of references to the ceremonial duties of the guard and they definitely did plenty of "stand around and guard" duty. There were other unit of the Hetaria (Imperial bodyguards) who were made up of locals and whose military skills would vary depending on which period you're discussing. These would be far more likely to be useful in investigations since the troops were locals, often from noble families. I don't recall any documentation of the Varangian guard being used to investigate a plot; their purpose was to be outsiders.

As outsiders the gaurd didn't have problematic entanglements, which made them a useful tool of the state. The guardsmen were without family ties to anyone who would want to plot against the throne. They often did not speak Greek very well either. Further, guardsmen were typically the ones sent to arrest, interrogate, and mutilate anyone who was considered a threat to the Emperor. Finally, the guard had a long tradition of profiting from their trustworthiness, creating a self-reinforcing cycle. Because of all this, the guard were feared, not trusted, by the locale nobles and it would have been nigh unthinkable to include a Varangian in any plot.

When the Emperor took the field the guardmen would accompany him. We have tales they they slept in their armor, with their swords under their heads, ready for battle. The Guard were also used as shock troops with their signature long axes capable of destroying a shield wall and breaking a battle line. These duties were far from ceremonial.

While the Varangian guardsmen did not investigate plots, they were involved in the arrest and execution of Imperial enemies and in protecting the imperial person on campaign. They did perform ceremonial duties as well, but were always the "fighting men" of the imperial bodyguard, and used as such in both peace and war.

Main Source: The Varangians of Byzantium

[Edit: confused Hetaria with Guard in one bit.]