Extension Attribute to Report When an Application was Lasted Opened

[kish_jayson]

Recently, our Asset Management department expressed a desire to not only report whether or not a workstation had software installed, but also when it was last used. This information can be valuable as it would allow us to repurpose existing unused licenses rather than needlessly purchasing additional seats.

As a result, I was able to create an Extension Attribute for each application we wanted to report on by using the following code example. The Extension Attribute should be created to return a Date (Data Type) and will function in the following way:

• IF the application is not installed, return "Not Present".
• IF the application is installed, return the date and time it was last opened.
• IF the application is installed, but NEVER opened, return "2001-01-01".

By using the Date (Data Type), this allows us to leverage Advanced Computer Searches to look for infrequently used software. For example, we could create an Advanced Computer Search to look for all instances of iMovie that have not been launched in more than 90 days and who's Last Inventory Update was less than 5 days ago.

So far, this has given us great insight into what our users are actually using on their workstations and helping us save a little time and money as well when it comes to license procurement or testing resources.

And because of that, I wanted to share it with the community. Enjoy!

#!/bin/bash

APPLICATION_PATH=/Applications/Self Service.app

if [ ! -e "$APPLICATION_PATH" ]; then
    result="Not Present"
fi

if [ -e "$APPLICATION_PATH" ]; then
    result=`mdls "$APPLICATION_PATH" | grep kMDItemLastUsedDate | awk '{ $1 = $1 } { print }' | cut -c 23-41`
fi

if [ "$result" == "" ]; then
    result="2001-01-01 01:01:01"
fi

echo "<result>$result</result>"

[bmarks]

I've been using this in our environment for a number of apps and it has been a great help, but today I tried to use it for Adobe apps and I'm getting errors I can't decipher. If I run the mdls command manually for Photoshop, for example, I get the following output:

bmarks$ mdls Adobe Photoshop CC 2015/Adobe Photoshop CC 2015.app/
2016-01-28 14:43:29.931 mdls[17255:308974] Metadata.framework [Error]: CFTypeRef copyObject(_MDPlistBytes *, int *, CFTypeRef *, const MDPlistBytesDeserializationCallbacks *, void *, CFAllocatorRef): Invalid plist bytes descriptor: 0x0000000088000031
2016-01-28 14:43:29.932 mdls[17255:308974] Metadata.framework [Error]: CFTypeRef MDPlistBytesCopyPlistAtIndexWithCallbacksAndAllocator(CFAllocatorRef, MDPlistBytesRef, CFIndex, const MDPlistBytesDeserializationCallbacks *, void *): plist copy bad (line 1198); idx = 0; quadIdx = 245; ctx = 0x0

This seems to be the case when I run the command on any of the Adobe CC 2015 apps. Thoughts?

[milesleacy]

Just making sure that you're aware of the Application Usage features in the JSS...

[bmarks]

I'm aware of that feature/checkbox, but as far as I know it doesn't give you any new criteria to use for searching the collected data. That's why the extension attribute comes into play unless I'm missing something.

[milesleacy]

Casper Suite Administrator’s Guide Page 444.

[bmarks]

Yeah, so I still don't see how collecting the application usage data allows you to create reports for dormant apps, which is the point of this extension attribute... or any reports, for that matter. Turning on app usage collection doesn't enable any new criteria to use when creating searches either. The visibility of app usage is still on a computer record by computer record basis and, for example, if I'm trying to figure out from 17,000 Macs which ones haven't opened Firefox in 90 days (so that I can remove it,) turning on that checkbox doesn't really do anything for that scenario.

[mm2270]

Uhm, I'm not sure the point that @bmarks is making has hit home as it should have. Yes, Application Usage is there, but let's be brutally honest for just a moment. Its cool and all, but it tends to be one of those things that, after the initial excitement passes, you realize its kind of useless as its implemented.
Application Usage data:

• Cannot be exported into a report
• Cannot be shown in Advanced Searches (as columns)
• Cannot be used as the basis of criteria for a Smart Group (or Advanced Search)

That's just the top 3 from my head. I'm sure there are other possible uses people would like for them.

So what can you do with them?

• Look at them in the JSS
• *crickets*

Now don't get me wrong here. I'm not saying its possible or even makes sense for JAMF to make it work in all the above areas, but in the very least we should be able to export them out into a csv file. You might say, well, try printing them then. Ever try printing Application Usage pages from the web view? In a word - Ick!
Seriously, the fact that we can't just export them into a usable report makes them basically pretty looking eye charts in your browser and nothing more.

BTW, the same holds true for everything under the "History" tab in a Computer Details view. Nothing under there is easily usable/exportable outside of viewing them right in your browser. And it could be argued that most of the other items in History, like Computer Usage, Policy Logs, etc lend themselves much better for export since they are already in a table format.

[milesleacy]

One would have to create a Licensed Software Record to generate the kind of reporting you mention. Instead of an Advanced Search object, the logs for the Licensed Software Record would be used to return the information.

If you wanted to fully automate removal of unused software, the Extension Attribute could be used to create a Smart Computer Group that would be used to scope a Policy that uninstalls the software in question. I'd be very wary of the user experience in this case, but it could be used to deliver on a particular requirement.

