How-to add Date filters to a report

This is a quick post to answer a question i got in the comments of the "add collection selection" post.
The question was ,how to add start date and end date, start time and end time?

It's fairly easy to do ,all you need is a date field and the Between clause.

In this demo i'm going to use the client activity field as an example.

SELECT TOP 100 A.name0,
               A.user_name0,
               B.lastactivetime,
               B.lastddr,
               lasthw,
               lastsw
FROM   v_r_system A
       JOIN v_ch_clientsummary B
         ON A.resourceid = B.resourceid
WHERE  lastactivetime BETWEEN '2016-03-20' AND '2016-03-23' 

In SSRS you might want to use parameters as the date values ,in my example i create a "From" and "To" Parameters and set their default values ,making the report more user friendly

The From Parameters default value is set to "=DateAdd("d",-30,Today())" that sets the default to 30 days back.

The To Parameters default value is set to "=Today()" ,setting it's default to today.
Change the last line in the query to use the parameters

WHERE  lastactivetime BETWEEN @From AND @To 

when i run the report the two parameters should be set by default to display clients that have been active in the last month.

Hope you found this usefull.

ConfigMgr Client Versions.(Updated to support up to 1602)

Have updated this report to identify client up to 1602 ,you can either replace the whole report or just update the "clientinfo" query

Hi ,it's been a while since my last post so i thought i post a small report I created to check that my clients are running the latest version.
Don't know about you ,but i don't memorize version numbers ,so i created this report that maps the version numbers to different ConfigMgr releases ,CU's and Service packs.

Download: ConfigMgr Versions.RDL
(Have updated the report to identify clients up to 1602)

Unfortunately I haven't found a way to automatically map the version numbers so it's a manual
process. This relase can map versions up to "Configmgr 1602" ,but it's easy to add more versions numbers if you need to...


Hope you find this useful.
Feedback is always nice ,so if you like it, find a problem or have an idea for improving the report ,post a comment bellow.

As Always ,here is the query for the report:
(As you can see it's nothing to it ,just a big case statement that maps the version number to the version name)


SELECT A.name0,
       A.user_name0,
       B.lastactivetime,
       B.lasthw,
       B.lastsw,
       C.caption0,
       A.ad_site_name0,
       A.client0,
       A.client_type0,
       A.client_version0,
       CASE
         WHEN A.client_version0 = '5.00.7711.0000' THEN
         'Configuration Manager RTM'
         WHEN A.client_version0 = '5.00.7711.0200' THEN
         'Configuration Manager CU1'
         WHEN A.client_version0 = '5.00.7711.0301' THEN
         'Configuration Manager CU2'
         WHEN A.client_version0 = '5.00.7804.1000' THEN
         'Configuration Manager SP1'
         WHEN A.client_version0 = '5.00.7804.1202' THEN
         'Configuration Manager SP1 CU1'
         WHEN A.client_version0 = '5.00.7804.1300' THEN
         'Configuration Manager SP1 CU2'
         WHEN A.client_version0 = '5.00.7804.1400' THEN
         'Configuration Manager SP1 CU3'
         WHEN A.client_version0 = '5.00.7804.1500' THEN
         'Configuration Manager SP1 CU4'
         WHEN A.client_version0 = '5.00.7804.1600' THEN
         'Configuration Manager SP1 CU5'
         WHEN A.client_version0 = '5.00.7958.1000' THEN
         'Configuration Manager R2'
         WHEN A.client_version0 = '5.00.7958.1101' THEN
         'Configuration Manager R2 Hotfix: KB 2905002'
         WHEN A.client_version0 = '5.00.7958.1203' THEN
         'Configuration Manager R2 CU1'
         WHEN A.client_version0 = '5.00.7958.1303' THEN
         'Configuration Manager R2 CU2'
         WHEN A.client_version0 = '5.00.7958.1401' THEN
         'Configuration Manager R2 CU3'
         WHEN A.client_version0 = '5.00.7958.1501' THEN
         'Configuration Manager R2 CU4'
         WHEN A.client_version0 = '5.00.7958.1604' THEN
         'Configuration Manager R2 CU5'
         WHEN A.client_version0 = '5.00.8239.1000' THEN
         'Configuration Manager R2 SP1'
         WHEN A.client_version0 = '5.00.8239.1203' THEN
         'Configuration Manager R2 SP1 CU1'
         WHEN A.client_version0 = '5.00.8239.1301' THEN
         'Configuration Manager R2 SP1 CU2'
         WHEN A.client_version0 = '5.00.8239.1403' THEN
         'Configuration Manager R2 SP1 CU3'
         WHEN A.client_version0 = '5.00.8325.1000' THEN
         'Configuration Manager 1511'
         WHEN A.client_version0 = '5.00.8355.1000' THEN
         'Configuration Manager 1602'
         ELSE 'Unknown'
       END AS Clientversion,
       A.creation_date0,
       A.deviceowner0,
       A.distinguished_name0,
       A.full_domain_name0,
       A.internetenabled0,
       A.is_assigned_to_user0
FROM   v_r_system A
       LEFT OUTER JOIN v_ch_clientsummary B
                    ON A.resourceid = B.resourceid
       LEFT OUTER JOIN v_gs_operating_system C
                    ON A.resourceid = C.resourceid
WHERE  client_type0 = 1
       AND A.resourceid IN (SELECT resourceid
                            FROM   dbo.v_fullcollectionmembership
                            WHERE  dbo.v_fullcollectionmembership.collectionid
                                   IN ( @Collections ))