[kish_jayson]

Thanks @bmarks and @mm2270. Application Usage Reports are really good for seeing the total duration that software has been used on a particular system, but doesn't give us the critical information of when the software was last used. Having this information available on demand and at-a-glance has been critical for our Asset Management department when it comes to making purchasing decisions.

Back to @bmarks original issue though, sadly I do not have an answer. We don't yet have any Creative Cloud products in our environment that I could test with. Had it worked on that particular application previously?

[bmarks]

@mm2270 Exactly, thank you.

And, yeah, I know how to use the extension attribute.

The bigger question is my original post above, which is why this doesn't seem to work with the Adobe apps. The mdls command seems to barf when I run using any Adobe app path. This only occurs with Adobe apps too, at least so far during my testing.

[mm2270]

@bmarks Regarding your original question, in looking back up at it, the path to Photoshop looks incomplete to me in the mdls command.

I'm seeing:

mdls Adobe Photoshop CC 2015/Adobe Photoshop CC 2015.app/

Shouldn't it be?:

mdls /Applications/Adobe Photoshop CC 2015/Adobe Photoshop CC 2015.app/

mdls needs the proper full path to the item to read spotlight data from it. Can you try doing it that way?

[kish_jayson]

@mm2270 Good catch. I didn't even realize the path looked incomplete.

[bmarks]

I was in the Applications folder when I ran the command. Those results are just an example from running mdls manually.

[mm2270]

@bmarks Never the less, have you tried it by putting in the full path instead of a relative one? It may not make a difference, but I'm curious to see what happens.
I wish I had Adobe CC apps here to test with (well, not really), but I don't, so I can't offer much help other than vague suggestions.

[Josh_Smith]

Cool EA @kish.jayson, thanks.

Works for me:

mdls /Applications/Adobe Photoshop CC 2015/Adobe Photoshop CC 2015.app | grep kMDItemLastUsedDate | awk '{ $1 = $1 } { print }' | cut -c 23-41
2016-01-27 18:31:36

[bmarks]

I'll ssh into a few of my Macs in a little while and try some path variations. I tested this on a few Macs yesterday with the same results before I posted. But, yeah, it's odd because I have used this same extension attribute for other apps without issue. Since our security scanning product will rate an individual Mac's vulnerability score based on things like having outdated Firefox (even if it hasn't been opened in ages,) we proactively delete certain apps to keep our vulnerability scores down. Thanks again for the assistance.

[bmarks]

@mm2270 In the end, I can reproduce the original errors, but only on specific Macs. Before posting here, I had ssh'd into about 3 or 4 of our Macs. I expanded my testing by ssh'ing into about 10-15 more Macs, and I did get relevant results on most of them. But, every now and then I'll encounter one with results like this:

bmarks-mp2013:/ caspermanage$ mdls /Applications/Adobe Photoshop CC 2015/Adobe Photoshop CC 2015.app/
2016-02-02 12:56:12.128 mdls[17121:284844] Metadata.framework [Error]: CFTypeRef copyObject(_MDPlistBytes *, int *, CFTypeRef *, const MDPlistBytesDeserializationCallbacks *, void *, CFAllocatorRef): Invalid plist bytes descriptor: 0x0000000088000031
2016-02-02 12:56:12.129 mdls[17121:284844] Metadata.framework [Error]: CFTypeRef MDPlistBytesCopyPlistAtIndexWithCallbacksAndAllocator(CFAllocatorRef, MDPlistBytesRef, CFIndex, const MDPlistBytesDeserializationCallbacks *, void *): plist copy bad (line 1198); idx = 0; quadIdx = 247; ctx = 0x0

The path doesn't seem to matter, i.e. whether I run the command with the full path, whether I run it while in the Applications folder or whether I run it while in the subfolder containing the specific Adobe app. So, in the end, I still don't know what my original issue was but at least the extension attribute is giving me good (if not perfect) data for most of my Macs.

[mm2270]

@bmarks Hmm, interesting. Have you tested on the affected Macs to see if mdls is working for other applications and files, and returning valid results? I'm wondering if the Spotlight index is just messed up on them and needs to be rebuilt. Generally when something doesn't contain data that mdls can read, it just returns a blank result, not an error, but then, I don't know if I've really done extensive testing on a Mac with a damaged spotlight index to see how it responds.

[bmarks]

This one I was just using for testing seems only to output those errors with the Adobe apps. And, after a little more digging, it may be isolated to certain CC 2015 versions of the apps. But, even on the same Mac that isn't universal. So far, however, I have not gotten any errors looking for the metadata of non-Adobe apps.

[bmarks]

I wonder if there might be a way to add a line to the beginning of the script/extension attribute that would "fix" or "update" the metadata for these apps in advance of acquiring the specific date field. Today I was demoing some JSS searches to my Software Asset Management team and I started to realize the errors may be more prevalent than I thought. Some of the people watching the demo stated that they had recently opened certain apps, but the dates being shown in the JSS were still displayed as 2001-01-01 01:01:01. When I ssh'd into their Macs and ran the mdls command manually, I got the errors I previously mentioned. So, it appears the EA will output the "never opened" date when it encounters the invalid output. I don't think it's system-wide corruption of the metadata. I can run the same command on every other app on the same Mac without issue. The issue also seems random with Adobe apps in the same suite, i.e. one Adobe app will successfully report the data and another Adobe app on the same Mac will display an error.

[kish_jayson]

You just reminded me of one caveat to this setup. If an application was recently updated and has not been launched since the update, the EA will return 2001-01-01 01:01:01. For us, we just need to check the Policy History prior to making the decision of whether or not to reclaim the license.

@bmarks Just out of curiosity, have you tried re-indexing the drive on one of the affected machines? I'm curious to see if it would make a difference.

[bmarks]

@kish.jayson Now that I have a few good test hostnames, I can try that in a little while. Thanks for the additional info.

[mm2270]

@bmarks That's really curious. I wish I had some good advice for you on this. This must be something weird in Adobe's applications. What else is new with Adobe's wares.
The only command I can think of to try that isn't too time consuming or intensive is:

mdimport /Applications/Adobe Photoshop CC 2015/Adobe Photoshop CC 2015.app/

Edit: Correction on the above command. You specify the path to the directory to reimport into Spotlight as shown, not the UTI.

From the mdimport manpage, it indicates that a command like above tells the local Spotlight server to reimport any items associated with the UTI specified in that directory path. So, that should tell Spotlight to reimport the Adobe applications, but I have no idea if this will help at all. It sounds like something is going wrong when Spotlight is indexing those Adobe CC apps, but I have no idea what it could be. How are the Adobe CC apps being installed initially?

Oh, the other thing is, the above can't be run as root. If you try, you'll get an error, so you'd need to work on a way to run that as a user logged in or some non root way for it to even do anything.

[cwaldrip]

I needed something similar, but I wanted to see the last time an application was running. Spotlight will tell me when it was opened - but only when it was opened. This will tell you when it was last seen running, but it could be ignored in the background ignored. Not great either, but still.

I have a launch daemon that runs every minute that runs a shell script...

#!/bin/bash
# Version 1.0
logdate=`TZ=America/New_York date '+%m/%d/%y %H:%M:%S %Z'`
running=`ps aux | grep "Contents/MacOS/Adobe Premiere Pro CC" | grep -v grep | grep -c "Premiere"`
case $running in
        0) echo "No Log" > /Library/Logs/dng.premiereusage.log
        ;;
        1) echo $logdate > /Library/Logs/dng.premiereusage.log
        ;;
esac
exit

It might seem absurd to do this every minute. If you want to check less frequently, go for it. But it's not a major CPU drain or anything to just do a ps aux and write the date/time to a file. :-)

This writes a single line to a log file (overwriting any previous entry) that looks like...

02/05/16 15:23:49 EST

Then I use this EA...

#!/bin/bash
if [ -e /usr/local/sbin/premiereusage.sh ] ; then
    result=`cat /usr/local/sbin/premiereusage.sh | grep Version | awk '{print $3}'`
else
    result="Not Installed"
fi
echo "<result>$result</result>"

If my script exists then it gets the content of the local log file and reports it back. Otherwise it will tell me the script isn't installed.

I also have a separate EA to check the version of the script (see the commented line at the start of the script) in case I need to update it later.

[sean]

Is it definitely only Adobe apps?

Perhaps try this:

 mdfind -onlyin /Applications kMDItemKind=Application | while read line; do  echo "${line##*/}"; mdls "$line"  | awk '/kMDItemLastUsedDate/ {print $3,$4}'; done

[brock_walters]

Hi guys -

Thought I'd posted on this thread but I must have forgot. :) Just wanted to point out that all the grep to awk kinda stuff on mdls output is a bit unnecessary (& could impact your output.) The -name flag allows you to call the specific attribute you're looking for & multiples can be used:

#!/bin/bash

# JSS Extension Attribute for determining the date & time an Application was last opened
# scope to Smart Group with Application Title criteria that matches the Appliation name in "$apppath"

apppath="/Applications/Self Service.app"
usedate=$(/usr/bin/mdls -name kMDItemLastUsedDate "$apppath" | /usr/bin/awk '{print $3, $4}')

if [ "$usedate" == "(null)" ]
then
    result="2001-01-01 01:01:01"
else
    result="$usedate"
fi

echo "<result>$result</result>"

Still using awk to print just the values into the Extension Attribute (i.e., minus the metadata label.)

mdls outputs lots of interesting information! Get Info comments & Labels... Lots of creative possible Extension Attributes using file metadata...

[mpermann]

@brock.walters when the EA encounters an application that hasn't been lunched it's returning (null) for me on 10.11.4. Are you getting a different result? Should the if statement be

if [ "$usedate" == "(null)" ]

instead of what is listed above?

[brock_walters]

what I believe is happening there is 1 of 2 things: either the application has never been opened or the Spotlight database (that's what's being examined by mdls command) may have been deleted or not updated. Your mileage may vary. :)

As per usual the ever-expert @mm2270 has a previous post on this very topic:

https://jamfnation.jamfsoftware.com/featureRequest.html?id=3361

Hope that helps!